• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Any smarter Way to get the result ?

Nair

New Member
I am trying to get the productivity of machines
data availability
1) Data entry sheet with different products process with machine used and different capacity - start date and end date
2) a separate sheet with each machines capacity per day - for different capacity products

Currently to get what result i wanted - the grey highlighted result (4) by manually first making a unique list of all products
then get total qty using count , vlookup to get the capacity per day for product for the machine

Please refer the attached file. advise any smarter way ! specially avoiding to create any manual list summary etc
 

Attachments

  • test temp.xlsx
    12.2 KB · Views: 6
3 questions:
1. In cell E16 you have formula =COUNTIFS(D3:D12,D16)
In the cell below that you have =COUNTIFS(D4:D13,D17) and so on below. I doubt this is right and I think the formula in cell E16 should be:
=COUNTIFS($D$3:$D$12,D16) and that should be copied down, so that the $D$3:$D$12 should be the same for all the cells below.
Unfortunately, doing this results in 8.5 in cell F22 which gives you a productivity of 121% in cell I21!
Is this right?

2. The Productivity value in cell I21 is dependent on the 7 in I19; where does this 7 come from?

3. What version of Excel are you using?
 
We can do something (with Power Query) like below where the green table incorporates the vlookup and splits multiple day lines into individual day lines.
After that it's a case of knowing where the actuals come from and incorporating that into the likes of a pivot table. The green table won't be visible to the user.
78879
 
Apologies. corrected file as attached. using Excel 2019

3 questions:
1. In cell E16 you have formula =COUNTIFS(D3:D12,D16)
In the cell below that you have =COUNTIFS(D4:D13,D17) and so on below. I doubt this is right and I think the formula in cell E16 should be:
=COUNTIFS($D$3:$D$12,D16) and that should be copied down, so that the $D$3:$D$12 should be the same for all the cells below.
Unfortunately, doing this results in 8.5 in cell F22 which gives you a productivity of 121% in cell I21!
Is this right?

2. The Productivity value in cell I21 is dependent on the 7 in I19; where does this 7 come from?

3. What version of Excel are you using?
 

Attachments

  • test temp.xlsx
    12.4 KB · Views: 2
We can do something (with Power Query) like below where the green table incorporates the vlookup and splits multiple day lines into individual day lines.
After that it's a case of knowing where the actuals come from and incorporating that into the likes of a pivot table. The green table won't be visible to the user.
View attachment 78879

The actual scenario is the main data will have multiple different machines for different product combination ( Oil, capacity )
as per the file (revised )
One way i tried is - a work sheet , where i run array commands and got unique values from main data ( which taking lot of memory and slowing down my file other process)
then done the vlookup count etc to reach the productivity . and used that data back in report.
 
Try,

1] Calculate the "Actual days" will use NETWORKDAYS.INTL () and Weekend number is 16 for exclude Friday only

2] I make adjusted the tables data in changing the last 2 data into machine type : "LV2", so that the summary formula result can be worked with criteria "LV2"

Then,

3] In "Result" O6, array formula ("Ctrl"+"Shift"+Enter") copied down :

'=IF(N6="","",IFERROR(SUM(SUMIFS(L:L,I:I,IF(B$16:B$23=N6,B$16:B$23,0),J:J,IF(B$16:B$23=N6,C$16:C$23),K:K,IF(B$16:B$23=N6,D$16:D$23,0))*IF(B$16:B$23=N6,E$16:E$23,0))/NETWORKDAYS.INTL(AGGREGATE(15,6,E$3:E$11/(G$3:G$11=N6),1),AGGREGATE(14,6,F$3:F$11/(G$3:G$11=N6),1),16),""))

78887
 

Attachments

  • test temp (BY).xlsx
    15.6 KB · Views: 6
Try,

1] Calculate the "Actual days" will use NETWORKDAYS.INTL () and Weekend number is 16 for exclude Friday only

2] I make adjusted the tables data in changing the last 2 data into machine type : "LV2", so that the summary formula result can be worked with criteria "LV2"

Then,

3] In "Result" O6, array formula ("Ctrl"+"Shift"+Enter") copied down :

'=IF(N6="","",IFERROR(SUM(SUMIFS(L:L,I:I,IF(B$16:B$23=N6,B$16:B$23,0),J:J,IF(B$16:B$23=N6,C$16:C$23),K:K,IF(B$16:B$23=N6,D$16:D$23,0))*IF(B$16:B$23=N6,E$16:E$23,0))/NETWORKDAYS.INTL(AGGREGATE(15,6,E$3:E$11/(G$3:G$11=N6),1),AGGREGATE(14,6,F$3:F$11/(G$3:G$11=N6),1),16),""))

View attachment 78887

Thanks Mate. this will suffice the requirement using the manual created summary ( B15:F23)
as per my orignal worksheet which is using the manual created working range summary.

any way we can avoid this manual created summary ? direct using data from ( B2: G12 ) and using range (I2:L11)
avoiding the manual created summary B15:f23 is the actual requirement as i had to create a seperte working sheet
with array formula to derive the unique data from range ( B2:G11) and that slow down the process
 
The actual scenario is the main data will have multiple different machines for different product combination ( Oil, capacity )
as per the file (revised )
That's fine, my offering will take account of that as well as allowing different start and end dates on the same line.
You've confirmed you have Power Query built in so that's fine too.

But I'm still after where your actuals come from; there is no sign of that in your attachment except for a single cell value, and presumably that's for one machine only (LV1).

If I can get actuals from you (hopefully in a table with dates) I can get you your reports in a flexible way.
 
updated the sheet showing separate sheets
1) REPORT - which is the target to achieve
2) Capacity - a sheet with master data for all machines and their per day capacity
3) DATA - Main data sheet where day to day assignment planning will be updated.
 

Attachments

  • test temp.xlsx
    17.4 KB · Views: 5
A question on how to derive productivity:
On the sheet Sheet1 you have 81% in cell O6 which is partly derived from the value in cell I17 with the formula =NETWORKDAYS(E3,F11,16)+1 which on that sheet is for the first and last dates for just one machine (LV1). It's easy to do similar for your Data sheet table and get the first and last dates on a per machine basis. I have a problem understanding the NETWORKDAYS formula (which excludes Saturdays and Sundays as workdays), but you've got a +1 in there too!? Also what's the 16 doing in there? At the moment, it's excluding a single holiday date of 16th Jan 1900 !?

At the moment your Data table start and end dates on a given row are both the same; can I assume it's automatically a 'working day' regardless of the day of the week? (you have both Sat and Sun dates in there - should we be using NETWORKDAYS on those?).

I expect, because you have both start and end dates in a given row, that these are not necessarily always going to be the same date; if they are different dates (no problem handling that by the way) can we assume that that machine has been working on every date between those 2 dates regardless of the day of the week?

Answer as many questions as you can, but principally answer the bold questions above.

Just out of interest (because it's work in progress) the attached has a green table on the Report sheet at cell G2 where the first 4 columns are essentially the same as your Data table columns (although if there were different start and end dates on a given row in the Data table there would be one row per day added on the Report table), but I've added a 5th column which just 'looks up' the machine and capacity on your Capacity sheet table and comes back with the capacity/day value.

You can see what happens if you have different start and end dates by changing, say, cell E3 (end date) on the Data sheet from May 11th to May 13th, then when you go back to the green report table, right-click it and choose Refresh, you'll see 3 rows appear for LV1 capacity 300, one each for the dates 11th, 12th and 13th of May. You can see how by counting rows we can get working days.

Finally (almost), if you can, tell me what Idle days are about.

Also out of interest is a pivot table at cell O1 of the Report sheet based on the green table. In the pivot table you can choose which dates you want to summarise at cell P1's dropdown. It could be better than that, we could group dates into weeks easily so that you'll see week to week changes.

Ultimately, the green table won't be on any sheet anywhere, only a summary pivot table of some kind will exist.

What version of Excel are you using?
 

Attachments

  • Chandoo48024test temp2.xlsx
    33.2 KB · Views: 2
For different Job orders - different machines will be used for different actions required.
Example :
Job Order 1001,
Process 1 - Machine A
Process 2 - Machine B
Process 3 - Machine C

Job Order 10002
Process 1 - Machine C
Process 2 - Machine D
Process 3 - Machine X

so on for specific planned dates from Start date to end date booked.

each machine have specific capacity based on type of job. Machine A might have 1 day required to finish job order 1001 , but may require 2 day to finish job order 1003. Thats updated in workstations capacity for each type of job.

Productivity of machine will be calculated based on how much work completed based on the machines capacity.
if a machine have 1 day required for job order 1001, and if its finishes 2 jobs of job order 1001 type then its 200% , if it perform only one job in 2 days the productivity is only 50% compared to capacity.

productivity and idle periods - it will be considered excluding the weekly off day for example in this case its friday.
so if machine can be used on off days to compensate any short falls during other days. A fall back day ! due to loss of productivity due to power failures, or lack of performance in other days.

IDLE PERIOD : is in the period we set , the machine not been used at all , no work been assigned to that machine. excluding the weekly off days.
Working days : set it as excluding friday. if friday a work is assigned thats to cover up short falls

output anticipated is
a) Must able to see all machines productivity during the period set
b) Must able to see the idle periods of each machines during the period set ( will help which machine is not being used and idle for long time)

Thanks to Team chandoo for your advises in advance.
 
For different Job orders - different machines will be used for different actions required.
Example :
Job Order 1001,
Process 1 - Machine A
Process 2 - Machine B
Process 3 - Machine C

Job Order 10002
Process 1 - Machine C
Process 2 - Machine D
Process 3 - Machine X

so on for specific planned dates from Start date to end date booked.

each machine have specific capacity based on type of job. Machine A might have 1 day required to finish job order 1001 , but may require 2 day to finish job order 1003. Thats updated in workstations capacity for each type of job.

Productivity of machine will be calculated based on how much work completed based on the machines capacity.
if a machine have 1 day required for job order 1001, and if its finishes 2 jobs of job order 1001 type then its 200% , if it perform only one job in 2 days the productivity is only 50% compared to capacity.

productivity and idle periods - it will be considered excluding the weekly off day for example in this case its friday.
so if machine can be used on off days to compensate any short falls during other days. A fall back day ! due to loss of productivity due to power failures, or lack of performance in other days.

IDLE PERIOD : is in the period we set , the machine not been used at all , no work been assigned to that machine. excluding the weekly off days.
Working days : set it as excluding friday. if friday a work is assigned thats to cover up short falls

output anticipated is
a) Must able to see all machines productivity during the period set
b) Must able to see the idle periods of each machines during the period set ( will help which machine is not being used and idle for long time)

Thanks to Team chandoo for your advises in advance.
@p45cal and @Nair

I have a 2nd Stroke, stayed in hospital for 6 days and just discharged, I am not comfortable now and hope p45cal can help to solve your problem.

Bosco
 
Last edited:
Bosco, I hope you have a speedy recovery!

Nair, your last message has confused me more than ever (never mind that I couldn't find any answers to the questions I asked!).
I'll turn this one over in my mind for a day or 2 to seee if I begin to see the light.
In the meantime, if someone else reading this thread can understand the requirements, would they be kind enough to jump in and explain to me what I seem to be missing…?
 
Back
Top