Ignoring 0 and #DIV/0

**wwsd** · 10-31-2009, 12:18 PM

I have a column I want to average however it contains zero's and #DIV/0 entries in it. Those entries are from linked sheets that have not been populated and are supposed to be there. I want to average the column that contains these values but want the values excluded from the calculation until they are populated with real numbers.

Anyone have any ideas on how to do this? Thanks in advance for any help you can give.

Steve

**martindwilson** · 10-31-2009, 12:28 PM

fix the div 0 first
if(iserror(your formula),0,your formula)
then use
something like
=SUM(A1:A100)/(SUMPRODUCT(--(A1:A100<>0)))

**DonkeyOte** · 10-31-2009, 12:28 PM

Martin, I'm afraid if the source values contain #DIV/0! you can not SUM in the first instance
edit saw your point re: correcting first, that said if you correct first then use a COUNTIF for Divisor - no need for SUMPRODUCT as I see it.

If you can't correct the underlying formulae you will need to use an Array

=AVERAGE(IF(ISNUMBER(A1:A100),IF(A1:A100<>0,A1:A100)))
confirmed with CTRL + SHIFT + ENTER

adjust ranges as necessary but whenever dealing with Arrays try to keep the ranges as "lean" as possible

**martindwilson** · 10-31-2009, 12:30 PM

i did say
fix the div 0 first

**jj72uk** · 10-31-2009, 12:30 PM

Ideally you'd want to aviod DIV/0 errors, this can be overcome by using IF with ISERROR or you can use IF with DENOMINATOR

Otherwise you could use a simple IF statement

=IF(SUM(A1:A100)=0,0,AVERAGE(A1:A100))

Where A1:A100 is the range you want to average.

**DonkeyOte** · 10-31-2009, 12:33 PM

Originally Posted by martindwilson

i did say
fix the div 0 first

Yes I edited my post when I saw that

-- no need for SUMPRODUCT though... SUMIF/COUNTIF should suffice.

jj72uk, the issue is 0's (in addition to errors) are to be excluded from the AVERAGE ... an AVERAGE on it's own will not suffice... if using 2007 you can use AVERAGEIF else see prior posts.

**wwsd** · 10-31-2009, 12:37 PM

Thanks for the responses. The #DIV/0 entries come from other sheets that are linked to the summary sheet of which I am trying to obtain this formula for.

I see where some are saying to fix the #DIV/0 error first. Do you mean go back to the sheets (which haven't been populated yet) and correct them there? Just some background on these unpopulated sheets they are for the months of November and December.......hence no data in these yet. I'm trying to make life easier by having all the months ready to go and linked to the summary sheet so that I can just look at that sheet to see where I am during any point in the year.

**DonkeyOte** · 10-31-2009, 12:46 PM

If the #DIV/0! emanate elsewhere you can use an ISERROR test to prevent them from being returned to this sheet...

=IF(ISERROR(linkedcell),"",linkedcell)

That said you could extend further

=IF(ISERROR(linkedcell),"",IF(N(linkedcell)=0,"",linkedcell))

the above would ensure only numeric values other than 0 are returned and you can run a standard AVERAGE.

If any / all of the above seems a little long winded just use the array as advised in post # 2 ... if the ranges aren't vast it shouldn't be too much of an issue.

**martindwilson** · 10-31-2009, 12:54 PM

surely countif is no good if you have -ve values ?
COUNTIF(A1:A15,"<>"&0) counts blanks and
COUNTIF(A1:A15,">"&0) ignores -ve's
sumproduct doesnt and allows you to extend the range for future input
or am i going daft?

**DonkeyOte** · 10-31-2009, 01:01 PM

or am i going daft?

No, my turn

I hadn't thought about the -ve's

That said, given I'm (openly) petty I'd opt for

=SUM(A1:A15)/SUM(COUNTIF(A1:A15,{"<0",">0"}))

OK .. no I wouldn't

I would say though in all honesty that if I find myself using a SUMPRODUCT to calculate either side for an Average I generally opt for an AVERAGE Array - performance wise there's little difference, it's generally more flexible (errors in source) and the latter is "generally" shorter chars wise
(not here I concede but that's because of the additional isnumber check given I've assumed errors persist).

My 2c.