Dec 20 2022 01:32 PM
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!
Dec 20 2022 01:53 PM
Dec 20 2022 01:57 PM
=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.
Dec 20 2022 02:26 PM
Dec 20 2022 02:42 PM
Dec 20 2022 03:08 PM
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
Dec 20 2022 03:47 PM
SolutionSince 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
Dec 22 2022 07:53 AM
Dec 22 2022 07:57 AM
Dec 22 2022 09:39 AM
Dec 28 2022 12:54 PM
Dec 29 2022 02:02 PM
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.
Dec 20 2022 03:47 PM
SolutionSince 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