This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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! Creating and using Lambda Functions in Excel Stages to follow to create a lambda function Three examples of lambda functions (this blog) When will you be able to use lambda functions?

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!

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

Posted by Andy Brown on 21 December 2020

In this blog

BMI (Body Mass Index) Calculation
Toilet breaks when watching a film
Removing invalid characters using recursion

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
120-180 minutes	2
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:

=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 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!

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

Some other pages relevant to the above blogs include:

Learn Excel

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

Three examples of lambda functions

BMI (Body Mass Index) Calculation

Toilet breaks when watching a film

Removing invalid characters using recursion

Head office

London

Manchester