LET - Names in Formulas - Generally Available
Published Nov 16 2020 12:10 PM 26.4K Views
Microsoft

Earlier this year we announced the release of a new function, LET, which allows you to give names to calculations or expressions in your formulae (Announcing LET). As of earlier this month, the LET function is now generally available within the Production audience!

 

Thanks to all of our Insider's for using the LET function and giving us feedback! As a result we've made a few changes that we'll outline below. Additionally, we’d like to share some best practices to consider when using the function or writing formulas in general!

 

Accessing LET Today

To get access to LET, please make sure you have updated to the latest version of Excel. 

LET is available to Excel subscribers with Office 365 or Microsoft 365. The feature is currently available on the Current Channel across all platforms and generally on the web.

 

Specifically versions of Excel greater than or equal to:

  • Windows: Version 2009 (Build 13231.20262)
  • Mac: Version 16.42 (20101102)

What version of Office am I using?

 

Changes made to LET

1. Autocompletion of names

We added a quality of life improvement whereby autocomplete will special case names defined by a LET with a unique icon and entry.

 

In the following example, you can see the special icon LETIcon.png indicating that you can autocomplete your formula with the count name:

 

=LET(count, 123, count

LET GIFLET GIF

 

2. Allowed Names

"." is no longer a legal character in names bound by a LET. If you happen to have a pre-existing workbook which has a "." in its name, it will be silent upgraded to a legal name.

 

The reason for this change was due to conflicts which could occur within “dot notation” for dereferencing Data Types.

 

To learn more about what names are allowed, please see the Help topic for LET.

 

3. Localization changes

Thanks to feedback from the community, we have removed localization for the LET function. LET is the name of the function in all SKUs of Excel and not localized.

 

Best Practices when Authoring Formulas

With the addition of LET, there are some new best practices we have found useful when authoring formulas, in general and with LET. We'd love to hear more from you if there are other best practices you have employed!

 

Example Scenario: Converting and formatting Temperature Values

For the below examples, feel free to follow along and try it out for yourself with the included sample data. The example LET fills out the values in the Fahrenheit column

 

Day

Celsius

Fahrenheit

January 1

4.68

 

January 2

4.49

 

January 3

4.4

 

January 4

4.71

 

 

Line Breaks

In desktop versions of Excel, you can add line breaks to your formulas using the “ALT+Enter” shortcut while authoring. We like to make use of this functionality and add an additonal indenting of white space, by hitting "spacebar" 5 times, to help make the formula easier to read when defining names.

 

Formula without line-breaks

=LET(Celsius, B2:B5, ConversionFactor, 9/5, FValue, (Celsius*ConversionFactor)+32, FValueRounded, ROUND(FValue,2), FValueRounded&"°F")

 

Formula with line-breaks and white spacing

=LET(CelsiusValue, B2:B5,

     ConversionFactor, 9/5,

     FValue, (CelsiusValue*ConversionFactor)+32,

     FValueRounded, ROUND(FValue, 2),

     FValueRounded&"°F"

)

 

Documenting your logic

The formula we have created does not benefit from any performance gains when using a LET. It does, however, gain improvements in documenting the intent of the formula and what each individual calculation does. This allows for easier debugging of more complex expressions and allows you to more easily document your work.

 

Consider the aforementioned formula with no LET

=ROUND((B2:B5*9/5)+32,2)&"°F"

 

While it is true that the above formula is shorter, it does not do a good job of conveying the intent of the calculation. With a LET, it is much easier to understand all the individual components and transformations that the value goes through as it gets converted. Simply read the formula, line by line.

 

=LET(CelsiusValue, B2:B5,

     ConversionFactor, 9/5,

     FValue, (CelsiusValue*ConversionFactor)+32,

     FValueRounded, ROUND(FValue, 2),

     FValueRounded&"°F"

)

 

Debugging and Updating Formulas

The last major benefit LET provides is with debugging and/or updating your formulas. Let’s take the previous example and imagine, while authoring the formula, we got to a state where we have the Fahrenheit value calculated and wanted to simply append "°F" to the end.

The formula might look something like this:

 

=LET(CelsiusValue, B2:B5,

     ConversionFactor, 9/5,

     FValue, (CelsiusValue*ConversionFactor)+32,

     FValueRounded, ROUND(FValue, 2),

     FValueRounded&"°F"

 

This formula will work fine but immediately we realize there are too many significant digits in our result:

 

Fahrenheit

40.424°F

40.082°F

39.92°F

40.478°F

 

At this point, all that’s needed to do is round the value and so we update the formula:

 

=LET(CelsiusValue, B2:B5,

     ConversionFactor, 9/5,

     FValue, ((CelsiusValue*ConversionFactor)+32)&"°F",

     ROUND(FValue, 2)

)

 

We then run into an issue because we are suddenly getting #VALUE! as a result…

That’s OK, though. With LET, we can more easily see where the error is occurring by testing different parts of the formula.

 

Breaking apart FValue, which seems to be the source of the error, we can return FValue and see what is getting fed into the ROUND function:

 

=LET(CelsiusValue, B2:B5,

     ConversionFactor, 9/5,

     FValue, ((CelsiusValue*ConversionFactor)+32)&"°F",

     result, ROUND(FValue, 2),

     FValue

)

 

This will of course return the previous results we saw before:

Fahrenheit

40.424°F

40.082°F

39.92°F

40.478°F

 

Depending on your formula authoring abilities and proficiency with debugging formula errors, the problem may jump out immediately to you: The issue is that we are returning a string and the ROUND function expects a number.

 

However, if it isn’t immediately obvious, you can further debug the statement by re-writing the LET to return the various components of FValue: CelsiusValue and ConversionFactor. These will both return numbers which means that the error lies in the expression defining FValue.

 

The offending line is clearly the appending of "°F": &"°F" and all that’s needed to do is append the value after rounding which will give us the final formula:

 

=LET(CelsiusValue, B2:B5,

     ConversionFactor, 9/5,

     FValue, ((CelsiusValue*ConversionFactor)+32),

     result, ROUND(FValue, 2)&"°F",

     result

)

 

While the above is valid, one last tip would be to simply add another name for the string conversion, in case you want to add an IF statement which returns both an unformatted and formatted value for Fahrenheit based on a condition in the future.

 

=LET(CelsiusValue, B2:B5,

     ConversionFactor, 9/5,

     FValue, ((CelsiusValue*ConversionFactor)+32),

     FValueRounded, ROUND(FValue, 2),

     FValueStringAppended, FValueRounded&"°F",

     FValueStringAppended

)

 

Learn More

To learn more about LET, please check out our help article and in the meantime we are excited to hear more from you about the ways you have used LET in your own workbooks!

LET Help

 

Availability Notes

LET is now available to Excel subscribers of Office 365 or Microsoft 365 in Production Current Channel.
To stay connected to Excel and its community, read the Excel blog posts and send us ideas and suggestions via UserVoice. You can also Excel on Facebook and Twitter

 

Chris Gross
Program Manager, Excel

12 Comments
Version history
Last update:
‎Nov 16 2020 02:32 PM
Updated by: