Clearing the "DIV/0!" message.

[GBL]

Hi Guys (and Gals):
I have the function AVERAGE(A1,C1,E1,G1) working fine; except that it
produces the message "DIV/0!" when there are no entries in A1, C1, E1, and
G1. Some time ago I noticed a tip on how to eliminate this "DIV/0!" message
(hope I've got the correct newsgroup).
Anyone recall the tip??

 

[Paul B]

GBL, here is one way

=IF(ISERROR(AVERAGE(A1,C1,E1,G1)),"",AVERAGE(A1,C1,E1,G1))


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 97 & 2000
** remove news from my email address to reply by email **

 

[Bernard V Liengme]

This will help =IF(ISERROR(AVERAGE(A1,C1,E1,G1)),"",AVERAGE(A1,C1,E1,G1))
Bernard

 

[GBL]

Hi Paul:
Thanks for the valid reply!! While waiting for a reply I found the
reference. Was in John Walkenbach's book (page 505) - which was the same
logic as yours. Thanks again!!!

 

[Harlan Grove]

I have the function AVERAGE(A1,C1,E1,G1) working fine; except that it
produces the message "DIV/0!" when there are no entries in A1, C1, E1, and
G1. Some time ago I noticed a tip on how to eliminate this "DIV/0!" message
(hope I've got the correct newsgroup).
Anyone recall the tip??

Others have shown you how to do this with error trapping.

However, indiscriminate error trapping is a bad thing when avoidable because
it's generally a good thing to see #REF!, #NAME?, #NULL! and #NUM! errors rather
than nothing ("") when they flow through since those errors usually indicate
serious problems elsewhere. For #DIV/0! from AVERAGE especially there are better
ways to avoid showing the error.

=IF(COUNT(A1,C1,E1,G1),AVERAGE(A1,C1,E1,G1),"")

Granted this doesn't display error values if none of these cells contained
numbers but some evaluated as errors, but it does a better job of screening
#DIV/0! only when it's due to no numbers in these 4 cells but passing through
#DIV/0! as the value of any of these cells when one or more of the other cells
evaluates as numbers.