How to use Excel LET function

If you write long complex formulas in Excel, then the new LET function is a perfect match for you. It lets you define variables inside the formula and get calculation results. Excel LET function is useful when you use the same expression multiple times in a formula.

This function is available exclusively for Excel 365 subscribers.

Sign up to Office 365 to get access to all the cool updates and new functions that allow you to be more efficient in Excel.

In this article, you will be provided a detailed guide on the following:

• Introduction to Excel LET Function
• Syntax & Arguments
• LET with one name-value pair
• LET with five name-value pairs
• LET with Emojis
• LET using Name manager

Watch how to use the LET function in Excel on YouTube and give it a thumbs-up!

Download this Introduction to the Microsoft Excel LET Function Tutorial workbook so you can practice along with us:

 

Introduction to Excel LET function

You might have used Named Range in Excel while creating lengthy formulas and know how they make the formula easier to read. LET function is like an advanced version of named ranges.

It lets you assign names directly to the calculation. In this way, Excel calculates the value of the variables just once and uses it several times within the same formula.

The three benefits of using LET functions are:

• Improves formula readability – Assigning a user-friendly name to a range/variable and using it repeatedly in the calculation will make the formula cleaner, easier to read and understand.
• Improves performance – Excel LET function eliminates duplicate calculation. The expression is evaluated at the beginning of the formula and then reused multiple times without further calculation. This increases the speed and performance of the worksheet.
• Easier to update – If you need to change the value of the defined variable, you can easily change it at the beginning instead of updating the same expressions multiple times.

 

Syntax & Arguments

The formula breakdown of the Excel LET function is:

=LET (name1, value1, [name2], [value2], calculation)

where:

• name1 – the name of the first variable defined (it should be without spaces, period, or special characters);
• value1– the value that should be assigned to the first name (it can be text, number, cell reference, or formula);
• name2 – the name of the second variable (optional);
• value2– the value assigned to second variable (optional);
• calculation – the expression or formula that uses these defined variables.

For example:

=LET(x, 5, y, 9, x+y/x)

Here, Excel will assign 5 to variable x, 9 to variable y, and then perform the calculation – 5+9/5. It will return the output as 6.8.

You can define numerous pairs of names and their values and insert a calculation that uses all of them. Excel can handle a total of 126 name-value pairs in this function.

Before you start using this incredible function, there are few things that you should note:

• LET function is not available in any of the older versions of Excel (2010, 2013, 2016, 2019). Only in Excel for Microsoft 365.
• The scope of defined names is limited to that formula only. You can reuse these assigned names multiple times within the formula but not anywhere else.
• There should be several name-value pairs but the last argument should be “calculation”.
• The name argument should start with a letter, an underscore (_), or a backslash (\). It cannot start with an “r” or “c” as they are reserved for the R1C1 style.
• The name argument should follow the same convention as the named range i.e. it should be without spaces, period, or special characters.

 

LET with one name-value pair

Let’s start with an easy example of one name-value pair to simply understand the working of the Excel LET function.

In this example, we have date, expected output, actual output in columns B, C, and D respectively. Since there is a discrepancy between the actual and expected output, we have calculated the difference in percentage between the two in column E.

This will give an estimate regarding how much was actually produced over how much was expected to produce.

• If the difference is less than 0.8, we want to return the word “bad”. It was not a productive day as the units produced was below the expected units.
• If the difference is greater than 1, we want to return the word “good”. It was a productive day as we produced more than what we had expected.

Now, we can use either the Nested IF function or the LET function to achieve this result.

 

Nested IF Function

IF function is used to test a condition and return a value if the condition is met, and a value if it is not. The syntax of the IF function is:

=IF(Logical Test, Value if True, Value if False)

In the example, you will be using the Nested IF function to test both conditions. So, let’s begin!

STEP 1: We need to enter the IF function in a blank cell:

=IF(

STEP 2: Enter the first argument – Logical_test

What is your condition? The difference % should be greater than 1.

=IF(E5>1,

STEP 3: Enter the second argument – Value_if_true

What value should be displayed if the condition is true? We want “good” to be displayed.

=IF(E5>1, “good”,

STEP 4: Enter the third argument – Value_if_false

What value should be displayed if the condition is false? If the condition is not met, we want to insert another IF function.

=IF(E5>1, “good”, IF(E5<0.8,

STEP 5: Enter the value if the condition of the second IF function is met.

=IF(E5>1, “good”, IF(E5<0.8, “bad”

 

STEP 6: Lastly, enter a blank when both conditions are not met.

=IF(E5>1, “good”, IF(E5<0.8,”bad”,“”)

STEP 7: Copy the formula below in the column.

 

LET Function

In this example, we will assign the difference % to a variable “pchg” using the LET function and then calculate the result.

STEP 1: We need to enter the LET function in a blank cell:

=LET(

STEP 2: Enter the first argument i.e. name

=LET(pchg,

….

STEP 3: Enter the second argument i.e. value to the name assigned. Here, the value is inserted in cell D5.

=LET(pchg, E5,

STEP 4: Now enter the entire nested IF function and replace the cell reference D5 to the variable pchg.

=LET(pchg, D5, IF(pchg>1,”good”,IF(pchg<0.8,”bad”,””))

As you can see, the LET function does exactly what the IF function was doing when we are using just one variable.

Let’s look at another example where using the LET function will be more useful than the nested IF .function.

 

Example 2:

In this example, we have sales from two branches, and we need to calculate commission based on it:

• If the total sales are greater than $7,000, the commission will be 10%;
• If the total sales are greater than $5,000, the commission will be 8%;
• If the total sales are less than or equal to $5,000, the commission will be 0.

In the worksheet below, we have two columns E and F that will contain the commission amount calculated using LET and Nested If respectively.

Using the Nested IF function, we can use the following formula to total commission:

=IF(SUM(C5:D5)>7000, SUM(C5:D5)*10%,IF(SUM(C5:D5)>5000, SUM(C5:D5)*8%,0))

Follow the steps below to get the work done using the LET function:

STEP 1: Enter the LET function in a blank cell:

=LET(

STEP 2: Enter the name for the variable to calculate the total sales i.e. total

=LET(total,

STEP 3: Enter the formula for the defined variable i.e. SUM(C5:D5)

=LET(total, SUM(C5:D5),

STEP 4: Enter the IF formula using the variable “total”.

=LET(total,SUM(C5:D5), IF(total>7000,total*10%,IF(total>5000,total*8%,0))

If we are using the Nested IF function only, Excel has to calculate the SUM function four times:

=IF(SUM(C5:D5)>7000, SUM(C5:D5)*10%,IF(SUM(C5:D5)>5000, SUM(C5:D5)*8%,0))

Whereas, when we are using the LET function, SUM is calculated just once at the beginning and the calculated value is used in the formula later. This makes the calculation faster and the formula cleaner.

=LET(total,SUM(C5:D5), IF(total>7000,total*10%,IF(total>5000,total*8%,0))

 

Using variables in Excel formulas makes updating the formula quick and easy.  Let’s say we have added another branch in our calculation i.e. West Branch and you need to update the formula.

To do so, simply edit the value of the defined variable from SUM(C5:D5) to SUM(C5:E5) and the total commission will be updated automatically.

=LET(total,SUM(C5:E5), IF(total>7000,total*10%,IF(total>5000,total*8%,0))

 

Until now, we have just used one name-value pair, let’s try using more and see how that works!

 

LET with five name-value pairs

In the previous feedback example instead of using one name-value pair, we will now be using 5.

You can see that there are five variables that we can use in this formula:

• Difference % – pchg
• Percentage you want to check if the difference is good – pergood
• Percentage you want to check if the difference is bad- perbad
• The text you want if the difference is good – textgood
• The text you want if the difference is bad- textbad

 

STEP 1: We need to enter the Excel LET function in a blank cell:

=LET(

STEP 2: Enter the first argument i.e. name of the 1st variable

=LET(pchg,

….

STEP 3: Enter the second argument i.e. value to the name assigned. Here, the value is inserted in cell D5.

=LET(pchg, E5,

 

STEP 4: Enter the name for the 2nd variable i.e. pergood

=LET(pchg, E5, pergood,

STEP 5: Enter the value for the 2nd variable i.e. 1.

=LET(pchg, E5, pergood, 1,

STEP 6: Enter the name for the 3rd variable i.e. textgood

=LET(pchg, E5, pergood, 1, textgood,

STEP 7: Enter the value for the 3rd variable i.e. “good”. Since it is a text make sure to put it within quotes.

=LET(pchg, E5, pergood, 1, textgood, “good”,

STEP 8: Enter the name for the 4th variable i.e. perbad

=LET(pchg, E5, pergood, 1, textgood, “good”, perbad,

STEP 9: Enter the value for the 4th variable i.e. 0.8

=LET(pchg, E5, pergood, 1, textgood, “good”, perbad, 0.8,

STEP 10: Enter the name for the 5th variable i.e. textbad

=LET(pchg, E5, pergood, 1, textgood, “good”, perbad, 0.8, textbad,

STEP 11: Enter the value for the 5th variable i.e. “bad”

=LET(pchg, E5, pergood, 1, textgood, “good”, perbad, 0.8, textbad, “bad”,

STEP 12: Lastly, enter the nested IF function and replace the cell reference, text, and numbers with the defined variables.

=LET(pchg, E5, pergood, 1, textgood, “good”, perbad, 0.8, textbad, “bad”, IF(pchg>pergood, textgood, IF(pchg<perbad, textbad, “”)))

Copy paste this formula to the remaining cells below.

Using the Excel LET function makes your formula easier to read and update if required in the future.

 

LET with Emojis

Instead of showing the text “good” or “bad, we can even use emojis. In windows, you can press keys – Windows + . to open the dialog box for emojis and simply click on the one you want to use.

In step 7, replace the text “good” with smile emoji:

=LET(pchg, E5, pergood, 1, textgood,”😊”

In step 7, replace the text “bad” with a thumb down emoji:

=LET(pchg, E5, pergood, 1, textgood, “😊”, perbad, 0.8, textbad, “👎”

This is what the result will look like. Looks interesting, isn’t it?

=LET(pchg, E5, pergood, 1, textgood, “😊”, perbad, 0.8, textbad, “👎”,IF(pchg>pergood,textgood,IF(pchg<perbad,textbad,””)))

 

LET using Name manager

You can store the long LET formula using name manager and then easily reuse it whenever you want in the future without the need to type the formula again.

Before we do that, if you look at the formula you will see that column E is a relative reference i.e. if you use this formula the reference will also change. So, if you paste this column in column H, the reference will change from E5 to F5.

But we don’t want that. So, let’s lock column E by putting a dollar sign in front of it.

Now that your formula is ready, follow the steps below to learn how to store it in name manager:

STEP 1: Copy the entire LET formula by pressing the keys Ctrl + C.

STEP 2: Go to Formula Tab > Name Manager.

STEP 3: In the dialog box, click on New.

STEP 4: Type the following in the dialog box:

• Name – DisplayEmoji
• Scope – Select Workbook
• Refers to – Paste the formula using Ctrl +V
• Click OK

STEP 5: In cell H5, type the function

=DisplayEmoji

STEP 6: Copy-paste the formula below and you will see it is displaying the same result as before.

You can use this formula =DisplayEmoji anywhere in the spreadsheet and it will display the same result!

 

Conclusion

The Excel LET function is a super useful function that helps you eliminate redundancy in a formula and make the formula easy to read and compose. It improves the performance of your spreadsheet since the variables in the Excel formulas are calculated at the beginning and reused later in the formula.

This function is currently available for Office 365 users only. So if you want to use this function make sure to install Office 365 in your system!