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
=MATCH(1,($C$2:$C$28=E2)*($D$2:$D$28="l"),0)-MATCH(1,($C$2:$C$28=E2)*($D$2:$D$28="w"),0)An alternative could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
- Thanks Pawn. It definitely works in this scenario. I'm afraid I oversimplified my question though. I'm actually looking through sales data by month and I'm trying to count the number of months of zeroes before the first month that we had an order. So for example, in months 1-4, sales were zero. then in month 5, sales were 100. Months 6-12 sales varied from 0 to 200. If you're able to take a stab at how to get the value of 4 out of this, I'd greatly appreciate it!
