Your first functions: SUM, AVERAGE, MIN, MAX and COUNT

The idea

At the end of every week, the café owner wants to know a few simple things. How many cups of coffee did we sell in total? What's a normal day? What was the slowest day, and the busiest? With what you know so far, you could answer the first one with =B2+B3+B4+B5+B6. That works for five days, but imagine a whole year of daily sales. Typing 365 cell addresses joined by plus signs is miserable, and if you miss one, nothing tells you.

That's why Excel has functions. A function is a ready-made calculation with a name. You hand it some cells, it does the work, and it hands back an answer. =SUM(B2:B366) adds up a whole year in a dozen characters, and there's no way to accidentally skip a day in the middle. Excel has hundreds of functions, but a handful of them do most of the everyday work, and those are what this module covers.

Function syntax

Every function, no matter how simple or complicated, is written in the same shape. Once you can read this one, you can read all of them:

# Total Kopi Susu cups sold this week
G1: =SUM(B2:B6)

G1: 173

• = starts the formula, as always.
• SUM is the function's name.
• Inside the parentheses are its arguments, separated by commas. Here there's one: the range B2:B6.

This module's sheet is one week of the café's sales, with the number of cups sold per product each day. It has a couple of gaps, because the café ran out of tea one day and out of bread another, and there's a Note column for anything unusual:

You don't need to memorise exact function names. In Excel, start typing =SU and a list of matching functions pops up underneath; press Tab to pick one. If you don't know the name at all, the Insert Function button (fx, next to the formula bar) opens a searchable list. When you pick a function there, Excel shows a small form that explains what each argument is for.

SUM, AVERAGE, MIN, MAX

These four are the ones you'll reach for most. Between them they answer the owner's questions: how much in total, what's typical, what's the least, and what's the most:

# Total Kopi Susu cups sold this week
G1: =SUM(B2:B6)
# Average Kopi Susu cups per day
G2: =AVERAGE(B2:B6)
# The slowest Kopi Susu day
G3: =MIN(B2:B6)
# The busiest Kopi Susu day
G4: =MAX(B2:B6)

G1: 173
G2: 34.6
G3: 28
G4: 40

Over the week the café sold 173 cups of Kopi Susu, which works out to 34.6 a day on average. The slowest day had 28, and the busiest had 40. Knowing both the average and the extremes is useful: if the average is 34.6 but one day had 40, the owner might want to find out what happened that day.

A function isn't limited to one range. You can give it several ranges, single cells or plain numbers, separated by commas, and mix them however you like:

# Kopi Susu and Roti Bakar together: two ranges in one SUM
H1: =SUM(B2:B6, D2:D6)
# Every cup of every product: one rectangle
H2: =SUM(B2:D6)
# The largest of three cells and the number 20
H3: =MAX(B2, C2, D2, 20)

H1: 219
H2: 305
H3: 32

The first adds two columns that aren't next to each other, so they're listed separately. The second covers the whole rectangle from B2 to D6, all three products at once. The third shows that you can mix cells and numbers: it returns whichever is biggest of Kopi Susu, Teh Tarik and Roti Bakar on the first day, or 20.

Empty cells and text

Remember the day the café ran out of tea? That cell is empty, and it's worth knowing what functions do with it. Functions that do maths on ranges skip empty cells and text completely, as if they weren't there. For SUM that makes no difference, but for AVERAGE it changes the answer:

# Teh Tarik's average, skipping the empty day
I1: =AVERAGE(C2:C6)
# Dividing by 5 days instead counts the empty day as 0
I2: =SUM(C2:C6)/5

I1: 21.5
I2: 17.2

The tea column has four numbers and one empty cell. AVERAGE divides by 4, the number of values it found. Dividing by 5 treats the empty day as zero cups. Which one is right depends on what the gap means: "no data" (use AVERAGE) or "we sold nothing" (type a 0 in the cell, and AVERAGE will count it).

COUNT, COUNTA, COUNTBLANK

Sometimes you don't want to add numbers up, you just want to know how many there are. How many days did we sell tea? How many days have a note? How many cells are missing? Excel has three counting functions, and the difference between them is what they count:

# How many days have a Teh Tarik number
J1: =COUNT(C2:C6)
# How many days have a note (text counts too)
J2: =COUNTA(E2:E6)
# How many sales cells are empty
J3: =COUNTBLANK(B2:D6)
# How many days are in the table
J4: =COUNTA(A2:A6)

J1: 4
J2: 2
J3: 2
J4: 5

COUNTA(E2:E6) counts the days that have a note. COUNTBLANK finds the two gaps in the data. COUNTA(A2:A6) counts the days in the table, a handy denominator: "average cups per day" is =SUM(B2:D6)/COUNTA(A2:A6).

Functions inside formulas

Once a function has worked out its answer, that answer is just a number. So you can use a function anywhere you'd use a number: add to it, divide by it, or even give it to another function. This lets you answer more interesting questions than "what's the total":

# The gap between the best and worst Kopi Susu day
K1: =MAX(B2:B6)-MIN(B2:B6)
# Kopi Susu's share of all cups sold
K2: =SUM(B2:B6)/SUM(B2:D6)
# The midpoint between the best and worst day
K3: =AVERAGE(MAX(B2:B6), MIN(B2:B6))

K1: 12
K2: 0.5672131148
K3: 34

The first is the range of Kopi Susu sales, the gap between the best and worst day. The second is coffee's share of all cups (57%). The third nests one function inside another: the arguments of AVERAGE are themselves function calls. Excel works from the inside out, the same way it handles parentheses.

The status bar

Sometimes you just want a quick look at a number without writing a formula. Select a range in Excel and look at the bottom-right corner of the window: the status bar shows the Average, Count and Sum of what you selected. Right-click the status bar to also show Min, Max and Numerical Count. It's perfect for checking something on the spot, like "roughly how much did we sell this week?" But it disappears as soon as you click somewhere else, so when a number belongs in a report, write the formula.

Recap

• A function is =NAME(argument, argument, ...); arguments can be ranges, cells or numbers.
• SUM, AVERAGE, MIN, MAX summarise numbers.
• Maths functions skip empty cells and text in ranges, so AVERAGE divides by the count of numbers.
• COUNT counts numbers, COUNTA counts non-empty cells, COUNTBLANK counts empty ones.
• AutoSum (Alt + =) and the status bar save time; check what they picked.