Average Functin

[Guest]

How do you average a column that contains formulas, when some of the formula
results are zero?

 

[Guest]

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

 

[Nielz]

Todd,

You could try sum / sumproduct. Ex. =SUM(A1:E1)/(SUMPRODUCT((A1:E1<>0)*1))

 

[Guest]

Tod...do you expect results from zero to positive numbers only, no blank
results??

 

[Bob Phillips]

=AVERAGE(IF(A1:A100<>0,A1:A100))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

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?

 

[Guest]

Thanks a lot. It worked.
--
Todd

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

 

[Guest]

Thanks
--
Todd

Todd,

You could try sum / sumproduct. Ex. =SUM(A1:E1)/(SUMPRODUCT((A1:E1<>0)*1))

 

[Guest]

Thank you.
--
Todd

=AVERAGE(IF(A1:A100<>0,A1:A100))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

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)