What is the range? Are the upcoming months cells really empty or do they house real 0's?
This is a discussion on Excluding #DIV/0! from yearly average within the Excel Questions forums, part of the Question Forums category; I have a spreadsheet that averages the year service level percentage. However, the upcoming months have no data and bring ...
I have a spreadsheet that averages the year service level percentage. However, the upcoming months have no data and bring back a #DIV/0! in the total field, which messes up my yearly average. How would I average the year, but exclude those error messages.
What is the range? Are the upcoming months cells really empty or do they house real 0's?
Hi,
Aladin has some posts that handle this, and his question still needs a response to give you the best answer.
Here are two alternatives, producing different results depending on what you require. Aladin has a few more as well, depending on your foloow-up to his post.
******** ******************** ************************************************************************>
Microsoft Excel - Book2 ___Running: xl2002 XP : OS = Windows Windows 2000
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
=
A B C D 1 1 1 * * 2 2 1 * * 3 3 1 * * 4 #DIV/0! 0 * * 5 5 1 * * 6 6 1 * * 7 7 1 * * 8 8 1 * * 9 #DIV/0! 0 * * 10 10 1 * * 11 * * * * 12 * * * * 13 4.2 * * * 14 5.25 * * *
Sheet1 *
[HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
Bye,
Jay
Basically, the workbook is set-up with individual spreadsheets. Each spreadsheet represents a month. The last spreadsheet pulls the numbers from the other spreadsheets and gives me an average for the year.
So, the last spreadsheet is set-up with Jan-Dec and pulls from a specific area on the other spreadsheets.
Hope this helps.
It seems you have an ordinary average formula in a certain cell in each month worksheet. If so, you could use a different average formula in such a worksheet: something like...Originally Posted by GordoB
=IF(SUM(Range),AVERAGE(Range),"")
the result of which would not mess up the last worksheet.
Jay,
How about the simpler...
=SUMIF(A1:A10,"<>#DIV/0!")/MAX(1,COUNT(A1:A10))
or
=SUMIF(A1:A10,"<>#DIV/0!")/MAX(1,COUNT(A1:A10)-COUNTIF(A1:A10,0))
Hi Aladin,
I am all for the alternatives. No complaints from me. Simple and efficient. Can't beat that combination.
I will go back to the VBA stuff now. I made this much more complicated than necessary. Sometimes the "whatever works..." is not the best way to do something.
Bye,
Jay
Like this thread? Share it with others