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
Hi Patrick, thank you so much for taking a stab at it. There was a slight issue with this formula as it would find the zeroes that may have occurred after the first month of sales. For example, if sales were 0,0,0,0,100,50,0,100,200,etc the value being returned was 7. This one did the trick: = XMATCH(TRUE, sales>0) - 1
Good to know. I tested it here without any errors.