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
fix the div 0 first
if(iserror(your formula),0,your formula)
then use
something like
=SUM(A1:A100)/(SUMPRODUCT(--(A1:A100<>0)))
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
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
Last edited by DonkeyOte; 10-31-2009 at 11:31 AM.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
i did say
fix the div 0 first
Yes I edited my post when I saw thatOriginally Posted by martindwilson
-- 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.
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.
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.
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.
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?
No, my turnor am i going daft?
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.
Last edited by DonkeyOte; 10-31-2009 at 12:03 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks