1. ## 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. ## Re: Average (2000 SR1)

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

Ian.

3. ## 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. ## 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>

5. ## Re: Average (2000 SR1)

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

6. ## 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. ## 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>

8. ## 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
•