Forum Discussion
Dynamic Average based off the position of the first non-zero data point
Hello,
This is piggy-backing off a previous question that was solved in finding the number of "leading zeroes" in monthly sales data. I am looking at the previous 24 months (columns a:x) and then column y counts the number of leading zeroes when present. For example,
Row 2 = (100,50,150,0,125,100,etc) the number of leading zeroes in column Y is zero
Row 3 = (0,0,50,0,100,200,300,etc) the number of leading zeroes in column Y is two
What I want to do is calculate the average, standard deviation, etc with varying starting points by row, based off where the first "non-zero" occurred. In row 2, the first non-zero occurred in column a. In row 3, the first non-zero occurred in column c. Thanks all!
6 Replies
=(SUM(INDEX(A2:X2,,MATCH(1,--(A2:X2>0),0)):INDEX(A2:X2,,24)))/(25-MATCH(1,--(A2:X2>0),0))
For the average calculation you can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or Excel 2021.
=STDEV.P(INDEX(A2:X2,,MATCH(1,--(A2:X2>0),0)):INDEX(A2:X2,,24))
This is the formula for the standard deviation in cell Z2. Enter the formula with ctrl+shift+enter if you don't work with Office365 or Excel 2021.
- Thanks Pawn! This worked. An alternate solution provided which also worked was =AVERAGE(XLOOKUP(TRUE,A2:X2<>0,A2:X2,X2):X2)
Happy holidays to you and yours.You are welcome. Instead of the long-winded formula i originally suggested for average one can apply this formula:
=AVERAGE(INDEX(A2:X2,,MATCH(1,--(A2:X2>0),0)):X2)
However since you have access to XLOOKUP the solution by Lorenzo is the best way for this.
Happy holidays to you and yours as well.
