Excluding #DIV/0! from yearly average

GordoB

New Member
Joined
May 9, 2003
Messages
12
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.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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.
Book2
ABCD
111
221
331
4#DIV/0!0
551
661
771
881
9#DIV/0!0
10101
11
12
134.2
145.25
Sheet1
 
Upvote 0
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.
 
Upvote 0
GordoB said:
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.
 
Upvote 0
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))
 
Upvote 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. :coffee: I made this much more complicated than necessary. Sometimes the "whatever works..." is not the best way to do something.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top