LET Function Syntax
The LET function has the following syntax:
=LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3…] )
- name1: is the first name to assign and must begin with a letter. This cannot be the output of a formula or refer to an existing named range.
- name_value1: is the value assigned to the name1
- calculation_or_name2: can be a calculation that uses all names within the LET function or a second name assigned to the name_value2 If this is the latter, then the calculation_or_name3 argument becomes required.
- name_value2: (optional) is the value assigned to the calculation_or_name2
- calculation_or_name3: (optional) can be a calculation that uses all names within the LET function or a second name assigned to the name_value2 If this is the latter, then the calculation_or_name4 argument becomes required.
NOTES: The last argument must be a calculation that returns a result. Also, the names must use the same naming conventions as those used when working with the Name Manager. Ex: Must start with a letter, no spaces in the name.
This all sounds a bit confusing, so let’s look at a practical example to bring things into focus.
Using the LET Function
If you are familiar with naming a range/cell using the Name Manager, this is the same idea.
What makes LET different is that the defined name only exists within the use of the formula and nowhere else. Think of it as a narrowly defined named range that can only be used at that calculation moment.
If we had a value stored in cell A1 and we wanted to calculate a valued added tax, we could write a LET function like the following.
=LET(VAT, 20%, A1 * VAT)
The second argument in our formula (20%) can be a static value, text, or a formula.
I know what you’re saying…
“That’s over-complicating things. Wouldn’t it be simpler to just write =A1 * 20%?”
In this case, it would be simpler. Where things become useful is later when we are writing things like multi-level nested IF statements. The formula will be easier to read and easier to update when changes are needed.
Stick with me; I promise it will get better.
Real-World Use of the LET Function
Using the below data set, we want to calculate the percentage difference between the Actual Revenue and the Budget Revenue.
The catch is we only want to show results for those percentages that are outside a plus or minus 10% threshold. In other words, values that are within +/- 10% are considered “expected” and therefore will be ignored. We only want to focus on the more extreme variances.
Calculating the Difference for All Apps
Just to see what results we have for all Apps we will write the percentage difference calculation for the entire table.
In cell D2, write the following formula and fill it down for the remainder of the table.
=B2 / C2 – 1
NOTE: You could also write this formula as (Actual – Budget) / Budget.
=(B2 – C2) / C2
The results are as follows.
If we manually identify which results we want to see versus the results we want to hide, the selections appear as follows.
Writing the Formula Using Traditional IF Functions
We will rewrite the original formula in cell D2 as follows (note – we are using the ABS function {absolute function} to set all results to positive values. This will make the calculations simpler):
=IF(ABS(B2 / C2 – 1) > 10%, B2 / C2 – 1, “”)
When filled down to the remaining table rows, we see the following.
The Problem with Our Formula
The problem with our formula is that it needs to perform each row’s calculation twice, once for the test and once for the result (if needed).
If you need to adjust the formula, you will need to perform that adjustment twice. Being human beings, prone to mistakes, we may fail to update all formulas or update all formulas the same way.
The opportunity for errors creeping into our formulas rises with every iteration of the logic.
Updating the IF with LET
We will update our formula to capitalize on the LET function. Select cell D2 and add the LET function in front of the IF function.
=LET(VAR, B2 / C2 – 1, IF(……
We are defining a name called “VAR” (for “Variance”) and setting the name to the original calculation (B2 / C2 – 1).
We can now replace all instances of the calculation with the name “VAR”.
=LET(VAR, B2 / C2 – 1, IF(ABS(VAR) > 10%, VAR, “”) )
Using LET with Multiple Variables
To make things more interesting, we could define a variable for the percentage threshold. If we wanted to use the name “T” (for “Threshold”), we would update the formula as follows.
=LET(VAR, B2 / C2 – 1, T, 10%, IF(ABS(VAR) > T, VAR, “”) )
To make things a bit easier to read, we can apply in-cell carriage returns in our formula by using the ALT-Enter key combination.
It’s up to you how many names you want to use in the LET function.
BONUS FORMULA
Below is an example of a LET function (in cell E2 and filled down) working with a 3-level nested IF to display symbols instead of calculations.
Using the ALT-Enter trick, we can display the formula as follows to make it easier to understand.
By using the VAR name, we avoid typing the same potentially lengthy formula multiple times. This reduces the opportunity to induce errors into our logic as well as easing maintenance if the formula needs to be changed.
The results are thus.
Using Symbols as Results
The symbols for arrows up and down were acquired using the Insert – Symbol feature in Excel.
The red circle-dash symbol was acquired by pressing the Windows-period key combination to launch the emoji library.
Coloring the Symbols
The application of color to the symbols is performed using Conditional Formatting.
If you are unfamiliar with the application of Conditional Formatting, consider following the link below for a tutorial of using Conditional Formatting with symbols.
How to use Own Symbols in Excel and Conditionally Format Them
Does LET make your workbooks run faster?
This is a BIG question.
We know there are benefits to the LET function regarding reducing user error during formula creation and maintenance, but will our workbook run faster (or slower) when using the LET function?
The Performance of the LET Function
The Data
Using test data that numbered into the 600K row range, I created the same formulas as above to generate symbols. This would test the calculation speed of the LET function under the same circumstances.
One thing I removed was the Conditional Formatting. This was to ensure that the total calculation time was consumed entirely by the LET function.
Another minor difference was to reference cells that contain the symbols rather than embedding the symbols within the formula.
The Formulas
I wrote three versions of the formula:
- The LET function using 1 variable named “VAR” (the percent difference calculation)
=LET(VAR, A4 / B4 – 1, IF(VAR > 10%, $B$1, IF(VAR < -20%, $A$1, IF(VAR < -10%, $C$1) ) ) )
- The LET function using 2 variables: “VAR” (as before) and “T” for “Threshold”
=LET(VAR, A4 / B4 – 1, T, 10%, IF(VAR > T, $B$1, IF(VAR < -20%, $A$1, IF(VAR < -T, $C$1) ) ) )
- The IF function as would be written if LET was not used
=IF(A4 / B4 – 1 > 10%, $B$1, IF(A4 / B4 – 1 < -20%, $A$1, IF(A4 / B4 – 1 < -10%, $C$1) ) )
The Timer
Using the “Fast Excel” add-in by Charles Williams (link to the download site) the workbook was recalculated multiple times using each formula by itself to obtain an average speed for that formula. The times were measured in milliseconds (thousands of a second).
The results were as follows:
As we can see, there isn’t any real benefit to using the LET function in this scenario. This is likely because our formula(s) is quite simple. We are only dividing one cell against another. It seems that Excel’s natural calculation speed is as good without the LET function lending a hand.
The only benefit for the user is an easier to read and maintain formula.
Giving the LET Function a Challenge
We’ll use ranges of cells manipulated by some of the newer Dynamic Array functions.
To learn more about the wonderful world of life-changing Dynamic Array functions, click here and prepare to be awestruck.
Our data set is below ranging from row 4 to row 74:
Our goals are as follows:
- Obtain the average salary for all departments
- Create a unique list of departments
- Obtain the average salary for each department
- For each department, compare the department’s average salary against the average salary for all departments to obtain a department vs. all variance
- Display a message depending on the variance value: above the average = “Above”, less than average but not more than 10% = “In Range”, and below 10% = “Far Below”.
The process is as follows:
- Obtain the average salary for all departments by selecting cell H4 and entering the following formula.
=AVERAGE(B4:B74)
- Derive a unique list of departments starting in cell H6 using the UNIQUE
=UNIQUE(C4:C74)
We will build the final formula in stages so things don’t get confusing.
- To obtain an average salary for each department from Step 2, Select cell I6 and enter the following formula.
=AVERAGEIFS(B4:B74, C4:C74, H6#)
NOTE: The reason for the # symbol after the H6 reference is to capitalize on the Spill Range (H6:H11) generated by the UNIQUE function.
Since we want to return the verbal responses, and we will be reusing this AVERAGEIFS formula several times, we will place it inside a LET function and give it a user-friendly, easy to understand name of “VAL”.
- Update the AVERAGEIFS formula to include a LET function as witnessed below.
=LET(VAL, AVERAGEIFS(B4:B74, C4:C74, H6#), …
- The remainder of the formula will be the nested IFs that will test the result of VAL against various thresholds and respond with the appropriate text response.
=LET(VAL, AVERAGEIFS(B4:B74, C4:C74, H6#), IF(VAL > H4, “Above”, IF(VAL < H4 * (1 – 10%), “Far Below”, “In Range”) ) )
By using VAL as a short name for the longer formula, we save on overall formula length and complexity. And don’t forget, if we need to update the formula, we only need to perform a single update.
Running a New Set of Performance Tests
Let’s see if we can make the LET function sweat a bit.
We have extended the range of data in the above table to well over 700K rows. We ran two different formulas, one using the LET function, the other using only IFs. These were run multiple times to obtain an average run time.
The results are as follows:
It is obvious that when working with large ranges, the implementation of LET along with the normal functions can greatly improve calculation times by not reselecting the same large ranges over and over.
Everyday Use
Even when you aren’t using crazy-large data ranges, you can still benefit from the LET function in the following ways:
- Easier to read and understand formulas
- Easier to write formulas
- Easier to update formulas
- Reduced user-errors when creating or updating formulas
Practice Workbook
Feel free to Download the Workbook HERE.
ليست هناك تعليقات:
إرسال تعليق