Do you want to streamline your Google Sheets formulas and improve their performance? Look no further – the LET function in Google Sheets is here to save the day! With the LET function, you can assign names to expressions to make your formulas more readable and efficient. In this article, we’ll explore the power of the LET function and how it can level up your Google Sheets game.
What is the LET Function?
The LET function allows you to assign names to expressions in your Google Sheets formulas. Instead of writing out the expression multiple times, you can simply use the assigned name. Not only does this improve the readability of your formulas, but it also enhances performance.
Improved Readability
Let’s say you have a formula that calculates the total sales in a month. Without the LET function, your formula might look something like this:
=sum(C:C)Now, let’s say you want to test whether the total sales meet a target. You might end up with a formula like this:
=if(sum(C:C)<100,"d",if(sum(C:C)<200,"c",if(sum(C:C)<300,"b","a")))With the LET function, you can simplify this formula by assigning a name to the total sales expression. Let’s call it “x”. Here’s how the formula looks with the LET function:
=let(x,sum(C:C),if(x<100,"d",if(x<200,"c",if(x<300,"b","a"))))By using the LET function, you only need to write the total sales formula once and replace all other occurrences with the assigned name. This not only makes your formula more concise but also easier to understand for yourself and others.
Performance Improvement
When you use the same expression multiple times in a formula, Google Sheets will calculate that result multiple times. This can negatively impact performance, especially with complex formulas. However, with the LET function, you can avoid this issue.
For example, let’s say you have a complex formula that uses the same expression multiple times. Without the LET function, Google Sheets would calculate the expression for each occurrence. But with the LET function, you can assign the expression to a name and reuse it throughout the formula. This reduces the number of calculations performed and improves the performance of your sheet.
Syntax of the LET Function
The syntax of the LET function in Google Sheets is as follows:
LET(name1, value_expression1, [name2, …], [value_expression2, …], formula_expression)Let’s break down the arguments:
name1,[name2, …]: The names to assign to thevalue_expression1,[value_expression2, …].value_expression1,[value_expression2, …]: The assigned values toname1,[name2, …].formula_expression: A calculation that usesname1,[name2, …]within the LET function.
It’s important to note that all the arguments in square brackets are optional. If you specify name2, you must also specify value_expression2, but not necessarily within the formula_expression.
Here are a few examples to illustrate the usage of the LET function in Google Sheets.
Examples of the LET Function
Basic Examples
Let’s start with some basic examples to get you familiar with the LET function. In the first example, we’ll use a single name and a value expression:
=let(a,5,a*2)Result: 10
In this LET formula, a is the name1, 5 is the value_expression1, and a*2 is the formula_expression.
Here’s an example with two names and two value expressions:
=let(a,5,b,2,a*b)Result: 10
Real-life Use of the LET Function
Now that you understand the basics, let’s see how the LET function can be used in real-life scenarios. Imagine you want to sum a numeric column based on a criterion. One of the functions you can rely on is the SUMIF function. However, there are a couple of issues with using SUMIF alone.
First, the formula isn’t reader-friendly and can be challenging to understand at a glance. Second, Google Sheets evaluates the monthly sum (SUMIF) twice, affecting performance.
Let’s take a look at how the LET function can solve these problems. Suppose you want to sum the sales amount (C2:C7) in January 2023 (B2:B7). You might use the following SUMIF formula:
=ArrayFormula(sumif(eomonth(B2:B7,-1)+1,date(2023,1,1),C2:C7))Now, let’s say you want to test the result and return “poor” if it’s less than 10, “good” if it’s between 10 and 40, and “exceptional” if it’s greater than 40. Without the LET function, your formula might look like this:
=if(ArrayFormula(sumif(eomonth(B2:B7,-1)+1,date(2023,1,1),C2:C7))<10,"poor",if(ArrayFormula(sumif(eomonth(B2:B7,-1)+1,date(2023,1,1),C2:C7))<40,"good","exceptional"))As you can see, this formula is not very readable, and the monthly sum (SUMIF) is calculated twice. Here’s where the LET function comes to the rescue:
=let(x,ArrayFormula(sumif(eomonth(B2:B7,-1)+1,date(2023,1,1),C2:C7)),if(x<10,"poor",if(x<40,"good","exceptional")))In this LET formula, x is the name_1, ArrayFormula(sumif(eomonth(B2:B7,-1)+1,date(2023,1,1),C2:C7)) is the value_expression1, and if(x<10,"poor",if(x<40,"good","exceptional")) is the formula_expression. By using the LET function, the formula becomes much more readable, and the monthly sum is calculated only once, improving performance.
LET and LAMBDA: Similar but Different
Although you can replace the LET formula with a LAMBDA, I don’t recommend it because they serve different purposes.
The LAMBDA function in Google Sheets has helper functions like BYROW, BYCOL, SCAN, REDUCE, MAP, and MAKEARRAY, which perform advanced array operations. Additionally, LAMBDA is useful when you want to test a custom-named function within a sheet.
In contrast, the LET function focuses on assigning names to expressions to improve formula readability and performance.
Conclusion
By harnessing the power of the LET function, you can transform your Google Sheets formulas into svelte, efficient powerhouses. Say goodbye to long, convoluted formulas and hello to streamlined, reader-friendly expressions. Start using the LET function today and unlock the full potential of Google Sheets!
To learn more about optimizing your Google Sheets and other SEO strategies, visit Crawlan.com. Happy Sheets-ing!
