average formula without getting div/0 error...???? - Page 2
Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 18 of 18

Thread: average formula without getting div/0 error...????

  1. #11
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    You can't use a discontiguous cell selection with COUNTIF. Instead, use...

    {=AVERAGE(IF(ROW($H$1:$H$330)={72,149,222,294,330},IF($H$1:$H$330,$H$1:$H$330)))}

    Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.

    [ This Message was edited by: Mark W. on 2002-04-09 12:53 ]

  2. #12
    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

    1. ensure none of the cells contain Div/0 or other error messages; revise them to show show blank or 0.

    2. Name the relevant cells (my example uses rN)

    3. Array enter

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

    ( I believe David Hager developed this solution)

    [ This Message was edited by: Dave Patton on 2002-04-09 12:51 ]

  3. #13
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-09 12:48, Dave Patton wrote:
    1. ensure none of the cells contain Div/0 or other error messages; revise them to show show blank or 0.

    2. Name the relevant cells (my example uses rN)

    3. Array enter

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

    ( I believe David Hager developed this solution)

    [ This Message was edited by: Dave Patton on 2002-04-09 12:51 ]
    However, be forewarned that there's a limit to the length of a defined name reference (256 characters?), and each cell reference is prefixed by its sheet name (e.g., =Sheet2!$H$72,Sheet2!$H$149,Sheet2!$H$222,Sheet2!$H$294,Sheet2!$H$330...) which imposes a "ceiling" on the number of discontiguous cells than can be listed (< 25 depending on the length of the sheet name and the magnitude of the row numbers involved).

    [ This Message was edited by: Mark W. on 2002-04-09 13:14 ]

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

    Default

    On 2002-04-09 11:26, Escher wrote:
    =SUM(H72,H149,H222,H294,H330)/MAX(1,COUNT(H72,H149,H222,H294,H330)-COUNTIF(H72,H149,H222,H294,H330,0))

    is this right?????
    Not quite... COUNTIF requires a contiguous range...

    Try:

    =SUM(H72,H149,H222,H294,H330)/MAX(1,COUNT(H72,H149,H222,H294,H330)-SUMPRODUCT((H72=0)+(H149=0)+(H222=0)+(H294=0)+(H330=0)))


  5. #15
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    =SUM(H72,H149,H222,H294,H330)/MAX(1,COUNT(H72,H149,H222,H294,H330)-SUMPRODUCT((H72=0)+(H149=0)+(H222=0)+(H294=0)+(H330=0)))
    Aladin, better take another look at this formula. The divisor is 1 (if H294 and H330 are empty) and so it's just summing.

    [ This Message was edited by: Mark W. on 2002-04-09 13:50 ]

  6. #16
    Board Regular
    Join Date
    Mar 2002
    Location
    California
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    sure use this


    {=AVERAGE(IF(A1:A6<>0,A1:A6))}

    Array formula so hit control/shift enter at the same time

  7. #17
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-09 13:53, lars wrote:
    sure use this


    {=AVERAGE(IF(A1:A6<>0,A1:A6))}

    Array formula so hit control/shift enter at the same time
    lars, been there done that... the range is discontiguous requiring this array formula...

    {=AVERAGE(IF(ROW($H$1:$H$330)={72,149,222,294,330},IF($H$1:$H$330,$H$1:$H$330)))}

  8. #18
    Board Regular
    Join Date
    Mar 2002
    Location
    California
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    my bad

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