Forum Discussion
Solved
Formula to calculate streak at the beginning of an array
Hello, I am stumped. I can calculate the current streak by using lookup or index / match. These use backwards looking logic. But what is the function that uses forward looking logic? I am specif...
Since I think I have a reasonably simple solution, it probably means I have failed to understand the problem!
= XMATCH(TRUE, sales>0) - 1 or, for the original problem, = XMATCH(TRUE, results<>TAKE(results,1)) - 1
Since I think I have a reasonably simple solution, it probably means I have failed to understand the problem!
= XMATCH(TRUE, sales>0) - 1
or, for the original problem,
= XMATCH(TRUE, results<>TAKE(results,1)) - 1
- Hey Peter, I hope you're enjoying this holiday season. So now that I am able to count the number of "leading" months with no sales, I've run into another stumper. I want to take averages, standard deviations, etc of my sales history but exclude those leading zeroes. Would you care to take a stab at how to only begin averaging only starting at the first month of sales? I.E. if I had sales in all 24 months, it would be =average(a2:1x2). But, say the first 2 months had no sales, I want the average to begin at cell c2.
The image shows various possible interpretations of 'averages'
These may be given by
= AVERAGE(sales) = LET( start, XLOOKUP(TRUE,sales<>0, sales,,,1), finish, TAKE(sales,-1), range, start:finish, AVERAGE(range) ) = LET( start, XLOOKUP(TRUE,sales<>0, sales,,,1), finish, XLOOKUP(TRUE,sales<>0, sales,,,-1), range, start:finish, AVERAGE(range) ) = AVERAGEIFS(sales, sales,"<>0")The two formulas involving range composition are probably of greatest interest.