Excel LET Function
Definition and background and Syntax
The LET function assigns names to calculation results. This allows storing intermediate calculations, values, or defining names inside a formula. These names only apply within the scope of the LET function. Similar to variables in programming, LET is accomplished through Excel’s native formula syntax.
To use the LET function in Excel, you define pairs of names and associated values, and a calculation that uses them all. You must define at least one name/value pair (a variable), and LET supports up to 126.
=LET(name1_of_variable1, name_value1, calculation_or_name2_of_variable2,[name_value2_of_variable2, calculation_or_name3_of_variable3 . . . ])
The LET function can take up to 132 variables.
The LET function with more than one variable can look like this:
=LET(name of variable1, name_value1, name of variable2, name_value2, calculation)
Argument | Description |
|---|
name1 Required | The first name to assign. Must start with a letter. Cannot be the output of a formula or conflict with range syntax. |
name_value1 Required | The value that is assigned to name1. |
calculation_or_name2 Required | One of the following: A calculation that uses all names within the LET function. This must be the last argument in the LET function. A second name to assign to a second name_value. If a name is specified, name_value2 and calculation_or_name3 become required.
|
name_value2 Optional | The value that is assigned to calculation_or_name2. |
calculation_or_name3 Optional | One of the following: A calculation that uses all names within the LET function. The last argument in the LET function must be a calculation. A third name to assign to a third name_value. If a name is specified, name_value3 and calculation_or_name4 become required.
|
Benefits
- Improved Performance: If you write the same expression multiple times in a formula, Excel calculated that result multiple times. LET allows you to call the expression by name and for Excel to calculate it once.
- Easy Reading and Composition: No more having to remember what a specific range/cell reference referred to, what your calculation was doing or copy/pasting the same expression. With the ability to declare and name variables, you can give meaningful context to yourself and consumers of your formula.
Notes:
The last argument must be a calculation that returns a result.
Names of variables align with valid names that can be sued in the name manager. E.g. "a" is valid but "c" is not because it conflicts with the R1C1 style references.
Examples
1. You have a standard variable of 150 that you want to the measure actual output against.
The variable has been given a name of "standard" and the value of cell C4 has been locked in as the value. Instead of $C$4 the value of 150 could have been hardcoded as the value.
2. When you use the function =SUM(..) the LET function can be used to allocate variables to the
arguments of the SUM function.
=SUM(x,3) will add variable x to 3 to get the total. This can be expressed in a LET function where
we give x a value of 5 and the result will be 8; (x +3) which result in (5 + 3)
Build into a let function it will look as follows:
=LET(x, 5, SUM(x, 3))
When we input this formula into a cell it will return 8.
3. Below is sales data that I want to filter so that the data show one sales person data and a dash are
added to any blank cells. Say I want to show the data for Fred only.
The FILTER and ISBLANK function can be used, wrapped in an IF function to get to the answer
with the original formula.
=IF(ISBLANK(FILTER(B3:E9;B3:B9="Fred"));"-";FILTER(B3:E9;B3:B9="Fred"))
Inside a LET formula the result will be the same.
=LET(Criteria;"Fred";Range;FILTER(B3:E9;B3:B9=Criteria);IF(ISBLANK(Range);"-";Range))
3. Given the following data we can assign variables in the let function and do the calculation:
Fixed Labor cost to manufacture four products is $500.00
The cost for each product are as follows:
Product A: $55.00
Product B: $60.00
Product C: $120.00
Product D: $10.00
The markup on cost that we want is 50%. We can use the LET function to calculate the total sales value of $1,117.50.
Solution:
Name the variables that will be used in the LET function and allocate a value to each variable,
Variable: TPC = Total Cost Value: =SUM(E6:E9)
FLC = Fixed Labor Cost Value: =$E$3
MU = Mark up Value: =$E$11
Calculation: (TPC+FLC)*(1+MU)
Write the LET function based on the identified variables and their values.
=LET(TPC,SUM(E6:E9),FLC,$E$3,MU,$E$11,(TPC+FLC)*(1+MU))
4. I have four sales representatives and I want to hand out weekly incentives for them if they have met
certain criteria during each five day workweek.
Each days sales that exceed $100,000.00 are taken into account, at least three of the five days sales
need to exceed $100,000 and the total of the three days sales need to exceed R750,000.00.
Solution:
Name the variables that will be used in the LET function and allocate a value to each variable,
Variable: MINSC = Number of days where the minimum sales were reached by the
sales representative.
Value: =COUNTIFS($B$3:$B$22,E3,$C$3:$C$22,">"&100000)
Variable: TS = Total sales of the days where sales exceed $100,000.00 per day per sales
representative
Value: =SUMIFS($C$3:$C$22,$B$3:$B$22,E3,$C$3:$C$22,">"&100000)
Calculation: =IF(AND(MINSC>=3,TS>750000),"Price","")
5. Calculate commissions earned per sales representative if the following criteria apply:
- Pool Heating Panels (Orders over $500 000.00) earns 1% commission
- Sine Inverter earns 1% commission irrespective of order value
- Grit Inverter (Orders over $1 000 000.00) earns 3% commission
- Ring Solar Panel earns % commission irrespective of sales order value
Comments
Post a Comment