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
Thanks so much Patrick. That definitely returns the value of the number of wins to start the season. I'm afraid I oversimplified my question though. I'm actually looking at sales data and I'm looking for the instance when the first "non zero" occurred. Unfortunately, the exact match doesn't seem to work for any value greater than 0. If you have any ideas for this scenario, I'd really appreciate it!
Try this one. I believe your version of Excel supports dynamic arrays but for anyone following this discussion on an older version, press ctrl-shift-enter to define formula as an array.
=SMALL(IF(sales<>0,ROW(sales)),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.