# Excluding #DIV/0! from yearly average

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

1. ## Excluding #DIV/0! from yearly average

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.

2. ## Re: Excluding #DIV/0! from yearly average

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

3. ## Re: Excluding #DIV/0! from yearly average

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
11**
2
21**
3
31**
4
#DIV/0!0**
5
51**
6
61**
7
71**
8
81**
9
#DIV/0!0**
10
101**
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.

4. ## Re: Excluding #DIV/0! from yearly average

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.

5. ## Re: Excluding #DIV/0! from yearly average

Originally Posted by 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.
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.

6. ## Re: Excluding #DIV/0! from yearly average

Jay,

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

7. ## Re: Excluding #DIV/0! from yearly average

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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•