How to use the new LAMBDA-feature in Excel (e.g. to identify outliers)

Whait what, Excel? You call yourself a data engineer and you work with Excel? Sure. Excel is still the best tool for quick’n’dirty solutions, don’t let Tableau, Python or Alteryx tell you something different… ;)

What’s LAMBDA?

One sentence: LAMBDA allows you to combine common Excel functions into one new custom function (respectively formula) that is available all over your workbook. Back in the days you had to cope with VBA or multiple columns to implement special calculations. LAMBDA still does not cover one disadvantage: Building custom functions remains a mess and ends up with long, encapsulated formulas.

Creating a LAMBDA function requires two steps. Let’s take an example challenge to demonstrate how that works: Find outliers by calculation quartiles and the upper and lower fence.

being an outlier

What are fences?

Upper and lower fences are borders, defined by 1,5 times the so called inter quartile range, which is just the area between two quartiles: the first and the third. The following formulas are used to find those borders for a given set of values.

Lower Fence = Q1 - (1,5 * IQR)
Upper Fence = Q3 + (1,5 * IQR)

Every value that’s outside this range can be considered as an outlier. That’s the short unprofessional explanation. Imagine you have a set of very important values:

example set of very important numbers — where are the outliers

A couple of steps are required to find the outliers with Excel. Every step requires one column (the bold parts are the actual Excel formulas, you don’t need to fill your sheet, that’s just for showing the process).

First you need to calculate the quartiles (I’m using the including quartile function, on the difference between including and excluding quartile check this text out).

1st Quartile = Q1 => Column B: =QUARTILE.INKL(A:A;1)
3rd Quartile = Q3 => Column C: =QUARTILE.INKL(A:A;3)

The next column calculates the interquartile range — IQR:

IQR = Q3 - Q1 => Column D: =C2-B2

Another two columns are required to calculate the lower and upper fence:

Lower Fence = LF => Column E: =B2-(1,5*D2)
Upper Fence = UP => Column F: =C2+(1,5*D2)

Finally we need to check the actual value: Is it lower than the lower fence or greater than the upper fence? Is it inside our range?

Is outlier? => Column G: =IF(OR(A2<E2;A2>F2);"x";"")

That’s how it would look like in Excel:

Outlier identification (in case you’re wondering: There are no outliers below the lower fence)

Now you would tend to compress the six additional columns into one, like this:

=IF(
    OR(
        A2<QUARTILE.INKL($A$2:$A$2670;1) - (1,5*QUARTILE.INKL($A$2:$A$2670;3) - QUARTILE.INKL($A$2:$A$2670;1));
        A2>QUARTILE.INKL($A$2:$A$2670;3) + (1,5*QUARTILE.INKL($A$2:$A$2670;3) - QUARTILE.INKL($A$2:$A$2670;1))
    );
    "x";
    "")

Isn’t that beautiful? Welcome to Excel! Good news is: LAMBDA helps you to make this a little more readable and also portable. The bad news: It still looks a little chaotic.

Step 1: Build the custom function inside a cell

The LAMBDA formula requires n parameters. You can add (almost) as many parameters as you want, the last parameter always contains the calculation, the ones before are parameters that can be used within the calculation. Example:

This one expects one parameter and would increment it by 1:

=LAMBDA(x, x + 1)

And this LAMBDA calculation just returns a 5 — it does not expect any parameters:

=LAMBDA(5)

And another one that adds up to values:

=LAMBDA(x;y;x+y)

If you just put those lines into a cell, Excel returns a #CALC-error because you’re not providing any references. The worksheet is only a place to create and test your LAMBDA function. You also need to add example values, according to the parameters your LAMBDA function, to make it work within your worksheet:

=LAMBDA(x;y;x+y)(5;9)

This will actualy return a 14. Now let’s put our upper-lower-fence-drama into a LAMBDA-function. First we replace every reference with a distinct variable, I chose value and range:

=IF(
    OR(
        value < QUARTILE.INKL(range;1) - (1,5 * QUARTILE.INKL(range;3) - QUARTILE.INKL(range;1));
        value > QUARTILE.INKL(range;3) + (1,5 * QUARTILE.INKL(range;3) - QUARTILE.INKL(range;1))
    );
    "x";
    "")

Now we wrap this up with LAMBDA and some brackets and register our two variables:

=LAMBDA(value;range;IF(
    OR(
        value < QUARTILE.INKL(range;1) - (1,5 * QUARTILE.INKL(range;3) - QUARTILE.INKL(range;1));
        value > QUARTILE.INKL(range;3) + (1,5 * QUARTILE.INKL(range;3) - QUARTILE.INKL(range;1))
    );
    "x";
    "")
)

And last but not least, we need to add some testing values — we just referencing the cells, as we did before:

=LAMBDA(value;range;IF(
    OR(
        value < QUARTILE.INKL(range;1) - (1,5 * QUARTILE.INKL(range;3) - QUARTILE.INKL(range;1));
        value > QUARTILE.INKL(range;3) + (1,5 * QUARTILE.INKL(range;3) - QUARTILE.INKL(range;1))
    );
    "x";
    "")
)(A1;A$2:A$2670)

Now let’s fill the column with this formula:

Unsurprisingly the result is the same.

Step 2 — Actually create a new function / formula

Of course we don’t want to copy the whole stuff every time we need to identify outliers. So let’s register this calculation as a new formula. Press either CMD+F3 (CTRL+F3 on Windows) or select “define names” in the Formula-Area:

Add a name for your new function — lets call it IS_OUTLIER — and paste your LAMBDA-formular into the second text field — without the second pair of brackets containing your test values:

=LAMBDA(value;range;IF(
    OR(
        value < QUARTILE.INKL(range;1) - (1,5 * QUARTILE.INKL(range;3) - QUARTILE.INKL(range;1));
        value > QUARTILE.INKL(range;3) + (1,5 * QUARTILE.INKL(range;3) - QUARTILE.INKL(range;1))
    );
    "x";
    ""))

Do not forget to press the plus-button to actually create the new formula. Confirm everything with OK and get ready to rumble. This is your new and simple outlier detection formula:

=IS_OUTLIER(value;range)

And it works, apparently, like a charm. If you need this feature in another worksheet, just copy the name definition.

That’s all. No epilog, but one word of advice: You have to subscribe to the Beta-channel (at least on MacOS), as the LAMBDA function is not yet provided in the official release candiate. Have fun! :)