Creating Standard Error function with Excel LAMBDA

LAMBDA is available in Excel for Microsoft 365 and Excel for the Web. LAMBDA allows users to implement custom Excel formulae without resorting to VBA macros. LAMBDA essentially keeps Excel within Excel.

This blog build various reusable statistical features and inference functions using LAMBDA. For a start, we will implement the Standard Error for a sample mean.

Firstly the Standard Error for a sample mean is defined as:

The syntax for LAMBDA is

=LAMBDA([parameter1, parameter2, …,] calculation) 

So the LAMBDA function implementation would be

=LAMBDA(array, STDEV.S(array)/SQRT(COUNT(array)) )

The array is the input parameter or variable, while the STDEV.S(array)/SQRT(COUNT(array)) part is the standard error calculation.

To create this LAMBDA as a reusable function:

1. Open Excel > Formulas > Name Manager
2. In the Name Manager dialog, click New
3. Enter:
1. Name: dcrStErr
2. Comments: Returns the standard error of a sample mean.
3. Refers to: =LAMBDA(array, STDEV.S(array)/SQRT(COUNT(array)))
4. Click OK and there you have your first LAMBDA custom function.

You can use formula just like any other built-in Excel formula. Type the equal sign "=" in a cell followed by the formula name. You may notice Excel code completion works with custom LAMBDA functions. Selecting the correct formula would show the comments relating to that function. I highly recommend having comments instead of leaving them empty.

The dcrStErr requires an array input. Appropriate parameter / variable names is recommended if you intend to reuse your functions. It improves readability. 

Select the array and press ENTER.

The standard error for the sample mean will be automatically calculated. 

So implementing LAMBDA custom functions is not difficult. Some tricky bits are involved with typing the syntax, but apart from that, it is not difficult to figure this out.

Now go out and happy playing with LAMBDA in your DC-DEN!