Formula to calculate streak at the beginning of an array

[krisplikj]

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!

[#2]

@krisplikj 

Something like this maybe:

=XMATCH("L",results)-1

[#3]

@krisplikj 

=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.

[#4]

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!

[#5]

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!

[#6]

@krisplikj 

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

[#7 · Accepted solution]

@krisplikj 

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

[#8]

@Peter Bartholomew, thank you so much.  This one did the trick!

= XMATCH(TRUE, sales>0) - 1

[#9]

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

[#10]

Good to know. I tested it here without any errors.

[#11]

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.

[#12]

@krisplikj 

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.