This Excel lambda function calculates a correlation matrix for a range of data, optionally using either the Pearson or Spearman Ranked correlation

excel-lambda-CORRELMATRIX.txt

/*
CORRELMATRIX

Returns a correlation matrix

Inputs:
  x - an array of 2:n numeric columns of equal size for which to calculate the correlation of each pair of 2 columns
  has_header - TRUE if the first row of x contains column headers. If omitted or FALSE, x is assumed to not include a header row
  ranked - if TRUE, calculate the Spearman Ranked Correlation Coefficient. If FALSE or omitted, calculate the Pearson Correlation Coefficient
*/


CORRELMATRIX =LAMBDA(x,[has_header],[ranked],
  IF(COLUMNS(x())<2,"x must be at least 2 columns",
    LET(
      _c,COLUMNS(x()),
      _hashead,IF(ISOMITTED(has_header),FALSE,has_header),
      _head,IF(_hashead,INDEX(x(),1,),"Column "&SEQUENCE(1,_c)),
      _rnkd,IF(ISOMITTED(ranked),FALSE,ranked),
      _corner,IF(_rnkd,"Spearman ranked","Pearson"),
      _nohead,LAMBDA(arr,INDEX(arr,2,):INDEX(arr,ROWS(arr),)),
      _r,ROWS(x())-IF(_hashead,1,0),
      _ranks,IF(
              _rnkd,MAKEARRAY(_r,_c,
                      LAMBDA(r,c,
                        LET(
                          _x,IF(_hashead,_nohead(x()),x()),
                          RANK.AVG(
                            INDEX(_x,r,c),
                            INDEX(_x,,c)
                          )
                        )
                      )
                    ),
              IF(
                _hashead,
                _nohead(x()),
                x()
              )
             ),
      _cor,MAKEARRAY(_c+1,_c+1,
            LAMBDA(r,c,
              IFS(
                AND(r=1,c=1),_corner,
                r=1,INDEX(_head,1,c-1),
                c=1,INDEX(_head,1,r-1),
                TRUE,CORREL(
                  INDEX(_ranks,,r-1),
                  INDEX(_ranks,,c-1)
                )
              )
            )
           ),
      _cor
    )
  )
);