Functions for grouped descriptive statistics in Excel

excel-lambda-describe-v2.txt

// Simple frequency table using COUNTA
FREQTBL = LAMBDA(dimension, GROUPBY(dimension, dimension, COUNTA,,0));

// Alternate version using EXPAND
FREQTBL_alt = LAMBDA(dimension, GROUPBY(dimension,EXPAND(1,ROWS(dimension),,1),COUNT,,0));

DESCRIBE = LAMBDA(data,
  LET(arepl, REPLICATE(VSTACK,ROWS(data)),
    LAMBDA(statistic, 
      GROUPBY(arepl(statistic),
            data,
            XLOOKUP(statistic, stat_names, stat_functions),
            0,
            0)
    )
  )
);


/*
Returns the text values with either the largest or smallest frequency
If there's more than one with the largest or smallest frequency
they are returned as a comma-separated list
The frequency is returned in parens at the end of the list

E.g.
A function to return the values in a dimension with the largest frequency counts:
=FREQTBL.EXTREMA(MAX)
Using that function:
=FREQTBL.EXTREMA(MAX)(wikicroplarge[Country])
*/
FREQTBL.EXTREMA = LAMBDA(function, 
                    LAMBDA(dimension, 
                      LET(
                        _freqtbl, SORT(FREQTBL(dimension),2),
                        _tcounts, TAKE(_freqtbl,,-1),
                        _extremum, function(_tcounts),
                        TEXTJOIN(
                          ", ",
                          TRUE,
                          FILTER(
                            TAKE(_freqtbl,,1),
                            _tcounts=_extremum,
                            #N/A
                          )
                        )&" ("&_extremum&")"
                      )
                    )
                  );


// e.g. =QUARTILE(1)(array)
QUARTILE.L = LAMBDA(q, LAMBDA(x, QUARTILE.EXC(x,q)));
QUARTILE1 = QUARTILE.L(1);
QUARTILE3 = QUARTILE.L(3);

// Test each member of an array to see if it's text
// COLUMNISTEXT = LAMBDA(x, ISTEXT(x));

// True if any value in the array is text, false otherwise
COLUMNHASTEXT = LAMBDA(x, OR(ISTEXT(x)));

REPLICATE = LAMBDA(direction, magnitude, 
  LAMBDA(of, 
  REDUCE(
    of,
    SEQUENCE(magnitude-1),
    LAMBDA(a,b,direction(a,of))
  )
));

stat_names = {"Sum"; "Mean"; "Count"; "Mode"; "Standard Deviation"; "Sample Variance"; "Standard Error"; "Kurtosis"; "Skewness"; "Confidence Level(95.0%)"; "1st quartile"; "Median"; "3rd quartile"; "Range"; "Distinct count"; "Rows"; "Minimum"; "Maximum"; "Length of longest text"; "Length of shortest text"; "Most common text"; "Least common text"};
stat_functions = VSTACK(
                  SUM, 
                  AVERAGE, 
                  COUNT, 
                  MODE, 
                  STDEV.S, 
                  VAR.S, 
                  LAMBDA(x, STDEV.S(x)/SQRT(COUNT(x))), 
                  KURT, 
                  SKEW, 
                  LAMBDA(x, CONFIDENCE.T(0.05, STDEV.S(x), COUNT(x))), 
                  LAMBDA(x, QUARTILE1(x)), 
                  MEDIAN, 
                  LAMBDA(x, QUARTILE3(x)), 
                  LAMBDA(x, QUARTILE3(x) - QUARTILE1(x)), 
                  LAMBDA(x, COUNTA(UNIQUE(x))), 
                  ROWS, 
                  MIN, 
                  MAX, 
                  LEN.EXTREMA(MAX), 
                  LEN.EXTREMA(MIN), 
                  FREQTBL.EXTREMA(MAX), 
                  FREQTBL.EXTREMA(MIN)
                  );


/*
Returns the min length of max length of a text column
E.g.
A function to return the max length:
=LEN.EXTREMA(MAX)
Using that function
=LEN.EXTREMA(MAX)(wikicroplarge[Country])
*/
LEN.EXTREMA = LAMBDA(function,
                LAMBDA(dimension,
                  IF(COLUMNHASTEXT(dimension), function(LEN(dimension)), #N/A)
                )
              );