Xlambda
Well-known Member
- Joined
- Mar 8, 2021
- Messages
- 860
- Office Version
- 365
- Platform
- Windows
ASCAN, Array SCAN, 3 in 1 function, SCAN by row, by column, by array. This is my take of tboulden's SCANBYROW/BYCOL.
Uses only new!! SCAN lambda helper function, no need of byrow,bycol,makearray, or lambda as arguments.
Uses only new!! SCAN lambda helper function, no need of byrow,bycol,makearray, or lambda as arguments.
Excel Formula:
=LAMBDA(a,[d],
LET(y,IF(d=1,TRANSPOSE(a),a),s,SCAN(0,y,LAMBDA(v,a,v+a)),r,ROWS(s),c,COLUMNS(s),sr,SEQUENCE(r)-1,
x,s-IF(d,IFERROR(INDEX(INDEX(s,,c),sr)*sr^0,0),0),
IF(d=1,TRANSPOSE(x),x)
)
)
LAMBDA 1.1.3.xlsx | ||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | |||
1 | d,omitted (by array) | d,-1 (by rows) | d,1 (by clms) | |||||||||||||||||||
2 | sample 1 | a | =ASCAN(A3:D6) | =ASCAN(A3:D6,-1) | =ASCAN(A3:D6,1) | |||||||||||||||||
3 | 1 | 2 | 3 | 4 | 1 | 3 | 6 | 10 | 1 | 3 | 6 | 10 | 1 | 2 | 3 | 4 | ||||||
4 | 5 | 6 | 7 | 8 | 15 | 21 | 28 | 36 | 5 | 11 | 18 | 26 | 6 | 8 | 10 | 12 | ||||||
5 | 9 | 10 | 11 | 12 | 45 | 55 | 66 | 78 | 9 | 19 | 30 | 42 | 15 | 18 | 21 | 24 | ||||||
6 | 13 | 14 | 15 | 16 | 91 | 105 | 120 | 136 | 13 | 27 | 42 | 58 | 28 | 32 | 36 | 40 | ||||||
7 | ||||||||||||||||||||||
8 | d,omitted (by array) | d,-1 (by rows) | d,1 (by clms) | |||||||||||||||||||
9 | sample 2 | a | =ASCAN(A10#) | =ASCAN(A10#,-1) | =ASCAN(A10#,1) | |||||||||||||||||
10 | 1 | 2 | 3 | 4 | 1 | 3 | 6 | 10 | 1 | 3 | 6 | 10 | 1 | 2 | 3 | 4 | ||||||
11 | 5 | 6 | 7 | 8 | 15 | 21 | 28 | 36 | 5 | 11 | 18 | 26 | 6 | 8 | 10 | 12 | ||||||
12 | 9 | 10 | 11 | 12 | 45 | 55 | 66 | 78 | 9 | 19 | 30 | 42 | 15 | 18 | 21 | 24 | ||||||
13 | 13 | 14 | 15 | 16 | 91 | 105 | 120 | 136 | 13 | 27 | 42 | 58 | 28 | 32 | 36 | 40 | ||||||
14 | 17 | 18 | 19 | 20 | 153 | 171 | 190 | 210 | 17 | 35 | 54 | 74 | 45 | 50 | 55 | 60 | ||||||
15 | 21 | 22 | 23 | 24 | 231 | 253 | 276 | 300 | 21 | 43 | 66 | 90 | 66 | 72 | 78 | 84 | ||||||
16 | ||||||||||||||||||||||
17 | Note: Did not set an extra argument for any kind of "initial value", since the only thing it does is too simple, | |||||||||||||||||||||
18 | adds a constant value to the final outcome , like in 10+ASCAN(a) | |||||||||||||||||||||
19 | ||||||||||||||||||||||
ASCAN post |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F2,P9,K9,F9,P2,K2 | F2 | =FORMULATEXT(F3) |
F3:I6 | F3 | =ASCAN(A3:D6) |
K3:N6 | K3 | =ASCAN(A3:D6,-1) |
P3:S6 | P3 | =ASCAN(A3:D6,1) |
A10:D15 | A10 | =SEQUENCE(6,4) |
F10:I15 | F10 | =ASCAN(A10#) |
K10:N15 | K10 | =ASCAN(A10#,-1) |
P10:S15 | P10 | =ASCAN(A10#,1) |
Dynamic array formulas. |
Upvote
0