G
Guest
How do I average a column of numbers and exclude cells with zero values?
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.
B
Biff
Hi!
Try this:
Entered as an array using the key combination of CTRL,SHIFT,ENTER:
=AVERAGE(IF(A1:A20<>0,A1:A20))
Biff
Try this:
Entered as an array using the key combination of CTRL,SHIFT,ENTER:
=AVERAGE(IF(A1:A20<>0,A1:A20))
Biff
H
Harlan Grove
Biff wrote...
As I never tire of pointing out, better to use
=AVERAGE(IF(rng>0,rng))
because if values could be negative as well as positive, then
legitimate values could also be zero, so the only time it make sense
(mathematically) to exclude zeros is when all values should be only
positive or only negative.
....Try this:
Entered as an array using the key combination of CTRL,SHIFT,ENTER:
=AVERAGE(IF(A1:A20<>0,A1:A20))
As I never tire of pointing out, better to use
=AVERAGE(IF(rng>0,rng))
because if values could be negative as well as positive, then
legitimate values could also be zero, so the only time it make sense
(mathematically) to exclude zeros is when all values should be only
positive or only negative.
G
Guest
=AVERAGE(IF(H2:H132<>0,H2:H132)) is the function I wrote from your example
and I am still getting an error. Is there anything wrong with the function I
have typed?
Stephen
and I am still getting an error. Is there anything wrong with the function I
have typed?
Stephen
G
Guest
What does "rng" stand for in this function?
Stephen
Stephen
H
Harlan Grove
Stephen wrote...
It needs to be an array formula. You don't just type it an press
[Enter], you type it, hold down [Ctrl] and [Shift] keys and press
[Enter].
If the error is #VALUE!, then the odds are you didn't enter it as an
array formula.
If the error is #DIV/0!, then the odds are there are no nonzero numbers
in H2:H132.
If the error is anything else, then you have that error value in at
least one cell in H2:H132.
....=AVERAGE(IF(H2:H132<>0,H2:H132)) is the function I wrote from your example
and I am still getting an error. Is there anything wrong with the function I
have typed? ....
"Biff" wrote: ....
It needs to be an array formula. You don't just type it an press
[Enter], you type it, hold down [Ctrl] and [Shift] keys and press
[Enter].
If the error is #VALUE!, then the odds are you didn't enter it as an
array formula.
If the error is #DIV/0!, then the odds are there are no nonzero numbers
in H2:H132.
If the error is anything else, then you have that error value in at
least one cell in H2:H132.
H
Harlan Grove
Stephen wrote...
It's a placeholder for whatever range address you want to use.
....What does "rng" stand for in this function?
It's a placeholder for whatever range address you want to use.
D
daddylonglegs
If I may be permitted......
The "rng" in Harlan's formula is simply "range", i.e. in your case
H2:H132
Following Harlan's argument I assume you have only positive values and
zeroes and you can therefore use
=AVERAGE(IF(H2:H132>0,H2:H132))
This formula needs to be confirmed with CTRL+SHIFT+ENTER (as Biff says)
so that curly braces appear around the formula in the formula bar,
alternatively you can use a formula which just requires ENTER
=SUM(H2:H132)/MAX(1,COUNTIF(H2:H132,">0"))
The "rng" in Harlan's formula is simply "range", i.e. in your case
H2:H132
Following Harlan's argument I assume you have only positive values and
zeroes and you can therefore use
=AVERAGE(IF(H2:H132>0,H2:H132))
This formula needs to be confirmed with CTRL+SHIFT+ENTER (as Biff says)
so that curly braces appear around the formula in the formula bar,
alternatively you can use a formula which just requires ENTER
=SUM(H2:H132)/MAX(1,COUNTIF(H2:H132,">0"))