Create your first Excel LAMBDA function
Here’s how to create a set of custom, lambda functions in Excel. No macros or VBA required.
You can now create your own custom functions in Excel without VBA or macros.
These are known as LAMBDA functions, and here’s how to to it..
Let’s create a “between” function, which compares a cell value to other cells containing a minimum and maximum value. If the cell value is within the range of these min and maximum values, then the function will return TRUE.
A LAMBDA function needs at least one parameter, and your desired formula, without the preceeding =.
It always starts with ` =LAMBDA`.
Given the above requirements, our formula will look like this:
=LAMBDA(val, lo, hi, AND(val >= lo, val <= hi))
val, lo, and hi are the function parameters ( the cell value, and min / max values), followed by a straightforward AND formula. Both these conditions must be met for the formula to return TRUE.
The initial formula syntax can be entered into any spare cell. However, you must also pass some test values in a set of parenthesis, immediately after the syntax (see cell K2 in the screenshot below, where I pass in A2, G3 and G4):
We don’t need to repeat the formula, just pass in the relevant cell references for each parameter.
If you don’t do this, you will not be able to save the function definition.
Once we’ve done this, its off to the Name Manager to define the formula properly.
We don’t need to pass any test values, just the formula itself.
We can also add comments to remind ourselves what the function parameters are, and indeed, what the function is for.
Use Ctrl + J to ensure these comments appear on separate lines
In addition to between, I defined inside. This is similar to between, except the min and max values are excluded
=LAMBDA(val, lo, hi, (AND(val>lo, val<hi)))
Here are the comments for this LAMBDA function:
check if value is greater than lower limit and less than higher limit, excluding the boundary values
val - cell to compare
lo - lower value limit
hi - higher value limit
I also defined a member_of function, to check if a value is one of a set of 3 target values
=LAMBDA(val,target1,target2,target3, OR(val = target1, val = target2, val = target3))
Here are the results of comparing a number of values to a min/ max and set of target values, using these three functions
You can see the actual formula in each cell:
These are simple examples, but don’t be mistaken. With some ingenuity, you now have a new set of Excel powers.
Advantages - wrapping complicated IF statements into functions simplifies the development and maintenance of a spreadsheet. I’m fairly certain it also reduces calculation time / complexity, combatting workbook bloat. Documenting your functions makes it easier to understand what’s going on - we’ve all had to debug someone else’s spreadsheet while wondering what the heck is going on.
Disadvantages - they are confined to the workbook where they are created. You will need to create an external resting place for your most common functions so you can copy and paste between workbooks as required.
Ten years ago, I began learning R because I couldn’t achieve what I wanted to in Excel.
I’ve been able to replicate the results of my {runcharter} package using some custom LAMBDA functions.
There is life in Excel yet!