Excel's Let() function

Let's follow up with the Map() function and talk about the Let() function in Excel (Office 365).

The Let() function assigns names to your calculations within a cell. This allows you to assign structure to your calculations for readability/debugging purposes. The format of the function is:

So, if I can say

=Let(
    house, 500000,
    tax_rate, .005,
    tax, house * tax_rate,
    tax)

the variable tax will equal $2,500, and the formula will return $2,500. All variables used in that calculation will be private to that cell. All other cells will not recognize the variable house, tax_rate, or tax. As of this writing, you can have 126 variables within a Let() function. Within the editor, you can have all the variables and calculations on one line or use Alt-Enter to make it easier to read. I recommend using Alt-Enter.

I can get the same results by not defining the tax variable.

=Let(
    house, 500000,
    tax_rate, .005,
    house * tax_rate)

However, I feel that you increase the readability by defining the tax variable (first example).

Let's say we have a table (tbl_EE) that looks like this:

This is the table we used with our Map() function example. If you remember, it's just a data dump from our HRIS for executive rewards. I converted it to a table and named it "tbl_EE". It's a simple table that lists the employee name, salary, short-term incentive percentage, long-term incentive percentage, and department. Most executive compensation professionals download more information, but this is a simple example.

In the simple example below, we calculated the Total Cash (E14), by defining

var_sal as C14,

var_STI as D14* var_sal,

var_cash as var_sal + var STI,

and returning var_cash. Then we copied down.

We could have used =C14*(1+D14), but… Remember the Let() function allows us to name and assign variables within a calculation. Sometimes, formulas get super hairy. How many times have you had to debug a 250-character If() statement? People move jobs. You are not doing the person who inherits your spreadsheet any favors with a formula with 12 embedded If() functions.

Let's look at the example below. We want to calculate Total Compensation. Total Compensation is defined as:

You can use the Let() function to calculate, and it’s much more readable. You can even add a comment variable.

You can have some very sophisticated Let() functions. In a further blog post, I'll give you some examples. But for now, here is what we reviewed.