Metric to Imperial/US conversion or vice-versa can be a little messy in Excel, the new Lambda() feature lets you make an easier and simpler conversion function.
It’s a simple and practical example of what’s possible with Lambda(). See Lambda – strange name for a really good extra in Excel
Metric <> Imperial/US conversion with Convert()
The standard way to convert between measures in Excel is the Convert() function.
=CONVERT(B2,”mi”,”km”)
=CONVERT(B2,”km”,”mi”)
It works but is a little obscure. Some of the measuring codes aren’t obvious. ‘oz’ is fluid ounces, weight in ounces is ‘ozm’. There are three mile options, statute, nautical and US Survey.
Conversion with Lambda()
The new Lambda feature let’s you make Excel custom functions without VBA. As a simple example, the above Convert() functions can be transformed into
=Miles2Km()
=Km2Miles()
These are simpler, shorter, easier to understand and less likely to be accidently changed.
Making a Lambda() function
Here’s how to convert a formula into a Lambda() function.
Start by making and testing the formula.
It’s a lot easier to test a Lambda() function separately before installing it, because you’re very limited in editing a working Lambda() formula.
Next, test as a full Lambda() function. That’s possible in a single cell by adding the parameters in brackets after the main formula.
=LAMBDA(Miles,CONVERT(Miles,"mi","km"))(5)
This passes the parameter (5) into the Lambda formula where it’s named as the ‘Miles’ variable.
Once you’re happy with the Lambda formula, load it into the workbook for general use.
Go to Formula | Define Name. Give a name to the new function and paste the Lambda() formula into the Refers to: field.
Now the function appears in the list as you type:
Treat the custom function just like any other function. Pass in a parameter or parameters. Add into longer formulas etc.
The reverse function is also possible.
=LAMBDA(Kms,CONVERT(Kms,"km","mi"))
Of course, in this example you don’t have to use Convert(), a simple multiplication is enough and very fractionally faster.
=LAMBDA(Miles,Miles* 1.609344)
In the end, you have collection of custom formulas ready to use throughout the workbook.
Lambda – strange name for a really good extra in Excel
Lambda() downsides need to be fixed
Flash Fill Magic in Excel
LET() assigns names to calculations in Excel
