Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Sep 2001
    Location
    USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Formula (Excel 97 SR2)

    Hello! I am looking for a formula to average a column of numbers but not count the 0's in the average. I am currently using AVERAGE but it takes the 0's into account. I am sure there is a way to do this but I am having a brain freeze. Thanks!!

    Example: 23, 100, 50, 0, 44, 60, 30 = 307 / 6 = 51.17 I want to divide by 6 rather than 7.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel Formula (Excel 97 SR2)

    use for example: (assuming A1:z1 is the range of interest)

    sumif(a1:z1,"<>0")/countif(a1:z1,"<>0")

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Formula (Excel 97 SR2)

    Hello Marie,

    In the book Excel 2000 Formulas by John Walkenbach, he gives examples of how to return the average of a range without including zeros:

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

    This array formula also works:

    =AVERAGE(IF(A1:A5<>0,A1:A5))

    when you are done entering this formula press the following keys CTL+SHIFT+ENTER

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel Formula (Excel 97 SR2)

    sumif / countif can have advantages over sum/countif if there are any cells with errors as it will ignore them also.

    Steve

  5. #5
    New Lounger
    Join Date
    Sep 2001
    Location
    USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Formula (Excel 97 SR2)

    I think I have to give a better example. I don't have a range of cells but rather set cells in a form.

    Example: (F231+F243+f254+f265+f276) I don't think the Countif will work.

    This is what I have so far....it returns a value but it isn't correct as it is only adding.

    =AVERAGE(IF(F231+F243+f254+f265+f276,>0,F231+F243+ f254+f265+f276))

    THANKS!!!!

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Excel Formula (Excel 97 SR2)

    If the number of cells involved is not much different from your example someting like

    SUM(F231,F243,F254,F265,F276)/COUNT(F321,F243,F254,F265,F276,">0")

    should work

    Andrew C

  7. #7
    New Lounger
    Join Date
    Sep 2001
    Location
    USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Formula (Excel 97 SR2)

    Nope still doesn't work. Do I have something incorrect?

    SUM(F231,F243,F254,F265,F276)/COUNT(F231,F243,F254,F265,F276,">0")

    Basically it is saying 3+3+3+3+3+0 =12 / 5 = 2.4 It should be 3

  8. #8
    New Lounger
    Join Date
    Sep 2001
    Location
    USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Formula (Excel 97 SR2)

    I can't even space the rows by 11 as this is a form.

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel Formula (Excel 97 SR2)

    Then you will have to brute-force and hardcode the cells:

    =SUM(F231+F243+F254+F265+F276)/SUM(F231>0,F243>0,F254>0,F265>0,F276>0)

    Steve

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel Formula (Excel 97 SR2)

    If you evenly space the rows by 11
    F231+F243+f254+f265+f276 to
    F232+F243+f254+f265+f276

    you could use the array formula (ctrl-shift-enter to confirm)
    =AVERAGE(IF((MOD((ROW($F$232:$F$277)-232),11)=0)*($F$232:$F$277>0),$F$232:$F$279))

    Steve

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Formula (Excel 97 SR2)

    Two things:

    1- COUNT(F231,F243,F254,F265,F276,">0") does not make sense. The first five arguments will be counted if the corresponding cells contain a number, even if that number is zero. The last argument will never be counted since it is a string (">0") and not a number.

    2- The SUM function has five arguments, but your 3+3+3+3+3+0 example is six numbers.

    I think that you will need a formula something like this:

    <pre>=SUM(F231,F243,F254,F265,F276)/SUM(F231>0,F243>0,F254>0,F265>0,F276>0)
    </pre>

    Legare Coleman

Posting Permissions

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