How do you average a column that contains formulas, when some of the formula
results are zero?
--
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
--
Brevity is the soul of wit.


"Todd" wrote:

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

Todd,

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

"Todd" skrev i en meddelelse
...
How do you average a column that contains formulas, when some of the
formula
results are zero?
--
Todd

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

"Todd" wrote:

How do you average a column that contains formulas, when some of the formula
results are zero?
--
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)

"Todd" wrote in message
...
How do you average a column that contains formulas, when some of the
formula
results are zero?
--
Todd

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?

"Nielz" wrote:

Todd,

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

"Todd" skrev i en meddelelse
...
How do you average a column that contains formulas, when some of the
formula
results are zero?
--
Todd

Thanks a lot. It worked.
--
Todd


"Dave F" wrote:

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
--
Brevity is the soul of wit.


"Todd" wrote:

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

Thanks
--
Todd


"Nielz" wrote:

Todd,

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

"Todd" skrev i en meddelelse
...
How do you average a column that contains formulas, when some of the
formula
results are zero?
--
Todd

Thank you.
--
Todd


"Bob Phillips" wrote:

=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)

"Todd" wrote in message
...
How do you average a column that contains formulas, when some of the
formula
results are zero?
--
Todd

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)

"Todd" wrote:

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