AVERAGE EXCLUDING SOME CELLS

[Q45]

How do I write a formula to average about 10 specific cells but I need to exclude any 0's from the average?
Example: Average cells A1, C1, D3, F4, G5 but if any one of those is a zero I don't want it to be included because it will skew the average.
Thanks!

 

[Dave Patton]

try the following Array formula; enter it with Ctrl-shift-Enter (CSE)

=SUM(rN)/SUM(N(LARGE(rN,ROW(INDIRECT("1:"&COUNT(rN))))>0))

the individual cells are named rN

adapted from an array formula submitted by
David Hager

 

[Q45]

Thanks everyone! I give them a try.

 

[nisht]

I am writing a sample formula you can edit according to your range.

=AVERAGE(IF(F22:F26<>0,F22:F26))

you need you enter this formula array formula

ni****h desai

 

[Aladin Akyurek]

On 2002-03-15 23:39, nisht wrote:
I am writing a sample formula you can edit according to your range.

=AVERAGE(IF(F22:F26<>0,F22:F26))

you need you enter this formula array formula

ni****h desai

Ni****h,

If you have a range of consecutive cells, you could use this array formula which is impervious to cells with formula-returned blanks. I still prefer the non-array formula that I posted.

The array-formula Dave Patton has posted is nice: It eliminates the need to collect the values of non-consecutive cells into a range of consecutive cells (the move Mark proposed). However, if there is any cell with a negative number, this formula alas does not compute a correct average.

Regards,

Aladin

 

[Aladin Akyurek]

On 2002-03-15 16:21, Dave Patton wrote:


try the following Array formula; enter it with Ctrl-shift-Enter (CSE)

=SUM(rN)/SUM(N(LARGE(rN,ROW(INDIRECT("1:"&COUNT(rN))))>0))

the individual cells are named rN

adapted from an array formula submitted by
David Hager

In case there are neg numbers in some of the non-consecutive cells (that is, in rN), it's safer to amend the array formula to:

=SUM(rN)/SUM(N(LARGE(rN,ROW(INDIRECT("1:"&COUNT(rN))))<>0))

Aladin