The LET function allows you to define names for portions of your formula that may repeat several times within the same formula.
There are several advantages to using LET in these types of situations:
- The repeating portion only needs to be written once.
- The calculation need only occur once, while the result can be reused as many times as needed in the formula. This can be a great boon to performance.
- Maintenance on the formula is easier since it only needs to occur once.
The repeating formula is given a nice, user-friendly name that is referenced in the actual calculation.
This is like giving a cell a named range, like “Discount”, and using that name in a formula instead of an ugly cell reference, like $K$19.
For example, if we were to calculate a variance that is plus or minus 10% between actual and budgeted revenue, we could write the following formula.
IF(ABS(B2/C2 – 1) > 10%, B2/C2 – 1, “”)
Notice how the calculation “B2/C2 – 1” is performed twice for every row in the table.
Writing this same formula using the LET function, we can write the formula once and give it a name, like “var”, then use that name in every original location.
=LET(“var”, B2/C2 – 1, IF(ABS(var) > 10%, var, “”) )
I know it looks like our formula just got more complicated, but if you look closely you’ll see that the only additional part is the naming of the formula (“var” as equal to the formula “B2/C2 – 1”).
The second half of the formula is the same as the first but only using the name “var” wherever the original formula occurred.