Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Average (2000 SR1)

    This is going to sound really lame, but how would you average the following cell values if the contents are greater than zero?

    B10, B20, B30, B40, B50

    B10= Average(B1:B9), B20=Average(B11:B19), etc

    I have tried all sorts of variations of SUMIF, COUNTIF, AVERAGE(IF..., etc with nary an error free formula.

  2. #2
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Average (2000 SR1)

    Is
    =AVERAGE(b10,b20,b30,b40,b50)
    what you want?

    Ian.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Average (2000 SR1)

    Mike,

    If one of the cells' contents is zero, this cell will not contribute to the average, so you can leave it in the sum. However, then, you want the total number be decreased by one, and this can be done using countif. So, maybe you can use something like (here, you have to replace the ranges with the series of B10, B20 etc.

    =SUM(A1:A7)/COUNTIF(A1:A7;"<>0")

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

    Re: Average (2000 SR1)

    Hans was close, and his formula would work if there were no negative values. The following should average only the values greater than zero.

    <pre>=SUMIF(B1:B8,">0")/COUNTIF(B1:B8,">0")
    </pre>

    Legare Coleman

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Average (2000 SR1)

    Sorry, didn't notice that negative values were to be discarded.

  6. #6
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Average (2000 SR1)

    Actually, I think I confused the issue by including how cells B10, B20, etc were calculated.

    What I am trying to do is average the values in cells B10, B20, B30, B40, B50 that are greater than zero.
    I cannot get any of the ideas to work when the cells are not contiguous(sp?).

    =AVERAGE(B10,B20,B30,B40,B50) will work if the values are all greater than zero. For zero values it only dilutes the mix.

    Thanks,

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

    Re: Average (2000 SR1)

    The following should do it:

    <pre>=SUM(IF(B10>0,B10,0),IF(B20>0,B20,0),IF(B30>0 ,B30,0),IF(B40>0,B40,0),IF(B50>0,B50,0))/SUM(IF(B10>0,1,0),IF(B20>0,1,0),IF(B30>0,1,0),IF(B 40>0,1,0),IF(B50>0,1,0))
    </pre>

    Legare Coleman

  8. #8
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Average (2000 SR1)

    Thanks Legare, pretty ingenious way of counting the number of >0 values.

Posting Permissions

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