Maintaining a Controlled Average

Hi All,

Is it possible to maintain a controlled average in excel by using a formula or code?

Say I want to maintain a controlled average of 15 work days in a month over 12 months. My total hours worked in a year should not exceed 15*12(180).

• So first month I work 15 days
• Second Month I work 13 days. So my average right now is 14...so next month I have potential of working more than 15 days to catch up with the average.
• Third Month I work 18 days. So now my average is 15.33, which is not acceptable. The worked days needs to be restrcited to 17 or below to get average of 15.
• So in this 3 rd month the values below 18(17 to 0) should only be permitted to be entered by the user. If the user enters any value 18 or above an error should be diplayed saying "Value Above Permissible Limits".

Thanks.

Re: Help in Maintaining a Controlled Average

Thanks PCI!

I have built the attached sheet - forum.ozgrid.com/index.php?attachment/56983/.

I have employed Index-Match functions to arrive at the actual days(unaveraged) worked by a person(FP+T&M) in a given month(TID and Month combination).

From your post above I have now understood how to employ the validation for averaging.

I need the same validation in col M - 'Controlled Average' of "Monthly Invoice Input" tab.

This is where it gets complex for me.....how can i call the average in the 'Controlled Average' against a particular TID/month combination?

I am at a intermediate competency in excel and any suggestions are appreciated....it adds to my learning curve

Thanks.

Re: Maintaining a Controlled Average

Hi Guys,

Any suggestions are welcome....do let me know any functions I may use here.

Thanks.

Re: Maintaining a Controlled Average

Thanks Herbds7.

Will give this a try!