Complex Staffing Formula

Mitch Excel

New Member
Joined
May 21, 2019
Messages
5
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!
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

BlueAure

New Member
Joined
May 21, 2019
Messages
46
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

New Member
Joined
May 21, 2019
Messages
5
A
B
C
D
E
F
G
H
1
Line1
Line2
Line3
Line4
Line5
TotalStaffing

2
Staffing
11
8
15
6
14
-
3
Date
Shift
-
-
-
-
-
-
4
5/21/19
1st
8
8
8
8
40
5
5/21/19
2nd
8
8
29
6
5/21/19
3rd
8
6
7
5/22/19
1st
8
4
8
4
4
40
8
5/22/19
2nd
8
2
26
9
5/22/19
3rd
3
14

<tbody>
</tbody>

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

New Member
Joined
May 21, 2019
Messages
46
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

New Member
Joined
May 21, 2019
Messages
5

ADVERTISEMENT

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

New Member
Joined
May 21, 2019
Messages
46
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

New Member
Joined
May 21, 2019
Messages
5

ADVERTISEMENT

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

New Member
Joined
May 21, 2019
Messages
46
This seems like a calculus optimization problem, are you certain you aren't my highschool math teacher from back in the day?
 

Watch MrExcel Video

Forum statistics

Threads
1,130,274
Messages
5,641,239
Members
417,202
Latest member
AndyVBA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top