G
Guest
I am trying to average a column of numbers. I want to only average the cells
that contain a number greater than 0. How do I do that?
that contain a number greater than 0. How do I do that?
G
Guest
you can use a pivot table, or auto filter with this condititional (greater
than 0) and in this case use =subtotal(1,a2:a500)
hth
regards from Brazil
Marcelo
"JimNColorado" escreveu:
than 0) and in this case use =subtotal(1,a2:a500)
hth
regards from Brazil
Marcelo
"JimNColorado" escreveu:
D
Don Guillett
this is an array formula that must be entered/edited using ctrl+shift+enter
=AVERAGE(IF(E1:E21>0,E1:E21))
=AVERAGE(IF(E1:E21>0,E1:E21))
D
Don
Jim
=AVERAGE(IF(D2
18<>0,D218))
Enter as an array Ctrl/shift/enter
Don
=AVERAGE(IF(D2
18<>0,D218))Enter as an array Ctrl/shift/enter
Don
D
Don
Jim
opps! Should be:
=AVERAGE(IF(B2:B18>0,B2:B18))
Don
opps! Should be:
=AVERAGE(IF(B2:B18>0,B2:B18))
Don
G
Guest
I'll admit my bias against array formulas and offer an alternative...
Average is simply sum/count. So =sumif(range,">0")/countif(range,">0") will
average the positive values.
Average is simply sum/count. So =sumif(range,">0")/countif(range,">0") will
average the positive values.
S
Sasa Stankovic
I support this result as the most logical....
bpeltzer said:
G
Guest
That worked. Thanks. I would have never figured it out.
D
Don Guillett
why?
S
Sasa Stankovic
because I don't like array functions... it is not something special... I
just dont like to use them...
just dont like to use them...
R
Ragdyer
If it's *most* logical to you, may I ask then, why would you use
Sumif(range,">0").
Seems illogical to *need* " add if it's greater then zero", *unless* there's
a stipulation to exclude negative values.
A simple:
Sum(range)/Countif(range,">0")
would be adequate without further qualification from the OP.
What do you think?
Sumif(range,">0").
Seems illogical to *need* " add if it's greater then zero", *unless* there's
a stipulation to exclude negative values.
A simple:
Sum(range)/Countif(range,">0")
would be adequate without further qualification from the OP.
What do you think?
S
StephenAccountant
What if I want to do the same thing but I want to include negative values.
For example
A1 B1 C1 D1 E1
1000 1500 -400 300
So I want to average A1 to D1 but i also want to have the formula include
cell E1 because there may be times when E1 has a value - positive or negative.
For example
A1 B1 C1 D1 E1
1000 1500 -400 300
So I want to average A1 to D1 but i also want to have the formula include
cell E1 because there may be times when E1 has a value - positive or negative.
G
Gord Dibben
You want the average of 5 cells, not just the AVERAGE of A1:E1?
=SUM(A1:E1)/5 returns 480
AVERAGE(A1:E1) returns 600 because AVERAGE ignores blanks.
Whatever suits you.
Gord Dibben MS Excel MVP
=SUM(A1:E1)/5 returns 480
AVERAGE(A1:E1) returns 600 because AVERAGE ignores blanks.
Whatever suits you.
Gord Dibben MS Excel MVP
S
StephenAccountant
No what I want is for the formula to ignore all Zero values.
So if only 4 out of the 5 cells have a value - positive or negative - i want
it to average the 4 cells not the 5
But if all 5 cells have values - positive or negative - i want it to average
the 5 cells.
So if only 4 out of the 5 cells have a value - positive or negative - i want
it to average the 4 cells not the 5
But if all 5 cells have values - positive or negative - i want it to average
the 5 cells.