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.

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!