Level 2 · Summarise the lighting range

Adding twenty prices with =D2+D3+D4+… works, but it is long, easy to break, and does not grow when rows are added. Functions do this work for you. A function has a name, then its inputs in brackets:

=SUM(D2:D21)

Ranges

D2:D21 is a range: every cell from D2 down to D21, written as the first cell, a colon and the last cell. A range can be a column (D2:D21), a row (B2:F2) or a block (B2:D21). Click a cell in the sheet and the formula bar shows which one you are in; selecting from D2 to D21 with Shift+click shows the range in the name box on the left.

The four everyday summaries

Function	What it returns
=SUM(D2:D21)	all the numbers added up
=AVERAGE(D2:D21)	the sum divided by how many numbers there are
=MIN(D2:D21)	the smallest number
=MAX(D2:D21)	the largest number

On the lighting range, MIN and MAX tell Jessica the price span at a glance: from 29.99 up to 409.00. The average sits far above the cheapest items, because a few expensive pieces pull it up. That is worth saying out loud in a pricing meeting: an average describes the range, not a typical product.

What functions skip

SUM, AVERAGE, MIN and MAX look only at numbers inside a range. Text and empty cells are ignored. That is convenient (the header unit_price in D1 would be skipped even if you included it) and also a trap:

• an empty cell is left out of AVERAGE, which is usually what you want: an unknown price is not a price of zero;
• a cell holding 0 is included, and drags the average down;
• a number stored as text (often from a messy export, shown left-aligned) is skipped by all four, so totals come out too low without any error.

When a total looks low, check that every value in the column is right-aligned. Numbers align right, text aligns left.

Typing functions

Function names are not case-sensitive: =sum(d2:d21) is accepted and Excel rewrites it as =SUM(D2:D21). While you type a formula, you can click a cell to insert its address instead of typing it.

Common mistakes

• Including the header or a total row in the range. A range like D2:D22 that also covers a total in row 22 counts that total twice.
• Stopping the range early. D2:D20 misses the last product. Check the last row number.
• Typed numbers. Copying the result of a calculator into G2 gives the right number today and a stale one next week. The checker asks for formulas.
• Writing =SUM(D2+D21). The plus adds just two cells. A range needs the colon.