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:
Ank_beleaua din Bucuresti

Femeie
25 ani
Bucuresti
cauta Barbat
35 - 51 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: 2308

See here:
and here

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

A few I got so far:

CALENDAR

Usage:
=CALENDAR(Year; Month; Day)

Source:

Lambda to add to Name Manager:
=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

Source:

Usage:
=FILL(range)

Lambda to add to Name Manager:
=LAMBDA(range,
SCAN("", range, LAMBDA(a, v, IF(v = "", a, v)))
)

FILL WITH DIRECTIONS

Source:

1 = fill down, 2 = fill up, 3 = fill to the right, 4 = fill to the left

Usage:
=FILL(range; [direction])

Lambda to add to Name Manager:
=LAMBDA(range;[direction];
LET(
CLS; COLUMNS(range);
RWS; ROWS(range);
DIR; IFERROR(IF(OR(direction > 4; direction < 1); 1; direction); 1);
UPD; DIR <= 2;
UPL; ISEVEN(DIR);
MTX; TOCOL(IF(range = ""; ""; range); ; UPD);
NoM; COUNTA(MTX);
SEQ; SEQUENCE(NoM);
RVS; SEQUENCE(NoM; ; NoM; -1);
DRM; IF(UPL; INDEX(MTX; RVS); MTX);
FRS; --(MOD(SEQ; IF(UPD; RWS; CLS)) <> 1);
FIL_RCRS; LAMBDA(INP;ME;
LET(
BLK; --(INDEX(INP; SEQ) = "");
RES; IF(BLK * FRS; INDEX(INP; SEQ - 1); INP);
IF(AND(INP = RES); RES; ME(RES; ME))
)
);
FLD; FIL_RCRS(DRM; FIL_RCRS);
FIN; IF(UPL; INDEX(FLD; RVS); FLD);
IF(UPD; WRAPCOLS(IF(RWS = 1; MTX; FIN); RWS); WRAPROWS(IF(CLS = 1; MTX; FIN); CLS))
)
)

_______________________________________

pus acum 1 luna

Mrrrr
AdMiN

Inregistrat: acum 18 ani
Postari: 2308

Nth LARGEST UNIQUE VALUE

Usage:
=NthLargestUnique(A1:A20;2)

Lambda to add to Name Manager:
=LAMBDA(rng;n;
LARGE(UNIQUE(rng);n)
)

RUNNING TOTAL

Usage:
=RUNTOTAL(rng)

Lambda to add to Name Manager:
=LAMBDA(rng;
SCAN(0;rng;LAMBDA(a;b;a+b))
)

EXTRACT ONLY NUMBERS

Usage:
=EXTRACTNUM(txt)

Lambda to add to Name Manager:
=LAMBDA(txt;
TEXTJOIN("";;IF(ISNUMBER(--MID(txt;SEQUENCE(LEN(txt));1));
MID(txt;SEQUENCE(LEN(txt));1);""))
)

_______________________________________

pus acum 1 luna

Mrrrr
AdMiN

Inregistrat: acum 18 ani
Postari: 2308

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

Credits to Excel Off The Grid:

_______________________________________

pus acum 4 saptamani

Pagini: 1

Mergi la