T
Todd
How do you average a column that contains formulas, when some of the formula
results are zero?
results are zero?
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.
D
Dave F
I'm assuming you want to ignore 0 values?
In that case, =SUM(A1:A10)/COUNTIF(A1:A10,">0")
This divides the sum of A1:A10 by the count of values in A1:A10 which are
greater than zero.
Dave
In that case, =SUM(A1:A10)/COUNTIF(A1:A10,">0")
This divides the sum of A1:A10 by the count of values in A1:A10 which are
greater than zero.
Dave
N
Nielz
Todd,
You could try sum / sumproduct. Ex. =SUM(A1:E1)/(SUMPRODUCT((A1:E1<>0)*1))
You could try sum / sumproduct. Ex. =SUM(A1:E1)/(SUMPRODUCT((A1:E1<>0)*1))
4
4pinoy
Tod...do you expect results from zero to positive numbers only, no blank
results??
results??
4
4pinoy
Nielz....when the divisor consider the count of negative number, then the
zero must be included in the count for the divisor....what do you think?
zero must be included in the count for the divisor....what do you think?
T
Todd
Thanks a lot. It worked.
--
Todd
--
Todd
Dave F said:I'm assuming you want to ignore 0 values?
In that case, =SUM(A1:A10)/COUNTIF(A1:A10,">0")
This divides the sum of A1:A10 by the count of values in A1:A10 which are
greater than zero.
Dave
T
Todd
Thanks
--
Todd
--
Todd
Nielz said:Todd,
You could try sum / sumproduct. Ex. =SUM(A1:E1)/(SUMPRODUCT((A1:E1<>0)*1))
4
4pinoy
Tod this one assumes the average of formula result (not considering
#N/A,#DIV...etc)
Formula Result assumed only with some blank(""), some zero (0), and some
(+/-) numbers..
simply...=sum(a1:a10)/counta(a1:a10)
#N/A,#DIV...etc)
Formula Result assumed only with some blank(""), some zero (0), and some
(+/-) numbers..
simply...=sum(a1:a10)/counta(a1:a10)