Function Reference: LET
| Syntax | ||
| =LET(name1,name_value1,calculation_or_name2,[name_value2],[calculation_or_name3],...[name125],[calculation_or_name126]) | ||
| Purpose / Description | ||
| Returns the result of a multi-step calculation which can be a single value, or more usefully a whole array of values. The function confers some clarity on those calculations which must be built compactly, and provides a boost to calculation, as each step of calculation need be performed only once, rather than the redundancy often used. Laid out well, and using clear conventions for how the formulas are implemented, they can add significant clarity and improve audit of calculations in complex calculation tasks. It works by defining local variable names within the LET function (not accessible to expressions outside the LET function) that can temporarily derive and hold intermediate values that contribute to the overall result, and the final result can be produced by any calculation using those intermediate results. But we would propose the following, as an easier way of understanding the intent... =LET(name1,name_value1,[name2,name_value2]...[name125,name_value125],result) That is, there is an initial required name and calculation, followed by up to 124 extra optional name and value pairs, followed by a result calculation. Unfortunately, the way a function and its optional arguments must work, the convention adopted by Microsoft is the only way to make it work logically, but we have found students find the naming convention confusing. |
||
| Arguments | ||
| Name | Type | Description |
| name1 | text string | A constant text string. References are not permitted, neither are numeric or logical values. The name may not contain spaces and other punctuation, and should not conflict with the name of functions, nor defined names. We have also found that the names rowx and colx cause evaluation conflicts. |
| name_value1 | any | A reference to a range or nested expression that returns a valid reference, a constant or a nested expression that derives the value or array of values to be referred to by name1. |
| calculation_or_name2 | any | If a second step of intermediate calculation is to be used, this must be a text string, following the rules for name1, that names a new internal variable. If this is the final result of the LET function, it may be any calculation that returns any type of value or an array of any that is the final result. |
| [name_value2]... [name_value125] |
any | A reference to a range or nested expression that returns a valid reference, a constant or a nested expression that derives the value or array of values to be referred to by calculation_or_namex. It may refer to any of the already defined name values within the LET function, by referring to the namex assigned to that set of values. |
| [calculation_or_name3]... [calculation_or_name125] |
any | If a subsequent step of intermediate calculation is to be used, this must be a text string, following the rules for name1, that names a new internal variable. If this is the final result of the LET function, it may be any calculation that returns any type of value or an array of any that is the final result. |
| Return Values | ||
| Type | Specific Value | Description |
| (array of) any | The result of the multi-step calculation can be any value type, or an array of any value type. | |
| error | #NAME? | In addition to the #NAME? error being generated in the usual way from a misspelled name reference or function, it is also generated if the local namex references are misspelled. |
| error | Errors returned by intermediate calculations within the LET function may be returned if they are not handled and referenced by subsequent intermediate calculations or the final result. | |
| error | Any errors referenced by arguments are returned due to propagation of error values. | |
| Notes / Comments / Advice / Warnings | ||
| Comment: | Remember that he number of arguments entered must be an odd number. An even number of arguments representing sets of name and name_value followed by a final odd numbered argument, being the overall result of the LET function. | |
| Comment: | In our examples, to distinguish the names within LET functions from defined names or other text, we prefer camel casing. For example, a name for an array of payment periods might be named paymentPeriods. Note that, you can avoid misspellings in the formula by using the name pick list while entering or editing a formula, in the same way as with defined names. We also recommend that inputs might all appear as name and name_value pairs in the first row of the formula, but then each step of calculation should get its own row (press ALT+ENTER to wrap text to a new row). If you use the Advanced Formula Environment, the wrapping and indenting of formula text is managed by that tool. |
|
| Comment: | The LET function is extremely useful in LAMBDA function-based formulas, where multiple steps of calculation are required, especially if stored as user-defined functions. | |
| Examples: | Open / download sample workbook that covers the basics for this function: LET Basics.xlsx More detailed and advanced examples showing how and why to use this function in real-world examples can found in the exercises and min-courses which can be found via the LET function tag below | |