Average Fucntion

[Jeremy Davis]

What is the formula to exclude zero values in an average formula?

-Jeremy

 

[J.E. McGimpsey]

one way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

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

 

[Jason Morin]

Not sure if this is faster than an array, but...

=SUM(A1:A10)/COUNTIF(A1:A10,"<>0")

would also work.

HTH
Jason
Atlanta, GA

 

[Norman Harker]

Hi Jason!

Out of curiosity, I ran a crude check and it does run about 20% faster
than the array formula equivalent. Usually with a one off entry time
won't be a consideration these days but I tend to prefer non-array
solutions and often , for novices, they are easier to understand.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Wednesday Public Holidays: Croatia (Statehood Day); Mozambique
(Independence Day); Slovenia (Slovenia Day)
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Aladin Akyurek]

That sounds interesting. I feel the formula should be changed to...

=SUM(A1:A10)/(COUNT(A1:A10)-COUNTIF(A1:A10,0))

in order to meet the scope of...

{=AVERAGE(IF(A1:A10,A1:A10))}

 

[Myrna Larson]

The 1st formula {=AVERAGE(IF(A1:A10,A1:A10))} gives an error if there are cells containing
text in the range. If that's a possibility, your suggested formula is required. If it isn't --
the data is all numeric or empty -- the AVERAGE formula is sufficient.

If there are error values in the range, neither one filters those out.

 

[Norman Harker]

Hi Aladin!

I was just looking at a comparison of that array formula and the
non-array formula. But perhaps not a good comparison as, you've noted,
they are not equivalents with different data.

But on like for like I'll go for your non-array entered formula in
preference to array entered. I don't think that time aspects are
likely to be relevant very often with an average even if they are much
different.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Thursday Public Holidays: None (which must be a reason to celebrate).
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.