EXCEL Interesting LAMBDA Functions

Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.

Lista Forumurilor Pe Tematici

Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login

POZE MRRRR'S FORUM (VIEW ONLY)

Nu sunteti logat.

Nou pe simpatie:
GabrielaQueen pe Simpatie

Femeie
24 ani
Galati
cauta Barbat
28 - 67 ani

Mrrrr's Forum (VIEW ONLY) / Tutoriale si Ghiduri Utile // Tutorials and useful guides / [EXCEL] Interesting LAMBDA Functions

Moderat de TRaP, TonyTzu

Autor

Mesaj

Pagini: 1

Mrrrr
AdMiN

Inregistrat: acum 18 ani
Postari: 2289

See here:
and here

To use, paste them into Name Manager and use the created functions.

A few I got so far:

Calendar

=CALENDAR(Year; Month; Day)

=LAMBDA(Year;Month;Day;LET(INPUT;DATE(Year;Month;Day);
A; EXPAND(TEXT(MOD(SEQUENCE(7)+1;7);"ddd");6+WEEKDAY(INPUT;2);;"");
B; DAY(SEQUENCE(EOMONTH(INPUT;0)-INPUT+1;;INPUT));
C; EXPAND(UPPER(TEXT(INPUT;"MMM"));7;;"");
D; WRAPROWS(VSTACK(C;A;B);7;"");D))

Fill Down

=FILL(range)

=LAMBDA(range,
SCAN("", range, LAMBDA(a, v, IF(v = "", a, v)))
)

and FILL with complications: 1 = down (default), 2 = up, 3 = right, 4 = left

=LAMBDA(Range;[Direction];
LET(A; TOCOL(IF(Range="";"";Range));
B; COUNTA(A);
C; INDEX(A; SEQUENCE(B;;B;-1);1);
D; IFERROR(IF(OR(Direction>4; Direction<1); 1; Direction); 1);
E; SWITCH(D; 1; A; 3; A; C);
F; SCAN(""; E; LAMBDA(X;Y; IF(Y=""; X; Y)));
G; INDEX(F; SEQUENCE(B;;B;-1);1);
H; SWITCH(D; 3; TRANPOSE(F); 4; TRANSPOSE(G); B; G; F);
H))

_______________________________________

pus acum 7 zile

Mrrrr
AdMiN

Inregistrat: acum 18 ani
Postari: 2289

Reverse text - apple becomes elppa

=REVERSE(txt)

=LAMBDA(txt;
TEXTJOIN("";; MID(txt; SEQUENCE(LEN(txt);;LEN(txt);-1);1))
)

Nth Largest Unique Value

=NthLargestUnique(A1:A20;2)

=LAMBDA(rng;n;
LARGE(UNIQUE(rng);n)
)

Running total

=RUNTOTAL(rng)

=LAMBDA(rng;
SCAN(0;rng;LAMBDA(a;b;a+b))
)

Extract only numbers

=EXTRACTNUM(txt)

=LAMBDA(txt;
TEXTJOIN("";;IF(ISNUMBER(--MID(txt;SEQUENCE(LEN(txt));1));
MID(txt;SEQUENCE(LEN(txt));1);""))
)

_______________________________________

pus acum 7 zile

Mrrrr
AdMiN

Inregistrat: acum 18 ani
Postari: 2289

Make array LAMBDA

E.g. in range A1:C3 I have some numbers:
1 2 3
4 5 6
7 8 9

Make array based on range and then you can refer to array with # reference

=LAMBDA(arr;
MAKEARRAY(
ROWS(arr);
COLUMNS(arr);
LAMBDA(R;C; INDEX(arr; R; C))
)
)

_______________________________________

pus acum 2 zile

Mrrrr
AdMiN

Inregistrat: acum 18 ani
Postari: 2289

Mrrrr a scris:

Reverse text - apple becomes elppa

=REVERSE(txt)

=LAMBDA(txt;
TEXTJOIN("";; MID(txt; SEQUENCE(LEN(txt);;LEN(txt);-1);1))
)

The above works for 1 cell.

The one below is a 3D mirroring LAMBDA and works for:

- 1 cell - reversing its contents
John becomes nhoJ

- 1 row of cells or 1 column of cells
0 1 2 3 becomes 3 2 1 0

- >1 rows of cells and/or >1 columns of cells
1 2 3
4 5 6
7 8 9
becomes
9 8 7
6 5 4
3 2 1

=LAMBDA(rng;
LET(
rw; ROWS(rng);
col; COLUMNS(rng);
IFS(
AND(rw=1; col=1); TEXTJOIN("";; MID(rng; SEQUENCE(LEN(rng);;LEN(rng);-1); 1));
OR(rw>1; col>1); MAKEARRAY(ROWS(rng);COLUMNS(rng);LAMBDA(rw;col; INDEX(rng; ROWS(rng)-rw+1; COLUMNS(rng)-col+1)))
)
)
)

Partially done with Gemini (MAKEARRAY)

_______________________________________

pus acum 2 zile

Mrrrr
AdMiN

Inregistrat: acum 18 ani
Postari: 2289

DOUBLEXLOOKUP, a function that performs multiple two-way lookups and spills the results horizontally and vertically.

Credits to Excel Off The Grid:

_______________________________________

pus acum 2 zile

Mrrrr
AdMiN

Inregistrat: acum 18 ani
Postari: 2289

Remove blanks

Based on the following video but adapted for Excel 2024 based on user @ernstborgener3745

=LAMBDA(array;
LET(
isBlank;ISBLANK(array);
blankRow;BYROW(isBlank;LAMBDA(a;AND(a)));
blankCol;BYCOL(isBlank;LAMBDA(a;AND(a)));
result;FILTER(FILTER(array;NOT(blankRow));NOT(blankCol));
result))

_______________________________________

pus acum 2 zile

Pagini: 1

Mergi la