Hi Reader,

Welcome to the Google Sheets Tips newsletter #298, your Monday morning espresso, in spreadsheet form! I hope you are doing well on this fabulous Monday morning.

As an adult, it's rare to experience something completely new in day-to-day life. Days follow a familiar pattern, even if the specifics are different.

Last week, however, the New York region was hit by a rare earthquake (thankfully, not too serious). I happened to be in Legoland New York with my family, in the gift shop, when it struck. It sounded like a passing freight train and Lego sets rocked back-and-forth on the shelves, but didn't fall off. I wasn't sure it was an earthquake until seeing confirmation in the news.

So I can now collect that badge: I-experienced-my-first-earthquake. Hopefully, my first and last.

Speaking of new experiences, today is the solar eclipse in North America. Although I don't live on the path of totality, I'm close enough to experience a significant darkening of the sky. I'll report back next week!

A big thank you to this week's sponsors who help keep this newsletter free to read:

_______

➜ Google Sheets Tip #298: The mighty (useful) LET Function

In this tip, we're looking at my current favorite function: the LET function.

The LET function lets us define named variables inside our functions. This has several huge benefits:

1. Makes our formulas easier to read
2. Makes our formulas more performant
3. Lets us add comments to our formulas

Let's start with a simple example before a real-world example.

Simple Example

Suppose we have a value in cell A2. Add this formula to the adjacent cell B2:

=LET( x , A2 , x+1 )

It adds 1 to the value in A2.

As first glance, we've added complexity for no apparent benefit. After all, we could have simply written:

=A2 + 1

But, this simple example illustrates how LET works.

Firstly, we create a named variable "x".

Then we add a definition for the variable x: the value from cell A2 (but it could be a more complex formula expression).

And finally, we write our main formula, but we use x in place of the cell references. I.e. we write x instead of A2.

A few things to note:

We can define multiple variables. We choose the variable names (use something descriptive and don't use existing keywords, e.g. don't call your variable SUM).

Let's see a real-world example:

Student Scores LET Formula Example

Consider this dataset of student test scores:

Suppose we want to calculate an average score for each student across the three tests, and then categorize them based on their average score.

There are three ways we could do this:

1. Helper Column: requires extra column in Sheet

The easiest way to solve this is to add the average calculation to column E with this formula:

=AVERAGE(B2:D2)

Then we can use the values in column E with an IFS function to categorize the data, in column F:

=IFS(E2>80,"Distinction",E2>60,"Good",E2>40,"Pass",TRUE,"Fail")

This is a perfectly valid way to solve this problem. The only downside is the extra column we have to add to our dataset.

2. Nested Formula: hard to read!

We can eliminate the helper column by nesting the AVERAGE clause inside the IFS, like so:

=IFS(AVERAGE (B2:D2)>80 , "Distinction" , AVERAGE(B2:D2)>60,"Good", AVERAGE(B2:D2)>40,"Pass", TRUE,"Fail")

However, notice the repetition.

It makes our formula hard to read and more likely to have mistakes (if we need to change the range B2:D2 we now need to do it in three places!).

Also, our formula potentially calculates the average three times instead of once, so this could affect the performance of our Sheet at bigger scales.

So, this is a subpar solution.

3. LET Formula: best of both worlds!

With LET, we can solve both problems. We create a formula that does not require a helper function or suffer from the repetition issue.

=LET( avScore , AVERAGE(B2:D2) , IFS( avScore>80 , "Distinction" , avScore>60 , "Good" , avScore>40 , "Pass" , TRUE,"Fail" ))

Looking back to the simple example, the first part of the LET construction is the variable name followed by the variable definition.

Here, we call the variable "avScore" and set it to be equal to the average of B2:D2.

(So now, if we need to ever change that range, we only need to do so once.)

This average value, stored inside the "avScore" variable, is now available to use elsewhere inside the LET formula.

We then add the IFS formula to finish the LET formula. We use the variable name "avScore" instead of cell references or nested formulas.

If you write complex nested formulas I encourage you to check out the LET function.

It's a powerful way to improve your formulas!

_______

If you enjoyed this newsletter, please forward it to a friend who might enjoy it.

Have a great week!

Cheers,
Ben

P.S. Map of AirBnB bookings for today's eclipse!​