Siju has attended:
Excel Advanced course
Monthly average compliance formula
I need to do monthly average compliance of a procedure by three catogories of staff. I have weekly compliance figures of these staff groups for 50 departments. I need to do a formula to get monthly average, but the problem is some weeks there may not be audits and this week will not be considered for doing monthly average. Can you help please.
RE: Monthly average compliance formula
Hi Siju
Please can you send a little more information. Perhaps a simplified example of your question.
So you know if you are using the Average function or AverageIFS fuction blank cells are not taken into account ie for when there was no audit. Cells that contain a zero will lower the average.
Regards
Doug Dunn
Best STL
RE: Monthly average compliance formula
Hi Doug,
Thanks for the reply. Can i have you e-mail please so that i can send you the excel sheet for you to have a look. I am sorry.. its seems to be bit complicated for me to do AverageIFS formula.
Many thanks
Siju
RE: Monthly average compliance formula
Hi Siju
Please can you send to Doug@stl-training.co.uk
Thanks
Doug
RE: Monthly average compliance formula
Hi again
I've added a formula to calculate the monthly average for the 3 categories of staff. See attached.
The formula is
=IFERROR(AVERAGE(E6,H6,K6,N6),"")
It leaves a blank cell if there are no entries for all weeks.
See if this calculates the averages the way you want.
Regards
Doug
Attached files...
HH Compliance Monthly July 2013 (1).xlsxRE: Monthly average compliance formula
Hi Siju
The file sent before missed out week 1 figures in the B column, sorry.
The formula is
=IFERROR(AVERAGE(B6,E6,H6,K6,N6),"")
I've attached the corrected file.
Please let me know if this performs the correct calculation.
Thanks
Doug
