This article is contributed. See the original author and article here.

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 wrokbook 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


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 


 

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.

%d bloggers like this: