# Complex Staffing Formula

#### Mitch Excel

##### New Member
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.

### 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
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
 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
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

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
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

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
This seems like a calculus optimization problem, are you certain you aren't my highschool math teacher from back in the day?

#### Mitch Excel

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

Replies
0
Views
244
Replies
1
Views
156
Replies
0
Views
441
Replies
20
Views
761
Replies
1
Views
290

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.

### Which adblocker are you using?

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

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