HELP ME PLEASE!!!!! average while ignoring zeros!
HELP ME PLEASE!!!!!  average while ignoring zeros!
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: HELP ME PLEASE!!!!! average while ignoring zeros!

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Please let me know how to do an average of a group of numbers while ignoring any zeros! the help thing in excel did not work at all. HOW DO YOU DO THIS PLEASE HELP ME!!! I NEED TO KNOW!

  2. #2
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,136
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    See if these choices are any help.

    Thanks to Aladin and Mark W for answering this question many times on this board.

    Assuming a range of A1:A10,

    Non array formula
    =SUM(A1:A10)/MAX(1,SUMPRODUCT((A1:A10<>0)*(ISNUMBER(A1:A10))))

    Array (Ctrl+Shift+Enter)
    =AVERAGE(IF(A1:A10,A1:A10))

    Tom Urtis


    Sorry, forgot a shorter non-array from Aladin as well:
    =SUM(A1:A10)/MAX(1,COUNTIF(A1:A10,">0"))

    T.U.

    [ This Message was edited by: Tom Urtis on 2002-03-01 20:53 ]

  3. #3
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,581
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I bow to MrExcel MVP, but would like to ask:

    Why do you need the *max* part?

    This worked for me:

    =SUMIF(A1:A4,">0")/COUNTIF(A1:A4,">0")
    ~Anne Troy

  4. #4
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,136
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    As noted, these were previous suggestions from others (much more formula-savvy than me), and the first ones I remembered, though both individuals have also posted shorter formula solutions for this question that were not reflected in my reply. Thanks for the tip.

    Tom U.

  5. #5
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,581
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Doh! Thanks Tom!
    ~Anne Troy

  6. #6
    New Member
    Join Date
    Feb 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ok thanks for that, im impressed at the speed of the replies. However now i have another problem. I still want to ignore zeros but now the numbers are still in the same colum, but not all in order like A1:A10 more like A1, A3, A14 etc... How do you do that, i tried seperating them by commas but that didnt seem to work. Thank you.

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

    Default

      
    On 2002-03-01 21:05, Dreamboat wrote:
    I bow to MrExcel MVP, but would like to ask:

    Why do you need the *max* part?

    This worked for me:

    =SUMIF(A1:A4,">0")/COUNTIF(A1:A4,">0")
    Consider

    {0.00,0.00,"",0.00}

    in A1:A4.

    Applied to the above set of values

    [1]

    =SUMIF(A1:A4,">0")/COUNTIF(A1:A4,">0")

    will result in #DIV/0!, while

    [2]

    =SUM(A1:A4)/MAX(1,COUNTIF(A1:A4,">0"))

    will compute 0.

    The formula in [2] assumes the benign condition that the range of interest does not house negative numbers. To average positive non-zero values, it needs to be rewritten as:

    [3]

    =SUMIF(A1:A4,">0")/MAX(1,COUNTIF(A1:A4,">0"))

    In order to average both neg and pos values excluding zeroes, [2] must be expanded to:

    [4]

    =SUM(A1:A4)/MAX(1,COUNTIF(A1:A4,">0")+COUNTIF(A1:A4,"<0"))

    which is exactly eq to the array-formula

    [5]

    {=AVERAGE(IF(A1:A4,A1:A4))}

    By the way, the formula in [5] cannot cope (without modification) with the formula returned blanks in the range of interest, while [4] would.

    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