# Create your first Excel LAMBDA function

**Data By John**, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)

Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

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!

**leave a comment**for the author, please follow the link and comment on their blog:

**Data By John**.

R-bloggers.com offers

**daily e-mail updates**about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.

Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.