J
Jeremy Davis
What is the formula to exclude zero values in an average formula?
-Jeremy
-Jeremy
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
J
J.E. McGimpsey
one way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):
=AVERAGE(IF(A1:A100=0,"",A1:A100))
=AVERAGE(IF(A1:A100=0,"",A1:A100))
J
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
=SUM(A1:A10)/COUNTIF(A1:A10,"<>0")
would also work.
HTH
Jason
Atlanta, GA
N
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.
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.
A
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))}
=SUM(A1:A10)/(COUNT(A1:A10)-COUNTIF(A1:A10,0))
in order to meet the scope of...
{=AVERAGE(IF(A1:A10,A1:A10))}
M
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.
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.
N
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.
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.