BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
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!
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:
The lambda function returns someone's weight over their height squared.
And here's how you could use it:
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|
|More than 3 hours||3|
Here's a lambda function to model this:
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:
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:
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:
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:
A named function to remove one string of text.
But what if the function could call itself? Then (deep breath) we could use this:
// the first argument is the string of text you want to reduce
// the second argument is the text containing bad characters
// what to return?
// if the number of bad characters left to consider is 0, just
// return whatever we've reduced the initial text to
LEN(CharactersToReplace) = 0,
// otherwise, call the function again, this time passing in
// a string of invalid characters which is one character shorter
// on each call, take whatever text remains ...
// ... and replace the leftmost character with an empty string
// pass in a string of illegal characters which omits the
// first one
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 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):
Not the world's easiest thing to understand, but very powerful!