Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: ignoring zero values in calculations

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

    Default

    I cells A1:A3 I have the numbers 5,0,5 respectivly. In Cell A4, I have the formula Average(A1:A3). I want the zero value to be ignored in the calculation, so that the result of the average is "5". Data is continuously added to the spreadsheet, and I never know which of the cells will have a zero value, so just averaging cells A1 and A3 is not an option.

    Any ideas?

  2. #2
    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-05-12 13:16, Kourada wrote:
    I cells A1:A3 I have the numbers 5,0,5 respectivly. In Cell A4, I have the formula Average(A1:A3). I want the zero value to be ignored in the calculation, so that the result of the average is "5". Data is continuously added to the spreadsheet, and I never know which of the cells will have a zero value, so just averaging cells A1 and A3 is not an option.

    Any ideas?
    {=AVERAGE(IF(A1:A3,A1:A3))}

    Note: This is an array formula which must be entered using the Control+Shift+Enter key combination.

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

    Default

    Thanks, that works perfectly.....but why? I don't get it. Why was the zero ignored using that array formula?

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

    Default


    Array enter (Ctrl-Shift-Enter)one of the following:

    =IF(COUNTIF(A1:C1,">0"),AVERAGE(IF(A1:C1=0,"",A1:C1)),"")

    =IF(COUNTIF(A1:C1,">0")=0,"",AVERAGE(IF(A1:C1=0,"",A1:C1)))

    =AVERAGE(IF(A1:C1,A1:C1)) gives error if all numbers are 0.


  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,035
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default



    An efficient formula would be:

    =SUM(A1:A3)/MAX(1,COUNT(A1:A3)-COUNTIF(A1:A3,0))


  6. #6
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    How about just

    =SUM(A1:A3)/COUNTIF(A1:A3,">0")

    Regards!

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

    Default

    On 2002-05-12 13:48, Yogi Anand wrote:

    How about just

    =SUM(A1:A3)/COUNTIF(A1:A3,">0")

    Regards!
    You're excluding negative numbers. Also, you might run into #DIV/0!


  8. #8
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You're excluding negative numbers. Also, you might run into #DIV/0!
    Good thinking Aladin: I am revising it to:

    =SUM(A1:A3)/COUNTIF(A1:A3,"<>0")

    regards!






  9. #9
    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-05-12 13:30, Kourada wrote:
    Thanks, that works perfectly.....but why? I don't get it. Why was the zero ignored using that array formula?
    When A1:A3 contains {5;0;5} the array formula, {=IF(A1:A3,A1:A3)}, produces {5;FALSE;5} because IF treats all non-zero values as TRUE and zero values as FALSE. The array formula, {=IF(A1:A3,A1:A3)}, returns the numbers (in A1:A3) themselves when TRUE and the boolean value, FALSE, when 0. The AVERAGE function ignores boolean (logical) values. So =AVERAGE({5;FALSE;5}) produces 5.

    [ This Message was edited by: Mark W. on 2002-05-12 15:05 ]

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,035
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default


    =SUM(A1:A3)/COUNTIF(A1:A3,"<>0")


    Try:

    {0;0;0}

    which are real 0's.

Some videos you may like

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
  •