Array Functions
Advanced

LET to name a value used twice

Calculate the average once, then use it by name.

Task:

Flag how far each branch is from the network average, as a share of that average. In C2:C7 use LET to name the average of B2:B7, then return (branch − average) ÷ average.

Interactive Spreadsheet

Read about LET

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

Variations of LET

0/3

The same formula in the other shapes it takes at work.

  1. Name a value so the formula reads like a sentence

    LET binds a name once and reuses it.

    Core
    Advanced
  2. LET with two names, one built from the other

    A later name can use an earlier one.

    Intermediate
  3. LET to name a value used twice

    Calculate the average once, then use it by name.

    Advanced

The data in this exercise

This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.

7 rows × 3 columns6 cells you fill in
ABC
1BranchSalesVs average
2Aldgate52000
3Bow47000
4Camden61000
5Dalston38000
6Ealing55000
7Fulham47000
What this exercise teachesMay contain the answer

Writing AVERAGE twice is not just longer: change the range in one copy and forget the other and the formula silently compares against two different averages. LET calculates it once and every use refers to the same value.