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


  • Re: Help in Maintaining a Controlled Average

    Thanks PCI!

    I have built the attached sheet -

    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 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 adds to my learning curve :)


  • Re: Maintaining a Controlled Average

    Hi Guys,

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


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!