Maintaining constant average

[andyb16]

Hi Guys,

I have a s'sheet within which I populate the count of days worked by a certain person. I want to write a formula that will keep the average aggregate count to 19.

For eg. suppose Jan days worked = 20, Feb days worked = 18, So March days worked should be 57- (20+18) = 17 days, where 57 is derived as 19*3. This should continue for the entire year.

Thanks

 

[Ruddles]

Assuming your employee's Month1 figure is in A1 and his Month2 figure is in B1, place this in C1:-

=19-SUM($[COLOR=blue][B]A1[/B][/COLOR]:[B][COLOR=magenta]B1[/COLOR][/B])+(19*COUNTA($[COLOR=blue][B]A1[/B][/COLOR]:[COLOR=magenta][B]B1[/B][/COLOR]))

Put various values in A1 and B1 and test that C1 is correct.

Finally copy C1 across to D1, E1, F1, etc.

When you know his Month3 figure, enter it in C1 and you will notice that D1 recalculates if the A1:C1 average isn't exactly 19.

 

[andyb16]

Thanks Ruddles! Its working gr8.

 

[Ruddles]

Thanks for the feedback.