Excluding #DIV/0! from yearly average

[GordoB]

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.

[Aladin Akyurek]

What is the range? Are the upcoming months cells really empty or do they house real 0's?

[Jay Petrulis]

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
A1A2A3A4A5A6A7A8A9A10A13A14 =
	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.

[GordoB]

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.

[Aladin Akyurek]

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

=IF(SUM(Range),AVERAGE(Range),"")

the result of which would not mess up the last worksheet.

[Aladin Akyurek]

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))

[Jay Petrulis]

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.