Spreadsheet Practice

Lesson 21 of 29, Dynamic arrays

LET

Name the pieces of a long formula.

Pro8 minutes, 2 exercisesLET

Watch it done

2 steps

Watch the technique worked out on sample data, then try it yourself in the exercises.

How it works

=LET(name1, value1, [name2, value2, …], result) assigns names inside a formula. Each name is calculated once and can use the names before it.

Long formulas become readable and faster to debug, and you stop repeating the same sub-calculation three times.

Syntax

=LET(subtotal,B2*C2,subtotal*1.08)One name
=LET(subtotal,B2*C2,discount,IF(subtotal>=500,subtotal*0.1,0),subtotal-discount)Names that build on each other

Watch out for

  • Names can't look like cell references: "tax1" is cell TAX1. Use names like taxRate.
  • The last argument is always the result.

The exercises

  1. Discounted totals. In D2, use LET: subtotal = qty × price; if subtotal is $500 or more, take 10% off. Return the discounted total. Fill down to D6.
  2. Quiz status. In E2, use LET to name the average of the three quizzes, then return Honors (85+), Pass (70–84.99) or Retake. Fill down to E6.

Graded practice for this lesson is part of Pro

The walkthrough and notes are open to everyone. Pro unlocks the exercises here and in the other 27 Pro lessons, Excel and Google Sheets, 79 exercises in all.

  1. Discounted totals
  2. Quiz status
Try these functions in the Formula Lab