Calculating an averags for a dynamic range between zeros

[grantjw]

I have a column of numbers representing each hour in a month (720 or 744 rows). I need to find the average of consecutive number blocks that fall between zero values. The block can be as small as two numbers or it could be as large as the whole month (no zero values in the column).

For example take a column of numbers 10,5,0,0,3,5,6,7,0,2,5,4,3,4. So I would need to find the average of the first block 10,2 then skipping 0,0 find another average of the next block 3,5,6,7, then skip 0 and find the average of the next block 2,5,4,3,4 and so on.

Any help would be appreciated. Thanks!

 

[WaterGypsy]

I've split this into three calculations for simplicity but you might be able to combine them.
Assume your data is in column A with a heading in row 1.
In cell B2 put the following formula: =IF($A2=0,0,IF(ISNUMBER($B1),$B1,0)+$A2)
In cell C2 put the following formula: =IF($A2=0,0,IF(ISNUMBER($C1),$C1,0)+1)
and in D2 put the following formula: =IF(C2=0,"",IF(C3=0,B2/C2,""))

Column B total each set of numbers
Column C counts the elements
Column D shows the averages

Hope this helps

 

[grantjw]

I've split this into three calculations for simplicity but you might be able to combine them.
Assume your data is in column A with a heading in row 1.
In cell B2 put the following formula: =IF($A2=0,0,IF(ISNUMBER($B1),$B1,0)+$A2)
In cell C2 put the following formula: =IF($A2=0,0,IF(ISNUMBER($C1),$C1,0)+1)
and in D2 put the following formula: =IF(C2=0,"",IF(C3=0,B2/C2,""))

Column B total each set of numbers
Column C counts the elements
Column D shows the averages

Hope this helps

You Feedback was extremely helpfull!
The Average for the first block 10,5 was not calculating correctly. I had to add
In Cell B1 put the following formula: =IF($A1>0,$A1,0)
In Cell C1 put the following formula: =IF($A1>0,1,0)

It works perfectly. Thanks to you and many others on this site who volunteer their knowledge. This will save many wasted hours on cut and copy operations!


JWG