Day 2 — SUM, AVERAGE, COUNT and friends

The six functions that do most of the work in most real spreadsheets.

Ranges — a block of cells

A colon means "everything from here to there". F2:F16 is the fifteen amount cells.

Every formula on this page runs against the Sales sheet below. The row numbers and column letters are real — F2 really is 165000.

SUM

=SUM(range)

Total sales are 1,068,000 across 88 units. You can pass a range, or individual cells separated by commas, or both.

AVERAGE, MAX, MIN

The average order is 71,200. The biggest is 220,000 and the smallest 7,200 — a spread of 212,800, which tells you the average alone is not describing this data very well.

COUNT versus COUNTA — the one people get wrong

COUNT counts cells containing numbers. COUNTA counts cells that are not empty, whatever is in them.

Look at rows 2 and 3 of that result =COUNT(F1:F16) still gives 15, even though the range covers 16 cells. F1 holds the word "Amount", which is text, so COUNT ignores it. =COUNTA(F1:F16) gives 16 because it counts the header too.

This is why a count can come out one short and you cannot see why: your range included a header, and COUNT quietly skipped it.

Text inside a range is ignored, not an error

Both SUM formulas give 15120, because SUM skips the text header rather than failing. Convenient — and dangerous, because a column of numbers stored as text will silently sum to zero.

ROUND

=ROUND(number, decimal places)

A negative number of places rounds to the left of the decimal point: -2 rounds to the nearest hundred.

ROUND changes the value, formatting does not Setting a cell to show two decimals only changes what you see — the stored number keeps all its digits, and totals will look like they are out by a rupee. ROUND changes the number itself. For anything that must add up exactly, use ROUND.

ROUNDUP and ROUNDDOWN

ROUND goes to the nearest, with halves going away from zero. ROUNDUP and ROUNDDOWN ignore the nearest and always go one way — useful for boxes, licences and anything you cannot buy a fraction of.

Nesting functions

A function can go inside another function. Excel works from the inside out.

The first two are the same calculation written two ways, and both give 71200. The third gives the average revenue per unit sold, which is a different and often more useful number: 12136.36.

MEDIAN — when the average lies

The average is 71,200 but the median is 54,000. Half the orders are below 54,000; a few very large laptop orders drag the average up. If someone asks for "the typical order", the median is usually the honest answer.

Try these yourself

Total the quantity column, then total only the first five orders.
Find the largest and smallest quantity in E2:E16.
Work out the average amount rounded to zero decimals.
Count how many cells in C1:C16 are not empty, then explain why it is 16 and not 15.
Find the second largest amount using LARGE.

← PreviousDay 1 — Formulas and cell references Next →Day 3 — IF and logical tests