Browse 535 attributed reviews, viewable separately for our classroom and online training
Creating and using Lambda Functions in Excel
Part three of a four-part series of blogs

You can now create and use anonymous functions in Excel (usually called "Lambda functions"). Learn how to add to the stock of Excel functions without having to learn any programming code!

  1. Creating and using Lambda Functions in Excel
  2. Stages to follow to create a lambda function
  3. Three examples of lambda functions (this blog)
  4. When will you be able to use lambda functions?

Posted by Andy Brown on 21 December 2020

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Three examples of lambda functions

Here are 3 cases where you might use lambda functions, in ascending order of complexity (the last one uses a technique called recursion).

BMI (Body Mass Index) Calculation

Your BMI is your mass in kilos divided by your height in metres squared, so you could create a lambda function called BMI which took in two arguments:

Argument What it should contain
Metres A person's height in metres
Kilos A person's weight in kilos

Here's what the lambda function might look like:

Lambda function for BMI

The lambda function returns someone's weight over their height squared.

And here's how you could use it: 

Two people's BMIs

My BMI hovers just over 25, and has done for many years, plonking me firmly down (that's not a metaphor) in the overweight category.

Toilet breaks when watching a film

As I get older, I find I need to go to the loo when watching a film more.  Here are my guidelines:

Length of film Breaks needed
Up to 120 minutes 1
120-180 minutes 2
More than 3 hours 3

Here's a lambda function to model this:

Toilet breaks function

The function uses the new(ish) Excel =IFS function to test various conditions. If a film lasts up to 120 minutes, I need one break; up to 180 minutes, two; and for any other films I need 3.

And here's how you could use this:

Toilet breaks filled in

Titanic is a 3-wee film if ever there was one.

Removing invalid characters using recursion

Suppose you want to create a party game whereby people have to guess film names when all the vowels and spaces have been removed:

Sample film names

A sample to give the idea - some films will be easier to guess than others ...

 

You want to replace A, E, I, O, U and spaces.  Normally you would have to create 6 different formulae to do this, but using recursion you can do everything with a single lambda function.

if you find this tricky to understand, read through the worked example I've given further down.

Here's a function which would replace all instances of one character with an empty string of text:

Replacing one character

The only problem with this is that it only replaces the U: we need to do the same thing again for each other vowel and for spaces.

You could turn this into a named lambda function:

Named lambda function

A named function to remove one string of text.

 

But what if the function could call itself?  Then (deep breath) we could use this:

=LAMBDA(

// the first argument is the string of text you want to reduce

RemainingText,


// the second argument is the text containing bad characters
CharactersToReplace,


// what to return? 

IF(

 

// if the number of bad characters left to consider is 0, just
// return whatever we've reduced the initial text to

LEN(CharactersToReplace) = 0,

RemainingText,

 

// otherwise, call the function again, this time passing in

// a string of invalid characters which is one character shorter

RemoveVowelsAndSpaces(

 

SUBSTITUTE(

// on each call, take whatever text remains ...

UPPER(RemainingText),

// ... and replace the leftmost character with an empty string

LEFT(CharactersToReplace,1),

""

),

 

// pass in a string of illegal characters which omits the

// first one

RIGHT(

CharactersToReplace,

LEN(CharactersToReplace) - 1

)

)

)

)

Note that Excel formulae can't contain proper carriage breaks, comments or indentation, so you won't be able to copy and paste this as it stands.

If you turn this into a lambda function called RemoveVowelsAndSpaces and incorporate this in your spreadsheet, you could get this:

The final formula

The final working formula!

Here's a worked example of what each of the arguments would contain for each iteration through the function for the case of Pearl Harbour (I've used an underscore _ to denote a space):

Nesting level RemainingText CharactersToReplace
1 Pearl Harbour AEIOU_
2 PERL HRBOUR EIOU_
3 PRL HRBOUR IOU_
4 PRL HRBOUR OU_
5 PRL HRBUR U_
6 PRL HBRR _
7 PRLHRBR  

Not the world's easiest thing to understand, but very powerful!

This blog has 0 threads Add post