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 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):

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 inenuity, you now have a new set of Excel powers.

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!