Introduction
LAMBDA() functions are relatively new to Excel and I have used them in this format: BYCOL(data,LAMBDA(column,FUNCTION(column))) and MAP and SCAN and a couple more. I use them to create dynamic descriptive statistics tables because it makes them dynamic AND it is so easy to do. This post, then, starts by showing you how to create a table of descriptive statistics and then it shows you the very first real LAMBDA() function of my own that I did yesterday!
What follows is an example of the use of the BYCOL()/LAMBDA() Function that is then followed by my own LAMBDA() Function.
Example: Descriptive Statistics
We can use the Data Analysis ToolPak in Excel to create a table of Descriptive Statistics for a variable and it's fine BUT it is a static table which means that if the data change, the statistics don't. That then means we need to run the descriptive statistics utility again. Boring! It's even worse when we create a table of descriptive statistics for several variables at the same time: in that case, we start by seeing this:
Identify the variables and range you want to use and then Data, Data Analysis, Descriptive Statistics
Using the BYCOL() LAMBDA() Function
What is a LAMBDA Function and How to Create Them?
The LAMBDA function in Excel is a powerful feature that
enables you to define custom, reusable functions directly within Excel
formulas. Introduced in recent versions of Excel, the LAMBDA function allows
for creating more modular, concise, and understandable formulas.
Basic Structure
The LAMBDA function has the following structure:
LAMBDA([parameter1, parameter2, ...], calculation)
Parameters: These are placeholders you define, representing
the inputs your custom function will use. You can specify any number of
parameters.
Calculation: This is the expression or calculation that the
LAMBDA function will execute using the provided parameters.
Key Features
Reusability: Once defined, a LAMBDA function can be saved as
a named function and reused throughout your workbook, just like any other Excel
function.
Modularity: You can build complex formulas in a step-by-step
manner, enhancing readability and maintenance.
Recursion: LAMBDA functions can call themselves, allowing
for powerful recursive operations. However, recursion must have proper exit
conditions to avoid infinite loops.
Example Usage
Here are four examples of
how to use a LAMBDA() function in Excel
Converting Fahrenheit to Celsius
Taking just one of those examples, how to convert a
Fahrenheit temperature to a Celsius temperature. The formula is the formula you
see at the bottom of the following screenshot:
=LAMBDA(temp,(5/9)*(temp-32))
What we have done here is to tell LAMBDA to assign a temp
value or temporary value that it can find in a cell somewhere and do this
Divide 5 by 9
Multiply the answer to that by subtracting 32 from the temp
value.
For example, 104 degrees F becomes
5/9=0.555555555 recurring
(104-32) = 72
104F = 0.555555555*72 C = 40 C
What we are seeing in the screenshot is how we store our
LAMBDA() function as our own Excel Function that we can use at any time in this
workbook.
Create your LAMBDA() function … =LAMBDA(temp,(5/9)*(temp-32))
Formulas è
Name Manager è
New è complete the dialogue
box below, the screenshot
Click OK
From the screenshot above that shows the four different
examples, enter 104 in cell D3 and in cell E3 type =ToCelsius(D3) and Press
Enter
Answer = 40 … Celsius … Done!. Repeat for the values in
cells D4:D7
LAMBDA() Formulas
Here are the LAMBDA() formulas for the other examples in my
list of four:
A+1 =LAMBDA(x,x+1) … I called it MyLambda()
F to C =LAMBDA(temp,(5/9)*(temp-32)) … I called it ToCelsius()
Hypotenuse =LAMBDA(a,b, SQRT((a^2+b^2))) … I called it Hypotenuse()
Count Words =LAMBDA(text, LEN(TRIM(text)) -
LEN(SUBSTITUTE(TRIM(text), " ", "")) + 1) … I called it CountWords()
Your Own Excel Function
By using the Name Manager approach, you have created your own User Defined Function for that workbook. Whenever you use that workbook, you can use your own Function, MYLambda(), ToCelcius(), Hypotenuse(), CountWords() ...
Can you see how these formulas work?
We tell LAMBDA the one or more variables to use: call them
a, b, c, … or x, y, z … or temp
To create the formula or logic that will solve your
problem, and make sure to use a, b, c or x, y, x or temp in that formula as parameters and
then create your functions using the Name Manager routine above.
Another Example … for you to do
Let's say you want to create a custom function to calculate
the area of a circle. Normally, you would calculate this with the formula π *
radius^2. With LAMBDA, you can define a reusable function.
Think about this for a moment … try it yourself. Then check
my answers at the bottom of this post.
Advantages
Customization: Tailor functions to your specific
needs without requiring VBA or external scripts.
Clarity: Break down complex operations into simpler,
named steps.
Collaboration: Share your custom functions with
others by sharing the workbook, enhancing collaborative efforts.
The LAMBDA function essentially extends Excel's capabilities by allowing you to define functions that behave like native functions, offering flexibility and enhancing the functional programming capabilities within Excel.
New LAMBDA
In my case, my LAMBDA function has the sole purpose of finding the Payback Period from the Project 1-4 inputs we have been discussing and it looks like this:
=PBPeriod(H15:H20,I15:I20,J15:J20,K15:K20)
Simple! What it is telling us is that in the range H15:H20 you will find the input data relating to Projects 1 to 3, as you can see below:
You need to download and open my file to understand what I have done here since the LAMBDA Function Results shown above relate to just one of the four projects I have included here: Project 1 in this case. That's because I have set up my model as a Scenario Model in which I check each project one by one.
Here are the results of projects 2, 3 and 4, as well: as Project 1 and I have used Excel's Scenario Manager to help me to input the data and then present the output in a logical way:
The results we are looking for are in rows 14:20 and they tell us that the Payback Period (PB) for each project are, in years
Project 1 2.7242
Project 2 3.2714
Project 3 4.3956 AND 4.4530
Project 4 4.12
To make this work, we set up the LAMBDA() function and create our PBPeriod function from there, by using Formulas, Name Manager:
What we have done here is set up the formula that we use on a row by row basis in Excel to test for and find the Payback Period for each project and when we do that, we use four input values: a, b, c, d, which are the year the cumulative cash flow and the cash flow for the year following the year for which we are estimating the Payback period. Please note, a, b, c, d are as defined in this formula and only for this formula: they are not defined for use in anyother function of formula: unless, of course, you have also set them up as range names.
To make this work, we set up our working sheet and output column as we have already seen under the heading of LAMBDA Function Results, with our own, newly created used designed function PBPeriod, which we enter in cell L15 and that SPILLS down automatically:
=PBPeriod(H15:H20,I15:I20,J15:J20,K15:K20)
If you prefer, you can amend that formula as follows, to show all results in the same cell:
=MIN(PBPeriod(H15:H20,I15:I20,J15:J20,K15:K20))
Work through my Excel file, link below. Make yourself familiar with what I have done and make sure you can make it work.
Conclusions
For this example, for these projects, this is a LAMBDA() function to mimics my manually devised function that I have been using for years for this case exercise. All that my new formula does is that is translates my manually created formula into LAMBDA() style and returns the answers I need. By wrapping MIN() around my LAMBDA() function, I get a slightly more direct result.
If you do not want to use the Scenario Manager, you can use, for example, one or more of the Form Controls that we can find on the Developer tab or by using Data Validation. I have not created such alternatives at the time of writing but if and when I do, I will share them here.
Download my file
Duncan Williamson
28th April 2024 Updated 29th April 2024
For you to do Solution
The answers to the question I set you on finding the area of a circle is here.
No comments:
Post a Comment