Xlambda
Well-known Member
- Joined
- Mar 8, 2021
- Messages
- 860
- Office Version
- 365
- Platform
- Windows
STREAK Largest consecutive distribution of a given value, byrow or bycol
This is my take on a cool function: STREAK
Added new functionality, max argument [x]
Main calculation core of the function: IF(i, v + i, 0)
This is my take on a cool function: STREAK
Added new functionality, max argument [x]
Main calculation core of the function: IF(i, v + i, 0)
Excel Formula:
=LAMBDA(a, v, [o], [x],
LET(
b, IF(a = v, 1, 0),
s, LAMBDA(x, MAX(SCAN(0, x, LAMBDA(v, i, IF(i, v + i, 0))))),
i, IF(o, BYROW(b, LAMBDA(x, s(x))), BYCOL(b, LAMBDA(x, s(x)))),
IF(x, MAX(i), i)
)
)
Book1 | |||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | |||
1 | |||||||||||||||||||||||||||||||
2 | =CHAR(RANDARRAY(20,20,65,67,1)) | =STREAK(B3#,"a") | |||||||||||||||||||||||||||||
3 | C | C | B | A | B | A | B | C | B | C | B | C | A | B | C | A | B | C | B | A | 1 | ||||||||||
4 | A | A | A | B | B | A | A | A | B | C | B | C | A | B | A | B | A | C | B | A | 3 | ||||||||||
5 | A | C | C | B | C | B | B | B | B | A | B | B | C | B | A | B | C | B | C | C | 1 | ||||||||||
6 | B | A | B | C | A | B | A | B | C | A | A | A | B | A | C | A | A | A | B | A | 3 | ||||||||||
7 | A | C | A | A | A | C | C | C | C | B | A | A | B | C | B | B | A | B | C | B | 3 | ||||||||||
8 | C | A | C | C | A | B | A | C | B | B | B | B | B | C | C | C | A | B | C | B | 1 | ||||||||||
9 | C | C | A | A | C | B | A | A | A | A | B | C | A | A | A | B | B | A | C | C | 4 | ||||||||||
10 | B | A | A | C | B | A | C | A | A | B | C | C | B | A | A | A | B | C | C | C | 3 | ||||||||||
11 | B | B | A | B | B | C | C | A | B | B | B | A | B | A | B | A | B | A | C | A | 1 | ||||||||||
12 | A | B | C | A | A | A | C | A | C | A | C | B | C | A | A | B | A | B | C | A | 3 | ||||||||||
13 | C | C | C | B | C | C | A | A | A | B | C | A | B | C | A | A | C | A | B | A | 3 | ||||||||||
14 | B | C | C | B | A | A | A | A | C | A | B | C | A | A | B | B | A | A | A | B | 4 | ||||||||||
15 | B | C | B | A | B | B | A | A | A | B | C | A | B | B | C | A | A | A | C | B | 3 | ||||||||||
16 | B | B | A | C | B | A | C | A | B | B | A | A | B | A | C | B | A | B | B | B | 2 | ||||||||||
17 | B | B | B | A | B | A | B | A | B | A | B | B | C | C | C | B | B | A | A | A | 3 | ||||||||||
18 | B | B | B | A | B | B | A | C | B | B | C | C | C | A | C | B | C | A | B | C | 1 | ||||||||||
19 | A | A | A | B | A | B | B | A | B | C | C | C | C | A | B | C | B | A | C | C | 3 | ||||||||||
20 | A | A | C | A | A | B | A | B | B | A | B | C | B | A | A | C | A | C | A | C | 2 | ||||||||||
21 | C | B | A | A | B | A | A | A | B | A | C | B | B | C | A | A | B | B | C | B | 3 | =STREAK(B3#,"a",,1) | |||||||||
22 | B | B | A | B | C | A | A | C | A | C | A | C | C | C | A | A | B | A | A | C | 2 | 4 | |||||||||
23 | |||||||||||||||||||||||||||||||
24 | 2 | 2 | 3 | 2 | 3 | 2 | 3 | 9 | 2 | 2 | 2 | 2 | 2 | 4 | 3 | 2 | 3 | 3 | 1 | 3 | |||||||||||
25 | =STREAK(B3#,"a",1) | ||||||||||||||||||||||||||||||
26 | 9 | ||||||||||||||||||||||||||||||
27 | =STREAK(B3#,"a",1,1) | ||||||||||||||||||||||||||||||
28 | |||||||||||||||||||||||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B2,Y21,W2 | B2 | =FORMULATEXT(B3) |
B3:U22 | B3 | =CHAR(RANDARRAY(20,20,65,67,1)) |
W3:W22 | W3 | =STREAK(B3#,"a") |
Y22 | Y22 | =STREAK(B3#,"a",,1) |
B24:U24 | B24 | =STREAK(B3#,"a",1) |
B25,U27 | B25 | =FORMULATEXT(B24) |
U26 | U26 | =STREAK(B3#,"a",1,1) |
Dynamic array formulas. |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
B3:U22 | Expression | =B3="a" | text | NO |
Upvote
0