Welcome to Excel Jet Consult blog post. In this tutorial, we will learn about how to use the newly released LET Function for modelling. Let’s get started
Definition of Function & 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, 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.
Syntax:
LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3…])
name1: The first name to assign. Must start with a letter. Cannot be the output of a formula or conflict with range syntax.
name_value1: The value that is assigned to name1.
calculation_or_name2: A calculation that uses all names within the LET function. This must be the last argument in the LET function. Or, A second name to assign to a second name_value. If a name is specified, name_value2 and calculation_or_name3 become required.
Example and Solution
In the caption below, we have a data for modelling. What we want to do essentially is to calculate the Estimated Hours required to produce each of the Computer Components produced by XYZ Computer Group. We will achieve this using the classic project management technique of Most Pessimistic, Most Likely and Most Optimistic estimates. In addition, we assigned weightings of 2, 6 and 4 respectively to the techniques mentioned above.
SUMPRODUCT FUNCTION METHOD
In this first example, we are going to use the elegant SUMPRODUCT function to calculate the Estimated Hours. Therefore, in cell E6, execute the formula:
=SUMPRODUCT((B6:D6*$B$4:$D$4)/SUM($B$4:$D$4))
Click Enter and copy down the formula. In the caption below, we can see that it required 1,494 Total Estimated Hours to produce all the Computer Components
LET FUNCTION METHOD
In this second example, we are going to use the new LET Function to model the estimated hours required to produce all the components. Therefore, in cell F6, execute the formula:
=LET(Units,B6:D6,weightings,$B$4:$D$4,SUM(Units*weightings)/SUM(weightings))
Click Enter and copy down the formula. In the caption below, we can also see that it required 1,494 to produce all the components. Exactly the same result that the SUMPRODUCT delivered
For more explanation, kindly watch the Youtube video below and do subscribe to my Youtube channel