98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Monthly average compliance formula
Monthly average compliance formula
Resolved · Urgent Priority · Version 2007
Fri 19 Jul 2013, 11:23Delegate Siju said...
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.
Fri 19 Jul 2013, 12:16Trainer Doug said...
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
Fri 19 Jul 2013, 14:32Delegate Siju said...
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
Fri 19 Jul 2013, 15:45Trainer Doug said...
RE: Monthly average compliance formula
Hi Siju
Please can you send to Doug@stl-training.co.uk
Thanks
Doug
Fri 19 Jul 2013, 17:01Trainer Doug said...
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).xlsxMon 22 Jul 2013, 11:22Trainer Doug said...
RE: 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
Attached files...
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Using an equal (=) sign that isn't part of a formulaBefore you type the equal sign, type an apostrophe: ' |
