AVERAGE EXCLUDING SOME CELLS - Page 2
Manage your personal finances in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

Thread: AVERAGE EXCLUDING SOME CELLS

  1. #11
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,327
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

     


    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. #12
    New Member
    Join Date
    Feb 2002
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks everyone! I give them a try.

  3. #13
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #14
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,752
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    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

  5. #15
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,752
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

      
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com