Forum Discussion
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 specifically interested in the data that repeats in the beginning of an array. Say I am looking at a football team's win / loss results and the team won (or lost) it's first four games in a row to start the season. How do I write a formula or function that will return a value of 4? Any help is greatly appreciated. Thanks!
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
11 Replies
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.
=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!
- 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
