Spreadsheets that Calculate
Spreadsheet functions
Work out what SUM, AVERAGE, MAX, MIN, COUNT and IF show for a sheet you are given.
A function is a calculation somebody has already written, given a name and left in the spreadsheet for you to use. You hand it a range of cells and it hands back a single value. SUM adds them up, AVERAGE shares the total out between them, MAX and MIN find the largest and the smallest, and COUNT tells you how many cells in the range are holding a number at all. IF is the odd one out and the interesting one: instead of a range, you give it a question and two answers, and it hands back whichever of the two the question deserves.
One value out, however much went in
Whatever a function looked at, what appears in the cell is one value and nothing else. That is worth saying because it is what makes functions worth stacking: since the result is just a value, you can subtract one function from another, add a number to one, or hand one straight to another, and the sheet will work it out in the ordinary way. It is also why a formula never shows its own working. The cell holds a result, and the recipe is only visible when you go and look at it.
Worked example
A sheet holds X1 = 20. What does =IF(X1 < 20, "Low", "High") show?
Everything hangs on the question inside the brackets, so settle that on its own first: is 20 less than 20?
An IF is really two things — a question and a choice — and doing them at the same time is what makes them go wrong.
Try it together
Now work through a sheet with a gap in it. Y1 = 12, Y3 = 6 and Y4 = 6, and Y2 has been left empty.
Deal with what is actually in the range before working anything out.
1.How many of the four cells are holding a number?
Have a go
Have a go on your own. A sheet holds Z1 = 9, Z2 = 15 and Z3 = 9. What does =MAX(Z1:Z3) - MIN(Z1:Z3) show?
Ready to practice?
Eight questions on what you have just read. Nothing is timed, and you can play as many times as you like.