Excel LET Function

Photo by Unsplash

Released for Office 365 recently, the new LET function brings clarity and transparency to spreadsheets by assigning names to the results of calculations.

Usage

The use of named ranges in Excel is a good way to help other users understand the workbook. When setting up named ranges, Excel makes the name available for use in the entire workbook by default. However, by restricting the scope to a single worksheet, one can use the same name repeatedly in the same workbook.

It is important to be aware of the scope of a name’s availability.

Now with the introduction of LET, it is possible to further restrict the scope of a name to a single cell.

Arguments

LET(name₁, value₁, [name₂], [value₂], … [name_n],[value_n], calculation)
name₁ = The name of the first value.
value₁ = Value assigned to the first name.
name₂, value₂ … name_n, value_n = Optional additional sets of names with values.
calculation = Calculation that uses names defined by previous arguments.

One can provide multiple name/value sets before using these in the final calculation. These sets can perform calculations using names defined earlier.

Example 1

In the example in the Amort worksheet of the LET demo workbook, the objective is to calculate the annual straight-line amortization cost of some assets, given cost, useful life and salvage value.

The calculation would be simple enough with =(B6-B8)/B7.

But setting out the calculation in the LET function adds clarity and reduces the chance of a calculation error.

=LET(cost,B6,life,B7,salvage,B8,(cost-salvage)/life)

Example 2

Another common situation arises when working with the IF function. A calculation is performed once to determine whether a condition is met, then carried out a second time for the result.

In the example in the BudgetChange worksheet, operating expense budgets for departments of the University of Manitoba are compared and areas with greater than a 5% budget increase are highlighted.

A way to accomplish this is with the formula below.

=IF((B6-C6)/C6>0.05,(B6-C6)/C6,"")

However, LET provides a more readable, albeit longer, formula.

=LET(CurrentBudget,B6,PriorBudget,C6,PercentChange,(CurrentBudget-PriorBudget)/PriorBudget,IF(PercentChange>0.05,PercentChange,""))

To improve readability, add linefeeds after each set of name and value. When entering the formula, press <Alt><Enter> after each set.

=LET(CurrentBudget,B6,
PriorBudget,C6,
PercentChange,(CurrentBudget-PriorBudget)/PriorBudget,
IF(PercentChange>0.05,PercentChange,""))

In the AverageChange worksheet, the percent change is returned for all departments. The data has now been set up in a table to provide additional clarity, so the formula in the Average Change column uses column names and returns an array.

=LET(CurrentBudget,Budget[Budget 2021],
PriorBudget,Budget[Budget 2020],
DollarChange,CurrentBudget-PriorBudget,
PercentChange,DollarChange/PriorBudget,
PercentChange)

At this point, the formula is practically written in English and contains no cell references that are not named.

Recommendation

Any time a longer formula contains more than one instance of the same calculation, consider using the LET function to improve readability and performance of the formula.

Download the LET demo workbook.

Here is the links to the Microsoft documentation for the LET function.

by Pat Martens

Seeds of Hope for 2021

Photo by Majharul Islam on Unsplash

Out with the Old

Many people are more than a little eager to leave the chaos of 2020, leaving the hardships of a global pandemic behind. The start of a new year is traditionally a time to ponder New Year’s resolutions and fresh starts, a time of new inspiration and fresh energy to re-focus on life. But wait…before throwing out the whole 2020 experience, perhaps ask this simple question: Are there good things that have emerged that should be carried forward into 2021?

When left-over carrot tops and plant stems are put on the compost pile in the garden to decay, they change into good soil that nourishes plants for the future year. In the same way, the beautiful thing about our “normal” way of life being destroyed is that change is an opportunity to determine what is worth keeping and examine new seeds that are sprouting!

Community Spirit

Crisis often pulls people and communities together. Not surprising then that 2020 saw some wonderful examples of community spirit, kindness, and generosity. There was a renewed appreciation for family and friends along with new and creative ways of connecting with them. Whether through Zoom calls, taking a walk outside together, or checking in to see if there were needs during a time of sickness, we created a community spirit. There was great applause for essential and health care workers. New appreciation was developed for schools and teachers, and homeschooling was recognized for the difficult thing that it is. Local small businesses found new support from the community as well.

With that community spirit was also an increase in awareness and compassion for social inequalities. We trust that our eyes were opened to the homeless and low-income families without computer or internet access. They are definitely in need of more support.

Embracing Technology

Restrictions required new ways of doing things. Working from home, eCommerce, electronic signatures on contracts, online shopping with contactless pickup, are but a few examples of new ways of accomplishing things. Technology enabled virtual tourism. Some examples are a virtual tour of the Vatican in Rome, The Louvre in Paris, Guggenheim Museum in New York, The Kennedy Center of the Performing Arts and Smithsonian National Museum of Natural History in Washington, and many more!

Healthier Planet

The restrictions of the pandemic also brought about some surprising but healthy outcomes for our planet. Reduced travel resulted in a noticeable reduction in carbon emissions and air pollution. Personal hygiene and cleanliness became even more important to prevent the spread of Covid-19.

Presenteeism is the expectation that one must report to the office even when sick. This pre-Covid social expectation has been drastically reversed. Now those even feeling mildly ill are expected to stay home or work remotely, if possible. This indicates an underlying shift in how our culture thinks about work. It is more important that the tasks are completed than the time spent or location. The freedom to stay home when not feeling well can only be a positive step toward better physical and mental health for employees.

The renewed emphasis on getting out and being active resulted in bike shops often being unable to keep up with demand. This is an encouraging change for a culture that has become increasingly sedentary.

While the isolation and pandemic restrictions may have caused mental stress, there was also an increased awareness of the need for mental health support and the effects of such stress on individuals. Even the increase in pet adoption and planting gardens were signs of people finding healthy coping strategies during their restlessness.

A significant drop in the number of premature births was observed worldwide– some places reporting a staggering 80% decline. While there may have been multiple contributing factors, the increased hygiene measures and behavior changes that resulted in less physically demanding work, less shift work, more sleep, and increased support may be a partial explanation.

Creativity

While some activities, such as Drive-in’s, puzzles and board games made a come-back, there were also new bursts of creativity. With many running races and marathons cancelled, new ways to host community activities emerged, such as virtual races and local runs that would not have ordinarily garnered such a keen interest. One such example is Villageruns.com where routes in surrounding communities were posted in an online platform. Participants were encouraged to run the routes to suit their own schedules. Those who completed the runs still received the traditional finisher shirt; it met both motivational and physical distancing needs.

Fundraisers and concerts went Virtual. Local fundraising dinners became a curbside pickup meal and still provided the opportunity to support local charities. Churches found ways to livestream services and some even offered choir participation from the safety of home. The Winnipeg Symphony Orchestra and other local musicians such as Steve Bell, offered online concert tickets and access to a live stream music event.

New markets for personal protective equipment and cleaning supplies created opportunities for business. New jobs opened up for delivery or cleaning staff. For example: a craft store that would otherwise be required to shut down as they were not selling essentials, now offered local delivery of supplies. Even advertising has been affected, such as Apple’s “Creativity goes on” ad which is comforting and positive, and comics have sought to capture the witty side of our lockdown lives!

Embrace the New

Sift through the composting remains of 2020. Discard the negativity and leave behind the cynicism. Remember to wash your hands often. Stay home when you are sick. Breathe, and allow the seeds of community spirit, kindness, and generosity to sprout. Let’s continue harnessing technology to meet human need and make decisions that are healthier for us and our planet. Embrace the new growth of creativity and allow the resulting hope to grow in 2021!