Descriptive Statistics summary with LAMBDA

-

One function in Excel that I find useful is the Descriptive Statistics from the Analysis Toolpak Add-In. At a glance I see a summary of the sample data.

However I also find it cumbersome to use.

  1. It requires you to add-in Analysis Toolpak - not loaded by default.
    I wonder why Microsoft choses not to pre-load in Excel even though it has been available since Excel 2000 (maybe even earlier?).
  2. It launches a dialog. Recently, this becomes an issue as it unavailable in Excel Web.
  3. It returns a static result, not an auto recalculating function, i.e. the result is not updated if and when the data is updated.

Of the 3 reasons above, I find the last most annoying. While field/laboratory sample data does not change after collection, but input data from external sources can be changing frequently. Any report based on external sources would have to be painstakingly updated manually.

So in this blog, I will create a Descriptive Statistics function with LAMBDA that would recalculate automatically just like other Excel functions. I will re-use lessons from earlier blogs relating to Table Array, Optional Parameters and also my previous dcrConfidence function.

I will calculate the same list of values as the same name function in Analysis Toolpak.

Name: dcrDescriptiveStatistics

Comments: Returns the descriptive statistics of a sample. Includes confidence interval. Default alpha = 0.05.

Refers To:

=LAMBDA(array, [alpha],
 LET(alpha, IF(ISOMITTED(alpha), 0.05, alpha),
    percent, TEXT(1-alpha, "0%"),
    HSTACK(
      VSTACK(
        "Mean",
        "Standard Error",
        "Median",
        "Standard Deviation",
        "Sample Variance",
        "Kurtosis",
        "Skewness",
        "Range",
        "Minimum",
        "Maximum",
        "Sum",
        "Count",
        percent & " Confidence Interval",
        "Lower Confidence Limit",
        "Upper Confidence Limit"
      ) ,
      VSTACK(
        AVERAGE(array),
        dcrStErr(array),
        MEDIAN(array),
        STDEV.S(array),
        VAR.S(array),
        KURT(array),
        SKEW(array),
        MAX(array)- MIN(array),
        MIN(array),
        MAX(array),
        SUM(array),
        COUNT(array),
        dcrConfidence(array, alpha,0),
        dcrConfidence(array, alpha,-1),
        dcrConfidence(array, alpha,1)
      )
    )
  )
)

The implementation is very straight forward as most values only need to call a single Excel function. Only percent is used as an intermediate variable and even then it was for the text description.

In writing LAMBDA functions, the most common mistake I make is missing out on the commas ",". And missing closing brackets ")" follows closely behind. A good text editor can aid in spotting your mistakes.

Below is a screenshot of the dcrDescriptiveStatistics function.

Notes:

  1. The function automatically recalculates like other Excel functions.
  2. The function does not adjust the column widths, unlike that from Data Analysis pack. 

You could make it prettier, by providing a title or adding other useful statistical descriptions. Go forth and make it prettier in your DC-Den!

Comments

Post a Comment