KA_LAMBDAs.txt

// video explaining all these formulas: https://youtu.be/yO8zX_Ivcug

// see video: https://youtu.be/P8knGhzV7uk
ListTable = LAMBDA(array,
        LET(one,TAKE(array,,1),two,TAKE(array,,-1),
        list,UNIQUE(one),
        HSTACK(list,BYROW(list,LAMBDA(x,TEXTJOIN(", ",TRUE,FILTER(two,one=x)))))));
	
// see video: https://youtu.be/lli7MTilTB8
repeatXtimes = LAMBDA(array,num_repeat,
        XLOOKUP(SEQUENCE(SUM(num_repeat)),VSTACK(1,SCAN(1,num_repeat,LAMBDA(a,b,a+b))),VSTACK(array,""),,-1));
  
// count number of unique items in selected range
COUNTU = LAMBDA(array,COUNTA(UNIQUE(array)));
  
// count number of words in a cell
WORDCOUNT = LAMBDA(array,LEN(TRIM(array))-LEN(SUBSTITUTE(array," ",""))+1);

// get word number n from text
WORDN = LAMBDA(value,num,IF(ISERROR(FIND(" ",value)),value,INDEX(TEXTSPLIT(value," "),num)));

// number of years, months and days between selected date and today's date
AGE = LAMBDA(array,
                LET(years,DATEDIF(array, TODAY(),"y"),
                months,DATEDIF(array, TODAY(),"ym"),
                days,DATEDIF(array, TODAY(),"md"),
                IF(years=0,"",years&" years, ")& IF(months=0,"",months&" months, ")& IF(days=0,"",days&" days")));

// join two numbers together with a dash in between
NEATRANGE = LAMBDA(first,second,format,TEXT(first,format)&" - "&TEXT(second,format));

// get list of all the sheet names in the workbook
SHEETNAMES = TRANSPOSE(REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),""));

// spearmans rank correlation
SPEARMANS = LAMBDA(array1,array2,CORREL(RANK.AVG(array1,array1),RANK.AVG(array2,array2)));

// pearsons correlation table
CORRELTABLE = LAMBDA(array,LET(
                headers,TAKE(array,1),data,DROP(array,1),
                average,BYCOL(data,LAMBDA(avg,AVERAGE(avg))),
                sd,BYCOL(data,LAMBDA(std,STDEV.S(std))),
                nums,MMULT(TRANSPOSE((data-average)/sd),(data-average)/sd)/(ROWS(data)-1),
                HSTACK(VSTACK("",TRANSPOSE(headers)),VSTACK(headers,nums))));

// choose random value from a list
CHOOSERAND = LAMBDA(array,INDEX(array,RANDBETWEEN(1,ROWS(array))));

// find values in one column that are missing from another column
MISSING = LAMBDA(array1,array2,FILTER(array1,COUNTIF(array2,array1)=0));

// get month as test, 1 = J, 2 = Jan, 3 = January
M = LAMBDA(array,[format],
                LET(format,IF(ISOMITTED(format),2,format),
                CHOOSE(format,TEXT(array,"mmmmm"),TEXT(array,"mmm"),TEXT(array,"mmmm"))));

// get weekday as text, 1 = M, 2 = Mon, 3 = Monday
WD = LAMBDA(array,[format],
                LET(format,IF(ISOMITTED(format),2,format),
                CHOOSE(format,LEFT(TEXT(array,"ddd"),1),TEXT(array,"ddd"),TEXT(array,"dddd"))));

// select cell with date in it or use TODAY() formula or put in 0
// to get calendar for that month
CALENDAR = LAMBDA(date,LET(date,IF(date=0,TODAY(),date),
        seq,SEQUENCE(8,7,-WEEKDAY(EOMONTH(date,-1)+1)-12),
        first,CHOOSE(SEQUENCE(,7),TEXT(date,"mmmm"),TEXT(date,"yyyy"),"","","","",""),
        SWITCH(SEQUENCE(8),1,first,2,{"Sun","Mon","Tue","Wed","Thu","Fri","Sat"},IF((seq>0)*(seq<=DAY(EOMONTH(date,0))),seq,""))));

// get just the numbers from a cell
GET.NUM = LAMBDA(value,TEXTJOIN("",1,IFERROR(MID(value,SEQUENCE(LEN(value)),1)*1,"")));

// get just the text from a cell
GET.TEXT = LAMBDA(value,LET(
                split,MID(value,SEQUENCE(LEN(value)),1),
                nums,CODE(LOWER(split)),
                TEXTJOIN("",1,FILTER(split,(nums>=97)*(nums<=122)))));
  
SUMCOLS = LAMBDA(array,BYCOL(array,LAMBDA(c,SUM(c))));
AVGCOLS = LAMBDA(array,BYCOL(array,LAMBDA(c,AVERAGE(c))));
STDEVCOLS = LAMBDA(array,BYCOL(array,LAMBDA(c,STDEV(c))));

SUMROWS = LAMBDA(array,BYROW(array,LAMBDA(r,SUM(r))));
AVGROWS = LAMBDA(array,BYROW(array,LAMBDA(r,AVERAGE(r))));
STDEVROWS = LAMBDA(array,BYROW(array,LAMBDA(r,STDEV(r))));

QUARTER = LAMBDA(date,VLOOKUP(MONTH(date),
                {1,"Q1";2,"Q1";3,"Q1";
                4,"Q2";5,"Q2";6,"Q2";
                7,"Q3";8,"Q3";9,"Q3";
                10,"Q4";11,"Q4";12,"Q4"},2,0));

SEASON = LAMBDA(date,VLOOKUP(MONTH(date),
                {1,"Winter";2,"Winter";
                3,"Spring";4,"Spring";5,"Spring";
                6,"Summer";7,"Summer";8,"Summer";
                9,"Autumn";10,"Autumn";11,"Autumn";
                12,"Winter"},2,0));

// get wind direction from degrees
WIND = LAMBDA(degree,CHOOSE(1+ABS(ROUND(degree/45,0)),"N","NE","E","SE","S","SW","W","NW","N"));

// get a,b and c of quadratic equation, ax^2 + bx + c, quadratic best fit of x_values and y_values
// this will vary depending on the language and ^{1,2} might neeed to be changed to ^{1;2} or ^{1\2} or ^{1.2}
QUAD = LAMBDA(x_values,y_values,
        LINEST(FILTER(y_values,(x_values<>0)*(y_values<>0)),FILTER(x_values,(x_values<>0)*(y_values<>0))^{1,2}));

// get sequence of repeating numbers 1,2,3,1,2,3,1,2,3 or 1,1,1,2,2,2,3,3,3
REPEAT = LAMBDA(repeat,start,step,stop,type,
                IF(type=0,
                MOD(SEQUENCE(repeat*stop,1,0,1),repeat)*step+start,
                QUOTIENT(SEQUENCE(repeat*stop,1,0,1),repeat)*step+start));

CaseSensitiveCount = LAMBDA(array,LET(
                        compare,--EXACT(TRANSPOSE(array),array),
                        ones,ROW(array)^0,
                        nums,MMULT(compare,ones),
                        CHOOSECOLS(UNIQUE(HSTACK(array,compare,nums)),1,-1)));

// remove every row that has a blank or an error in it
FILTERX = LAMBDA(array,
                FILTER(array,BYROW(ISBLANK(array)+ISERROR(array),LAMBDA(r,SUM(r)))=0));

// take table with one column of dates and one column of numbers
// get table of years and either sum, average or count of numbers
// sum1avg2count3: sum = 1, average = 2, count = 3
YEARTABLE = LAMBDA(array,[sum1avg2count3],
                LET(sum1avg2count3,IF(ISOMITTED(sum1avg2count3),2,sum1avg2count3),
                one,TAKE(array,,1),two,TAKE(array,,-1),
                years,SORT(UNIQUE(YEAR(one))),
                nums,BYROW(years,LAMBDA(r,
                LET(f,FILTER(two,YEAR(one)=r),
                CHOOSE(sum1avg2count3,SUM(f),AVERAGE(f),COUNT(f))))),
                HSTACK(years,nums)));
	
// take table with one column of dates and one column of numbers
// get table of months and either sum, average or count of numbers
// sum1avg2count3: sum = 1, average = 2, count = 3
MONTHTABLE = LAMBDA(array,[sum1avg2count3],
                LET(sum1avg2count3,IF(ISOMITTED(sum1avg2count3),2,sum1avg2count3),
                one,TAKE(array,,1),two,TAKE(array,,-1),
                months,SEQUENCE(12),
                nums,BYROW(months,LAMBDA(r,
                LET(f,FILTER(two,MONTH(one)=r),
                CHOOSE(sum1avg2count3,SUM(f),AVERAGE(f),COUNT(f))))),
                HSTACK({"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},nums)));

// horizontally join two tables together
// when the left hand column in each table is the same but is missing or including extra values
// select header rows or the first row of data won't be sorted
// see video: https://youtu.be/RWRc-gjS7EU
HJOIN = LAMBDA(array1,array2,
                LET(title1,TAKE(array1,1),title2,DROP(TAKE(array2,1),,1),
                data1,DROP(array1,1),data2,DROP(array2,1),
                left1,TAKE(data1,,1),left2,TAKE(data2,,1),
                list,SORT(UNIQUE(VSTACK(left1,left2))),
                VSTACK(HSTACK(title1,title2),IFNA(HSTACK(list,
                INDEX(data1,XMATCH(list,left1),SEQUENCE(,COLUMNS(data1)-1,2)),
                INDEX(data2,XMATCH(list,left2),SEQUENCE(,COLUMNS(data2)-1,2))),""))));

// vertically join two tables together
// when the left hand column in both tables is the same variable
// but the other columns are missing columns or including extra columns
VJOIN = LAMBDA(array1,array2,
                LET(left_title,TAKE(array1,1,1),
                title1,DROP(TAKE(array1,1),,1),title2,DROP(TAKE(array2,1),,1),
                data1,DROP(array1,1),data2,DROP(array2,1),
                left1,TAKE(array1,,1),left2,TAKE(data2,,1),
                list,HSTACK(left_title,SORT(UNIQUE(HSTACK(title1,title2),1),,,1)),
                IFNA(VSTACK(list,
                INDEX(data1,SEQUENCE(ROWS(data1)),XMATCH(list,HSTACK(left_title,title1))),
                INDEX(data2,SEQUENCE(ROWS(data2)),XMATCH(list,HSTACK(left_title,title2)))),"")));

// unpivot a table with one variable along the top and one variable down the side
// see video: https://youtu.be/m3ATM3TNZe0
UNPIVOT = LAMBDA(array,
                HSTACK(TEXTSPLIT(TEXTJOIN(".",1,DROP(TAKE(array,,1)&"-"&TAKE(array,1),1,1)),"-","."),
                TOCOL(DROP(array,1,1))));

// pivot table with one variable column
// sum1avg2count3: sum = 1, average = 2, count = 3
// totals_1_no_totals_2: include totals = 1, don't include totals = 2
// see video: https://youtu.be/VoB_cigX1hk
// see video: https://youtu.be/RT7skH-xbLk
PIVOT1 = LAMBDA(data,[sum1avg2count3],[totals_1_no_totals_2],
LET(sum1avg2count3,IF(ISOMITTED(sum1avg2count3),2,sum1avg2count3),
totals_1_no_totals_2,IF(ISOMITTED(totals_1_no_totals_2),2,totals_1_no_totals_2),
one,TAKE(data,,1),two,TAKE(data,,-1),
group1,SORT(UNIQUE(one)),
m_sum,SUMIFS(two,one,group1),
m_avg,AVERAGEIFS(two,one,group1),
m_count,COUNTIFS(one,group1),
CHOOSE(totals_1_no_totals_2,
CHOOSE(sum1avg2count3,
VSTACK(HSTACK(group1,m_sum),HSTACK("Total",SUM(two))),
VSTACK(HSTACK(group1,m_avg),HSTACK("Total",AVERAGE(two))),
VSTACK(HSTACK(group1,m_count),HSTACK("Total",COUNT(two)))
),
CHOOSE(sum1avg2count3,
HSTACK(group1,m_sum),HSTACK(group1,m_avg),HSTACK(group1,m_count)
)
)));

// pivot table with two variable columns
// sum1avg2count3: sum = 1, average = 2, count = 3
// totals_1_no_totals_2: include totals = 1, don't include totals = 2
// see video: https://youtu.be/VoB_cigX1hk
// see video: https://youtu.be/RT7skH-xbLk
PIVOT2 = LAMBDA(data,[sum1avg2count3],[totals_1_no_totals_2],
        LET(sum1avg2count3,IF(ISOMITTED(sum1avg2count3),2,sum1avg2count3),
        totals_1_no_totals_2,IF(ISOMITTED(totals_1_no_totals_2),2,totals_1_no_totals_2),
        one,TAKE(data,,1),two,INDEX(data,,2),three,DROP(data,,2),
        group1,SORT(UNIQUE(one)),
        group2,TRANSPOSE(SORT(UNIQUE(two))),
        m_sum,SUMIFS(three,one,group1,two,group2),
        m_avg,AVERAGEIFS(three,one,group1,two,group2),
        m_count,COUNTIFS(one,group1,two,group2),
        top_one,HSTACK("",group2,"Total"),
        top_two,HSTACK("",group2),
        CHOOSE(totals_1_no_totals_2,
                CHOOSE(sum1avg2count3,
                        VSTACK(top_one,
                                HSTACK(group1,m_sum,SUMIFS(three,one,group1)),
                                HSTACK("Total",SUMIFS(three,two,group2),SUM(three))),
                        VSTACK(top_one,
                                HSTACK(group1,m_avg,AVERAGEIFS(three,one,group1)),
                                HSTACK("Total",AVERAGEIFS(three,two,group2),AVERAGE(three))),
                        VSTACK(top_one,
                                HSTACK(group1,m_count,COUNTIFS(one,group1)),
                                HSTACK("Total",COUNTIFS(two,group2),COUNT(three)))
                        ),
                CHOOSE(sum1avg2count3,
                        VSTACK(top_two, HSTACK(group1,m_sum)),
                        VSTACK(top_two, HSTACK(group1,m_avg)),
                        VSTACK(top_two, HSTACK(group1,m_count))
        ))));

This is amazing, thank you Karina.

Hey Karina,

with 1+0*NOW() your "SHEETNAMES" function could react to changes! :)

SHEETNAMES = TRANSPOSE(REPLACE(GET.WORKBOOK(1+0*NOW()),1,FIND("]",GET.WORKBOOK(1+0*NOW())),""));

Greats :)