Having fun with Excel LAMBDA function – building a BMI calculator and MPG conversion formulas

Let’s face it, despite the ever-growing catalog of 500(!) or so Excel functions, we cannot expect Microsoft developers to create all functions that meet the needs of every Excel user. Even if this feat were possible, the program itself would become unusable due to the sheer number of functions made available blocking us from using a few that we actually need. Traditional solution is age-old – using VBA to code a User Defined Function (UDF.) In fact, we’ve already covered the process of creating UDFs here and here.

Fast forward to 2016, when Excel teased the idea of the LAMBDA function, initially limiting its release to Office 365 subscribers opted in into the Office Insider edition. Since then this “ultimate” Excel function went through a number of revisions and enhancements and while it didn’t make its wider debut in time for Excel 2021 version, it was eventually released to the general public (i.e. all current Office 365 subscribers) earlier this month. We can now create a custom Excel function without having to deal with the Visual Basic Editor window ever again.

Introducing LAMBDA function

So, what’s so special about this LAMBDA function anyway?

To start with, we can not only create functions tailored to our unique needs, but also reuse them by naming them appropriately, without having to write a single line of code. This function can help us fully customize and augment built-in function library in Excel. No longer do you need to deal with lengthy formulas, you can easily replicate their functionality within your workbook by invoking your custom function. These formulas can be as complex as you need them to be and LAMBDA even supports array functions – we will cover them in the next post.

Limitations and pitfalls of the LAMBDA function

Of course this function is not a panacea and a couple of its limitations include: the fact that it is workbook-specific, greatly reducing the practical adaption of this function; lack of the Help functionality further restricts users’ ability to take a full advantage of the LAMBDA function. We will cover a small work-around against the latter in our examples below.

Other factors to be on the look-out for, include:

• #VALUE! error will be returned if you pass an incorrect number of arguments OR more than 253 parameters to your formula with LAMBDA function
• #CALC! error occurs when Excel encounters a scenario it does not currently support, specifically when your LAMBDA function is present in a cell without also calling it from within the cell
• #NUM! error is likely a circular reference when you call LAMBDA function from within itself
• Avoid using a period [.] symbol your parameter names

Working with LAMBDA function

The syntax of the function calls for setting your parameters separated by a comma, with the last coma preceding your calculation and inputs provided in a separate set of parentheses :

As an example, to calculate our Gross Profit Margin of 25% for our product sales where our Price/Revenue is $10 and Cost/COGS = $8, we could implement a formula as following:

=LAMBDA (Price, Cost, Price/Cost-1) (10,8)

Let’s go through a couple more examples on how to put this function to a good use. We will start with the good ole’ BMI calculator to further reinforce author’s unhealthy obsession with calculating BMI, be it in Java Script, VBA, or even Python.

BMI Calculator

As a reminder, in order to calculate once Body Mass Index, we would need to divide our weight (in pounds) by height (in inches) squared, multiplied by 703:

BMI = Weight (in pounds) / (Height (in inches) ^ 2) * 703

With weight stored in cell C5 and height located in cell C6, we can construct our formula to include two arguments and specify our inputs as following:

=LAMBDA(WeightInLbs, HeightInInches, WeightInLbs/(HeightInInches^2)*703)(C5,C6)

To save this formula for a future use, we can create a custom function by invoking a Formula Manager [shortcut: CTRL + F3] in the below manner. Notice that while LAMBDA does not support tooltips, we can override this limitation by providing additional comments to help users get more information about our formula.

Using the same input cells, our formula becomes:

=BMI(C5,C6)

Or, a version with error handling:

=IFERROR(BMI(C5,C6),"BMI cannot be calculated, check your inputs!")

Putting it all together, we have:

Fuel Economy calculation

Suppose you want to compare fuel economy numbers of Škoda Kodiaq (available in European markets only) with its American cousin – Volkswagen Tiguan. You’d need to navigate unnecessarily confusing world of metric system alive and well everywhere outside of US and the US system. To complicate things further Europeans measure fuel consumption based on the distance driven – Liters per 100 km (aka L/100 km) , while Americans use distance per fuel consumed – Miles per Gallon (aka MPG). If this were not enough, the British and some of the Commonwealth nations purchase their fuel in liters, yet measure fuel economy in Imperial/UK gallons – UK MPG. Peculiarly enough, Canadians use either L/100 km or UK MPG.

To start off with our calculations, let’s get some measurements out of the way:

1 Mile = 1.60934 Kilometers

1 US Gallon = 3.78541 Liters = 0.83267 US Gallons

1 UK Gallon = 4.54609 Liters = 1.201 US Gallons

For the L/100 km to MPG conversion, let’s take 100 L/100 km and convert into MPG: 100 Liters = 378.541 Gallons, while 1 Mile = 1.69034, so dividing 378.541 by 1.60934 gives us a conversion factor of 235.2151. As a result our formula becomes:

US MPG = 235.2151/(L/100 km)

Similarly, to convert UK MPG into US MPG we have:

US MPG = UK MPG * 0.8326

Since we need to use either of the conversions in our formula, we can utilize ISOMITTED function – one of the newer helpful LAMBDA functions by the Excel team. With L_100km representing Liters per 100 km and UK_MPG standing for UK Miles per Gallon, while Cell B5 containing L/100 km input and cell B10 UK MPG, the formula to convert L/100 km fuel economy becomes:

=LAMBDA(L_100km,UK_MPG,IFS(ISOMITTED(L_100km),UK_MPG*0.8326,ISOMITTED(UK_MPG),235.215/L_100km))(B5,)

Similarly, to convert UK MPG into US MPG we would use the below:

=LAMBDA(L_100km,UK_MPG,IFS(ISOMITTED(L_100km),UK_MPG*0.8326,ISOMITTED(UK_MPG),235.215/L_100km))(,B10)

To create our custom MPG function, let’s invoke Formula Manager once again:

Below formulas put this function to a good use for either of the scenarios:

=MPG(B5,)            
=MPG(,B10)

Additional examples can be illustrated further:

To help you get started, feel free to download workbook companion for this post.

How will you put LAMBDA function to a good use? What type of custom formulas will you create?