Functions: SUM, AVERAGE, MIN, MAX, COUNT

You can add up a store's revenue for six months with plus signs, listing every cell:

=B2+B3+B4+B5+B6+B7

It works, but the formula grows with every row. Revenue for a year would take twelve addresses, and for five years, sixty.

For tasks like this, Excel has functions: ready-made actions with a name. The SUM function adds up all the numbers in a range, so the same six months of revenue take a shorter formula: =SUM(B2:B7).

Name and arguments

A function has a name and arguments: what it works with. Arguments go in parentheses after the name.

If there are several arguments, they're separated by commas.

You need several ranges when the numbers include something that shouldn't be added. For example, say the table already has a quarter total right below March. The formula =SUM(B2:B4,B6:B8) adds up the months and skips that row. Otherwise the quarter would be counted twice.

You don't have to type the whole function. When you type a name after =, Excel shows a list of matching functions, and you can pick the one you need from it. It's handy to select the range in parentheses with the mouse instead of typing it: type =SUM( and drag across the cells.

There's a button for the sum too: select the cell below a column of numbers, then on the Formulas tab click AutoSum and choose Sum. Excel types SUM by itself and picks the range above the cell, so all that's left is to press Enter. Try it on the sheet above: clear B8 and calculate the total with the button.

Make sure the total cell doesn't end up in its own range. The formula =SUM(B2:B8) in cell B8 tries to add itself, and Excel can't calculate it.

Five everyday functions

Besides SUM, the four functions you'll need most often are AVERAGE, MIN, MAX and COUNT. All five are written the same way: the name and a range in parentheses. Only the name changes, and it decides what the function takes from the same column:

A typical order: MEDIAN

One big number can easily throw off the average. An online store got seven orders this morning, and one of them is a wholesale order. AVERAGE in B10 came up with an average order of $8,629, even though only one order out of seven is over $3,000. The MEDIAN function shows a typical order more accurately:

The median is the number right in the middle when you line the orders up from smallest to largest: here it's $2,100, with three orders cheaper and three more expensive. For the median, the wholesale order is simply the most expensive one, and it doesn't matter whether it's $48,000 or a million. If there's an even number of numbers, the median is the average of the two numbers in the middle.

Not the largest, but the second: LARGE and RANK

MAX finds only first place. If the director asks for the top three sales, you need the LARGE function. It has two arguments: the range and the position by size. The formula =LARGE(B2:B7,2) returns the second-largest sale. SMALL works the same way, only it counts from the smallest number: with position 1, it returns the same as MIN.

The opposite question, what place each manager is in, is answered by RANK. Its arguments are a number and the range to compare it with. The largest number gets place 1.

Both formulas are written once and filled down, so the range in them is locked with dollar signs: $B$2:$B$7. An address like this is called an absolute reference, and it doesn't shift when you fill. The sales in B2 and the place number in A10 are written without dollar signs: they need to shift to the next row.

=LARGE($B$2:$B$7,A10)

=RANK(B2,$B$2:$B$7)

Functions skip text

Functions count only numbers and skip text in a range. So a number stored as text quietly drops out of the total, and the sum comes out smaller than the real one.

COUNT helps catch such a cell: if there are six months but COUNT returned 5, one revenue figure is stored as text. Find it and type the number in again:

COUNT counts only numbers. To count all filled cells, including those with text, you need the COUNTA function. For example, how many customers left a review: COUNT on the review column would return 0, since there are no numbers in it.