lrobbo314
Well-known Member
- Joined
- Jul 14, 2008
- Messages
- 3,957
- Office Version
- 365
- Platform
- Windows
STREAK will look at a range, and given a value, will return how many consecutive times the value appears in the range.
The function can look at a single row, single column, or range with multiple rows and columns.
The function can look at a single row, single column, or range with multiple rows and columns.
Excel Formula:
=LAMBDA(range,lookup_value,by_row,
LET(t,range,
rc,IF(by_row,COLUMN(t),ROW(t)),
IF(by_row,
BYROW(t,LAMBDA(x,MAX(FREQUENCY(IF(x=lookup_value,rc),IF(x<>lookup_value,rc))))),
BYCOL(t,LAMBDA(x,MAX(FREQUENCY(IF(x=lookup_value,rc),IF(x<>lookup_value,rc)))))
)
)
)
LAMBDA | ||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | |||
1 | B | A | B | A | C | C | C | A | C | C | A | A | B | C | C | A | B | A | B | 2 | ||
2 | A | C | A | B | A | A | A | A | A | B | B | A | A | C | A | C | C | A | A | 5 | ||
3 | A | C | B | A | B | A | B | A | B | A | A | B | B | B | A | A | B | C | A | 2 | ||
4 | B | B | C | C | A | A | A | A | B | B | A | C | B | A | B | C | A | C | C | 4 | ||
5 | C | C | A | A | B | C | C | A | C | B | A | A | C | B | C | B | C | B | B | 2 | ||
6 | A | A | A | A | A | C | C | B | B | A | A | B | C | C | C | C | A | A | B | 5 | ||
7 | B | A | B | B | C | A | B | B | A | C | A | B | B | A | C | A | C | A | C | 1 | ||
8 | A | B | A | C | C | C | C | C | A | A | A | C | B | C | A | B | A | A | A | 3 | ||
9 | A | B | A | C | B | B | B | B | C | B | C | C | A | B | C | A | A | B | C | 2 | ||
10 | B | B | C | C | A | B | A | A | C | C | B | C | A | C | B | C | C | B | A | 2 | ||
11 | A | B | B | C | B | B | C | B | C | A | B | B | C | C | C | B | C | A | A | 2 | ||
12 | A | A | A | C | B | C | C | A | B | A | B | A | A | A | A | A | A | B | A | 6 | ||
13 | 2 | 2 | 2 | 2 | 1 | 3 | 1 | 5 | 2 | 2 | 6 | 2 | 2 | 1 | 2 | 1 | 2 | 3 | 3 | |||
14 | ||||||||||||||||||||||
15 | Single Row | |||||||||||||||||||||
16 | 5 | |||||||||||||||||||||
17 | ||||||||||||||||||||||
18 | Single Column | |||||||||||||||||||||
19 | 6 | |||||||||||||||||||||
Sheet2 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
T1:T12 | T1 | =STREAK(A1:S12,"A",1) |
A13:S13 | A13 | =STREAK(A1:S12,"A",0) |
A16 | A16 | =STREAK(A6:S6,"A",1) |
A19 | A19 | =STREAK(K1:K12,"A",0) |
Dynamic array formulas. |
Upvote
0