Thanks:  0
Likes:  0

# Thread: AVERAGE EXCLUDING SOME CELLS

1. 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

2. Thanks everyone! I give them a try.

3. 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

4. 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,

5. 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))

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•