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.

Nou pe simpatie:
Profil mariana_n23

Femeie
24 ani
Brasov
cauta Barbat
29 - 52 ani

Mrrrr's Forum (VIEW ONLY)Reguli Inregistrare Login PozeNu sunteti logat. Lista Forumurilor Pe Tematici

Mrrrr's Forum (VIEW ONLY) / Tutoriale si Ghiduri Utile // Tutorials and useful guides /

[EXCEL] Interesting LAMBDA Functions

Pagini: 1

Moderat de TRaP, TonyTzu

Mrrrr
AdMiN
Postari: 2388

See here:
https://www.reddit.com/r/excel/comments ... ons_youve/
and here
https://www.reddit.com/r/excel/

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: https://www.reddit.com/r/excel/comments ... t/k85dtks/

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: https://www.reddit.com/r/excel/comments ... t/k819eeu/

Usage:
=FILL(range)

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

FILL WITH DIRECTIONS

Source: https://www.reddit.com/r/excel/comments ... t/lgr1kem/

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))
)
)

_______________________________________

Mrrrr
AdMiN
Postari: 2388

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);""))
)

_______________________________________

Mrrrr
AdMiN
Postari: 2388

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 saptamani#4

TRaP
Moderator
Postari: 960

Mrrrr a scris:

RUNNING TOTAL

Usage:
=RUNTOTAL(rng)

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

An enhanced version of the above can be found here: http://mrrrr.3xforum.ro/post/2865/EXCEL ... arameters/

Pagini: 1

Mergi la