# Tweaking a formula

#### Silo

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

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

#### barry houdini

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

##### Active Member
Hello Barry

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

Replies
4
Views
270
Replies
4
Views
437
Replies
1
Views
171
Replies
2
Views
144
Replies
9
Views
200

1,191,060
Messages
5,984,414
Members
439,885
Latest member
Akshay1

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