3. How much did it cost? How many people were affected? The first function: adding a series of cells with SUM
One of the most common stories you’ll want to tell with a spreadsheet is ‘How much did it cost?’ These are grand total stories, and involve one of the most basic calculations you will want to perform in your spreadsheet: adding up the values across a number of cells.
Now, you could do this with a very long formula like so:
=A1+A2+A3+A4+A5
…And so on. But that would be laborious. It is for this reason that spreadsheets have what are called functions.
Functions are words that have special meanings in Excel and other spreadsheet software. They are a shortcut for a series of instructions.
The function for adding up a series of cells, for example, is called SUM. All you need to do to use SUM is tell it what cells you want to add up, in parentheses after the function.
To add the values in cells A1 to A5 using that function, then, you might write the following:
=SUM(A1:A5)
It is easiest to think of functions as being like recipes: instead of having to write out ‘add this and then add that and then add that’ you can simply say: ‘use the CAKE recipe with these ingredients’, or ‘use the PIE recipe with these ingredients’.
Once you know that you can start to guess which sets of common instructions are going to be recipes - functions - in your spreadsheet software. Here are just a few instructions which have their own one-word functions:
- ‘Add all these figures up’
- ‘Calculate an average for these figures’
- ‘Tell me if a cell contains an error’
- ‘Count how many numbers I have in this column’
- ‘Extract the month from this date’
- ‘Replace the full stops in this cell with commas’
There are dozens and dozens more, too.
Once you know this, you can start to search for useful functions whenever you come across a task or problem which you think others may have already solved, adding the word “function”. For example, you might search Google for ‘Excel function extract day from date’ or ‘Google spreadsheets function count blank cells’.
If functions are recipes, arguments are the ingredients
You can’t cook a recipe without ingredients, and so (just as with our SUM example) every function is followed by at least one ingredient, in parentheses. These are called arguments.
In our first example above, SUM was the function and A1:A5 was the argument.
Sometimes you will get an error message because you didn’t ‘supply the right arguments’. For example, you may have submitted too many or too few (or none at all). Normally the error message will give you clues, and even a link to more information. And of course you can search for more information on that error. But knowing the jargon helps.
Back to our formula, then. You’ll notice that we specify a range of cells using the colon symbol:
=SUM(A1:A5)
So if we wanted to add all the cells from A1 to A500 we could adapt it like so:
=SUM(A1:A500)
You can also select cells across more than one column. If we wanted to add all the cells from A1 in the top left of our range to B500 as the bottom right (last) cell we could select them with this range:
=SUM(A1:B500)
Crucially, functions like SUM which work with numbers will ignore any words in the specified cell range. In other words, text does not cause it any problems.
For that reason, if all you want to do is add up all the numbers in a column you can often do so much more quickly by just using the column letters like so:
=SUM(A:A)
This simply means: use the SUM function on all the numbers in column A.
The same principle applies to rows:
=SUM(1:1)
Would mean add the values in all cells in row 1.
When there’s more than one ingredient: commas and semicolons
The SUM function is a simple recipe, and so only needs one ingredient. But other functions take more than one - as we’ll see in further chapters.
In these cases, each ingredient - argument - is separated by a comma (in English language versions of software) or semicolon (in Spanish, Portuguese, German and other language versions).
In fact, SUM is unusual in that it can take one or more than one argument: you can add more than one range of cells by simply adding a comma or semicolon between each one like so:
=SUM(A1:A5,A7:A9)
=SUM(A1:A5;A7:A9)
This adds together the values in cells A1 to A5, and from A7 to A9.
But before we come on to other functions with multiple ingredients, there are some other simple ones to explore.
Recap
- Functions are words that have special meanings in Excel and other spreadsheet software: a shortcut for a series of instructions.
- Functions are always followed by parentheses containing the ingredients it needs. These are called arguments (specifically) or parameters (in general).
- If a function uses more than one parameter, each one is separated by a comma or semi-colon, depending on the language of the software you’re using.
- You can search for useful functions whenever you come across a task or problem which you think the designers of the spreadsheet software may have already solved, adding the word “function”.
- Specify a range of cells by putting a colon between the first and last cell in that range, e.g.
A2:A300. - You can more quickly select a whole column or row by only using the letter or number of that column or row, e.g.
A:Aor1:1