In the world of data analysis and spreadsheet management, the ability to simplify and streamline complex formulas is invaluable. This is where the LET function in Google Sheets comes into play. While many may associate LET with Microsoft Excel, it’s a concept that has been prevalent in programming languages like Python and BASIC for quite some time. Essentially, the LET function allows you to assign values to variables and use these variables within your formulas, making them more readable and manageable. For example, if you’re performing time series analysis, you might assign values to the variable year with a simple statement like let year = 2024.

In this blog post, we will explore how the LET function can be utilised in Google Sheets to simplify complex calculations. We’ll start with a brief definition and syntax overview, followed by practical examples that demonstrate the power and flexibility of LET. Whether you’re tired of repeatedly setting up the same formula or looking to enhance your spreadsheet’s efficiency, the LET function is a game-changer.

LET is not a Microsoft Excel unique function. Other languages, e.g. Python, BASIC, etc, have been using the LET function for quite some time. More specifically, let is a statement that allows you to assign the value of an expression to a variable. If I wanted to do some time series analysis, I could assign some values to the variable year; let year = 2024.

Definition

Now, let’s look at it from a Google Sheets/Excel context. If we pull up the Google documentation for the function:

LET(name1, value_expression1, [name2, …], [value_expression2, …], formula_expression)

https://docs.google.com/spreadsheets/d/1ocyAbAh7L55OQWU58hCwhgz1LyezGU8gfY6Fi2cck6o/edit#gid=0

Problem

LET allows us to create a bunch of different variables then we can provide an expression for those variables to be processed. Let’s say that I’m sick of having to set up the same formula for a calculation, I can use the LET function to create this formula for me. I want to have two variables, x and y, and I want to perform two things:

1. Find the sum of 5 numbers (A2:A6) then times that sum by itself, and
2. Find the average of the same 5 numbers (A2:A6) then times that average by itself.

I create break this down into parts:

1. Variable 1 name is x,
2. Variable 1 expression is the sum of those numbers (SUM(A2:A6)),
3. Variable 2 name is y,
4. Variable 2 expression is the average of those numbers (AVERAGE(A2:A6)), and
5. The overall expression is x times itself added to the y times itself (x*x + y*y).

Let’s input these parts into the LET function:

=LET(x, SUM(A2:A6), y, AVERAGE(A2:A6), x*x + y*y)

Now, to implement that into the sheet:

This means we can also update any of those values and it will automatically update the LET function in B2:

Example 1 – Discounts

Suppose you have a formula that calculates the total price with tax and discount:

= (B11 * B12) * (1 + B13) – B14

Where:

• B11 is the quantity.
• B12 is the unit price.
• B13 is the tax rate.
• B14 is the discount

We can now use the LET function to turn our thoughts of approaching each step into a process that it solves for us:

=LET(quantity, B11, unit_price, B12, tax_rate, B13, discount, B14, total_price, quantity * unit_price, total_price_with_tax, total_price * (1 + tax_rate), final_price, total_price_with_tax – discount, final_price)

I’m going to separate this out a little bit so it’s easier to read:

=LET(

Here are our variables:

quantity, B11,

unit_price, B12,

tax_rate, B13,

discount, B14,

Here are out expressions (calculable steps):

total_price, quantity * unit_price,

total_price_with_tax, total_price * (1 + tax_rate),

final_price, total_price_with_tax – discount,

Here is out output expression:

final_price)

So, this is first finding the total price (B11*B12), then using ‘total_price’ and tax_rate, finding the total price with tax ((B11*B12)*(1+B13)), then calculating the final price (total_price_with_tax – discount (B14)), and outputting it as the solution.

Let’s see what that looks like in the sheet:

And like the previous problem, these values are dynamically updatable:

Example 2 – Calculating Compound Interest

If you’ve seen this topic before, you might be familiar with the formula:

FV=P×(1+r/n)^(n×t) 

Where:

• FV is the future value of the investment,
• P is the principal investment amount (initial deposit),
• r is the annual interest rate (decimal),
• n is the number of times interest is compounded per year, and
• t is the time the money is invested for in years.

If we look at the formula in Google Sheets, it would be a bit confronting to those who aren’t familiar with it:

=B18*(1+B19/B20)^(B20*B21)

If we implement it into a LET, this provides space for more understanding within the formula:

=LET(

principal, B18,

annual_rate, B19,

compounding_periods, B20,

years, B21,

rate_per_period, annual_rate / compounding_periods,

total_periods, compounding_periods * years,

future_value, principal * (1 + rate_per_period) ^ total_periods,

future_value)

We can also see, when comparing the two methods, that we have the same result: 180.872595.

Example 3 – Solving Quadratic Equations

With this portion, we’re actually stepping back over a problem that we’ve solved before in previous posts: post 1 and post 2.

We’re taking this quadratic equation, in the general form:

ax² + bx + c = 0

The solutions (roots) of this equation are given by the quadratic formula:

x = (−b +/- sqrt(b²−4ac))/2a

Let’s assume the following variables in Sheets:

• B27 contains the a variable,
• B28 contains the b variable, and
• B29 contains the c variable.

Next thing we want to do is calculate the roots. First, let’s calculate the discriminant:

D = b² – 4ac

To calculate the two roots, the following two questions need to be solved:

x₁ = -b-sqrt(D))/2a

x₂ = -b+sqrt(D))/2a

In order to formulate this in Google Sheets, I’m going to step through each portion one-by-one, implementing a new step as we go. So, first, let’s set up our variables:

=LET(

a, B27,

b, B28,

c, B29,

)

Now let’s add the first root solution (discriminant and denominator):

=LET(

a, B27,

b, B28,

c, B29,

discriminant, b^2-4*a*c,

denominator, 2*a,

root1, (-b – SQRT(discriminant)) / denominator,

root1

)

Now let’s add the second root solution:

=LET(

a, B27,

b, B28,

c, B29,

discriminant, b^2-4*a*c,

denominator, 2*a,

root1, (-b – SQRT(discriminant)) / denominator,

root2, (-b + SQRT(discriminant)) / denominator,

)

Now to deal with the output of this formula. If we want to calculate both roots and output them in two separate cells (horizontally), we can set them inside the array symbols, separated by a comma — {root1, root2}:

=LET(

a, B27,

b, B28,

c, B29,

discriminant, b^2 – 4 * a * c,

denominator, 2 * a,

root1, (-b – SQRT(discriminant)) / denominator,

root2, (-b + SQRT(discriminant)) / denominator,

{root1, root2}

)

If you would like the output to be vertical, This formula will output both roots in an array, displaying them horizontally. If you want to display them vertically, we can set them inside the array symbols, separated by a semicolon:

The LET function in Google Sheets is a powerful tool for anyone looking to streamline and simplify complex calculations. By allowing the creation of variables and expressions within a single formula, LET enhances readability, reduces redundancy, and improves performance. From calculating the total price with tax and discounts to solving quadratic equations and computing compound interest, the LET function proves to be an indispensable asset in a variety of scenarios. Embracing this function not only makes your formulas easier to understand but also opens up new possibilities for efficient data management and analysis. As we’ve seen through our examples, the LET function transforms daunting and convoluted calculations into clear, manageable steps, making it an essential feature for any serious Google Sheets user.

Have you created LET functions before? Have you ever tried it? Give it a go and let me know about your progress in the comments below!