Complex Staffing Formula

[Mitch Excel]

I'm putting together a spreadsheet that determines how much staffing we need to bring in. Row 5 contains permanent data showing how many people are needed to run a certain line. Row 7 and below show how many hours the line will be running each shift. I'm using the following formula to determine the total staffing we need to bring in each shift:

=SUMPRODUCT(E$5:AO$5,E7:AO7)/8

This works fine for the majority of the time when we are running 8 hours per shift but gets problematic when running partial shifts (i.e. 2 hours, 4 hours, etc...) We would like the results to achieve the following goal:

1. Bring in the minimum amount of staffing needed to satisfy demand.
2. A line cannot run with less than the quantity listed in row 5.
3. As one line finishes up, the crew can split up and move around to other lines on the same shift.

Thanks for your help!

 

[BlueAure]

Hello Mitch,

This is a confusing explanation of what you need. Could you please clarify the question by explaining the current worksheet in more detail or providing an example?

 

[Mitch Excel]

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]G
[/TD]
[TD="align: center"]H
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Line1
[/TD]
[TD]Line2
[/TD]
[TD]Line3
[/TD]
[TD]Line4
[/TD]
[TD]Line5
[/TD]
[TD]TotalStaffing

[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD]Staffing
[/TD]
[TD="align: center"]11
[/TD]
[TD="align: center"]8
[/TD]
[TD="align: center"]15
[/TD]
[TD="align: center"]6
[/TD]
[TD="align: center"]14
[/TD]
[TD="align: center"]-
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Date
[/TD]
[TD]Shift
[/TD]
[TD="align: center"]-
[/TD]
[TD="align: center"]-
[/TD]
[TD="align: center"]-
[/TD]
[TD="align: center"]-
[/TD]
[TD="align: center"]-
[/TD]
[TD="align: center"]-
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]5/21/19
[/TD]
[TD]1st
[/TD]
[TD="align: center"]8
[/TD]
[TD="align: center"]8
[/TD]
[TD="align: center"]8
[/TD]
[TD="align: center"]8
[/TD]
[TD][/TD]
[TD="align: center"]40
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]5/21/19
[/TD]
[TD]2nd
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]8
[/TD]
[TD][/TD]
[TD="align: center"]8
[/TD]
[TD="align: center"]29
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]5/21/19
[/TD]
[TD]3rd
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]8
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]6
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]5/22/19
[/TD]
[TD]1st
[/TD]
[TD="align: center"]8
[/TD]
[TD="align: center"]4
[/TD]
[TD="align: center"]8
[/TD]
[TD="align: center"]4
[/TD]
[TD="align: center"]4
[/TD]
[TD="align: center"]40
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]5/22/19
[/TD]
[TD]2nd
[/TD]
[TD="align: center"]8
[/TD]
[TD][/TD]
[TD="align: center"]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]26
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]5/22/19
[/TD]
[TD]3rd
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]14
[/TD]
[/TR]
</tbody>[/TABLE]

I hope this example helps explain. Essentially, I'm trying to automate column H to give the results shown based on two data points:
Data point 1 - C2:G2 are constant staffing numbers for the line
Data point 2 - C4:G9 are hours running per line per shift.

Results in column H should meet the 3 criteria from the original post. Preferably without using VBA or array formulas.

 

[BlueAure]

Thank you for the clarification! I am struggling to understand why your formula multiplies each line by the number of hours worked. What you are essentially doing here with this formula is multiplying the # of workers by the hours worked then dividing by the hours worked. This means you could get the same results by just adding the # of workers. Essentially it is:

((11+8+15+6+14)*8)/8
which is equal to
(11+8+15+6+14)

It seems to me that all you need is to check if there is a value in a line's column and if there is add those workers to the total.

If you want to "reuse" workers, you will still need all those workers at the start of the shift. If I am misunderstanding please correct me, but it seems to me that if everyone starts at the same time, if a line runs, then all you need to do is add the running lines and thats the minimum at least at the start of the shift.

 

[Mitch Excel]

I think the meaning of the numbers isn't clear. Let me redefine:

C2:G2 - Shows how many people we need to run a given line. For example if we want to run Line1 we need 11 people per hour.
C4:G9 - Shows how many hours we need to run each line on each shift. For example, Line2 needs to run 8hrs of 5/21 1st shift and then 4hrs on 5/22 1st shift.
H4:H9 - This is where I need to put a formula. It should calculate how many people we need to bring in for each shift, while meeting the criteria from the original post.

 

[BlueAure]

So, if you are running lines 1, 2, and 3 for 4hrs, 5hrs, 6hrs wouldn't you need (11 + 8+ 15) people at the start of the shift? It seems to me like the hours worked are irrelevant unless you either send people home or start at different times.

 

[Mitch Excel]

Yes, the start time can be variable for each line. See row 7 for an example of when it gets complicated. On 5/22 1st shift, it would be best to bring in 40 people total because Line5 can run for 4 hours and then split the crew up between Line2 and Line4 for the rest of the shift.

 

[BlueAure]

This seems like a calculus optimization problem, are you certain you aren't my highschool math teacher from back in the day?

 

[Mitch Excel]

Lol, yeah this is definitely a math problem. Maybe I should try a math forum somewhere.