Tweaking a formula

[Silo]

Hello everyone

I like some help tweaking a formula I’m working with

This formula delivers a total monthly required quantity in units

Based on average 22 day work calendar per month

Here’s how the formula works

K2 = Daily ratio (in this example 4.25 per day)
O3 = Number of days in the month 31
C5 = Average days worked (22)
H2 = Total Staff


=SUM((($K$2*$O$3)/$O$3)*C5)*H2

Total monthly target in unit sales is 1029

How I would like to modify this is to… I’d like to use this formula per head count (on each row) but have the formula adjust each individual targets based on the number of days they actually are scheduled to work. In other words if 1 employee only works 18 days due to personal time, vacation etc. I like the formula to adjust up everyone else so that the expected target can still be chased.

Last note the headcount can and will change up or down based on location

Any assistance would be helpful.

 

[barry houdini]

Hello Silo, O3 appears to be redundant in your current formula. You are multiplying and then dividing by that figure, which cancel each other out so you'd get the same result with

=$K$2*C5*H2

I'm not clear how you want to change it. How is the data laid out, i.e. numbers of staff, numbers of days worked etc.?

 

[Silo]

Hello Barry

Thanks for replying

Both number of staff and scheduled days worked .

But I like to set it up where the formula could adjust everyone's target depending on the number of days they each work and end up with the same expected total in this example 1029 units