Tuesday, March 18, 2014

yogi_Cumulative Average of Successive Values In Column A Greater Than 10 -- Reset To 0 If Value Less Than 10 Is Encountered And Start Afresh

                                         Google Spreadsheet   Post  #1564
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Mar-18-2014
post by Hans Clough (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/pY7fL0pBuMo)
Formula question
I need a formula that applies only when certain conditions exist. Example:

a1=11
a2=6
a3=25
a4=15
a5=50
a6=2
a7=37

I want the average of the numbers >10 as long as all the numbers >10. As soon as a number drops below 10, I want the formula to reset, and the result to enter as zero.

In this case, the formula would deliver
b1=11
b2=0
b3=25
b4=20
b5=30
b6=0
b7=37

And so on. My actual formula is more complicated, but this is the missing piece.

Thanks much!
---
https://docs.google.com/spreadsheet/ccc?key=0Aqs_pdsxr236dGNja2xhbVBXWjBrN2RqYU0xR1gzdUE#gid=0

The first is the initial data set. 

The second is what it should (assuming my rushed math is correct).

The third is what Rafael suggested.

the fourth is what Glynwood Suggested.

You should be able to make comments on this. 

I already explained the logic behind it, but I'll reword it for you. I want it to give me the average (or whatever) of all the numbers before the current row up until it reaches a number that is <10. The idea is that the averages are contained within the the numbers that are <10. Hope that makes sense.
-------------------------------------------------------------------------------------------------------------------------------------------------



No comments:

Post a Comment