Finding the Average

[Mike H.]

I want to average an entire row, but there are cells with the value of zero.
I only want to average the cells that have a value greater than zero. The
problem is this. I don't want to clear out the cells with the value of zero
because they are programmed with formulas. I need the average to continue to
modify when these cells value are greater than zero.

 

[FSt1]

hi
=SUM(A1:F1)/COUNTIF(A1:F1,">0")

Adjust to fit your data.

Regards
FSt1

 

[Bob Phillips]

=AVERAGE(IF(1:1>0,1:1))

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


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Joel]

You can create your own average

=SUMIF(4:4,">0")/COUNTIF(4:4,">0")

 

[Dave F]

=AVERAGE(IF(1:1>0,1:1))

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

(there's no email, no snail mail, but somewhere should be gmail in my addy)






- Show quoted text -

If you're using XL 07, you can use the AVERAGEIF function and avoid
having to use an array formula.

Dave