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