Since the main purpose of a spreadsheet is to perform calculations it is appropriate that we consider just how this is done.
In general, a cell of a spreadsheet can contain one of three things:
- A number
- A formula
All calculations are done using formulas. A formula occurs whenever a cell has contents that begin with an equals sign, which is the signal to Calc that it needs to perform a calculation. For instance, if a cell contains “A3+B3″, Calc would examine this, see the letters and the plus sign, and decide that the contents of the cell were a text string. After all, it cannot be a pure number with those other things there. But place an equals sign in front, so that the contents now read “=A3+B3″ and Calc knows that this is formula, and will perform the calculation. And one of the best ways to interact with a cell that contains a formula is to use the Formula Bar, which normally appears just above the cells of the spreadsheet proper:
This place is the best place to work with Formulas, and has several features worth mentioning. First, there is what is called a Name Box, which displays the cell address of the cell you are editing. But it also can move you to a different cell. Just type in a cell address (e.g. C12), press the Enter key, and you will move to that cell and can start editing. Next to this is an icon for the script “f” and a magic wand image. This is the Function Wizard. Since Functions will be worth many tutorials in themselves we won’t spend any time on this right now other than to note this is where you access all of the built-in mathematical functions that Calc offers. BTW, the script “f” is the mathematical symbol for function, hence the icon.
The next Icon is a Greek upper-case sigma, which is the mathematical symbol for a _sum_. One of the things you do often in a spreadsheet is add up bunch of numbers. If you had a column of numbers, say in Column B, that were in cells B2 through B13 inclusive, you could then go to cell B14, or even lower (e.g. B15), click on the Sum symbol, and Calc would guess that what you wanted to do was add up those numbers and place the total in the cell. I like to skip a line when getting sums because it makes the spreadsheet easier to read, and Calc takes this in stride.
When you do this the Formula bar will now show you what is in cell B15 (as I did it) and it will read:
Well, actually it will probably read =SUM(B2:B14) because LibreOffice assumes I want to sum everything. Since cell B14 is empty, it won’t matter. I will adjust my formula to just go to cell B13, though, just to prevent any future problems.
So, what is this formula telling us. First, it begins with an equals sign, which is telling Calc to perform a calculation. Second, it contains the formula name of SUM. Actually, all of the letters we see here are upper case, but it doesn’t really matter. Calc will work with them fine if they are lower case, or even if some are lower and some are upper. For instance:
would work just as well, though you should expect that the next time you look at this Calc will probably have turned it back to all upper case.
Using Parentheses in Formulas
Next, you see an expression inside of parentheses. This is important, because the best way to build formulas is to use parentheses to set off parts of the formula. This helps to resolve ambiguity. For example, what is the value of:
Now you could rely on understanding the rules for order of operations, and decide that the multiplication needs to be done first, making this equivalent to
And that resolves to 14.
But if you actually wanted to do the addition first, what you wanted was to do
And that resolves to 20.
The best way to be perfectly clear and avoid problems is to use parentheses to ensure that some operations are done before others. If you write your formula as
there is no ambiguity at all. You want to do the calculation inside the parentheses first, then the remaining calculation when that is complete.
For more complicated expressions you can do what is called nesting of parentheses. This means that one calculation set off inside of parentheses is contained within another expression also inside of parentheses. And the rule is that the innermost set of parentheses always gets calculated first, then calculations are done in order as you work outwards. For example, take this expression:
This would be resolved by first looking at the innermost set of parentheses, which contains the expression 4*5, which would resolve to 20. Then the next set of parentheses working outward has us add 3 to the 20 we got from that earlier calculation, giving us 23. Then the whole thing is multiplied by 2, giving a final result of 46. Or so you might think. We all expect that when a number appears in front of a parenthetical expression that we will multiply it. But Calc hates ambiguity, so if you actually entered the above formula, Calc would throw an error and ask you if it could change the formula to what it considers a proper formula, which is
So, best practice in LibreOffice is to always use parentheses to make clear the order of operations, and always use mathematical operators (that is the general term for the symbols we use like +, -, the Sigma, the square root sign, etc. And did you know that in addition to the upper case Sigma (for Sum), there is also an upper case Pi for Product? And why those letters? They are the equivalent sounds for S and P in Greek.<g>
Now, with nested parentheses you do need to be careful that every parenthetical expression is properly closed, by which we mean that the open parentheses sign is matched by a close parentheses sign. For example:
should not compute at all. Actually, Calc does compute it because it is assuming it knows what you want. This is after all a pretty simple formula. Here is one more complicated:
When I enter it into Calc I get the answer 50, which means that Calc is inferring that a close parentheses sign should have come after the final 2. There is clearly a missing close parentheses sign, in fact two of them if you counted carefully, but we are starting to get dangerously close to a situation where what you intended and what Calc delivers are diverging. Calc will always try to make sense of what you enter, and will often succeed, but you should keep in mind stories like the one about the billion-dollar spacecraft that crashed because of a math error in the calculations.
The last icon on the bar is an equals sign, and clicking this will put an equals sign at the beginning of the formula area for whatever cell you are in. It also changes the icons on this bar, and in fact the Sum icon also does. When you start working on a formula, the Sum and Equals buttons go away and are replaced with a red X and green Check Mark. These keys are used to either discard your edits (red X) or accept them (green Check Mark). You need to get used to these two buttons or you will get very frustrated trying to edit formulas. The red X does not, for instance, clear a cell, unless that cell was already clear before you started editing. What it really does is to revert the cell to whatever contents it had before you started editing.
Editing a formula begins when you click on the Formula area (technically called Input Line) and place your insertion mark there. If the cell you are editing already contains a formula, you can start to edit that formula. For instance, if you noticed a problem with your parentheses, you click on the Input Line, and make the changes. If the result was to your satisfaction, you could click the green Check Mark to accept the edit. But if it isn’t working for you, just click the red X and you will revert to the previous contents of the cell.
Building Formulas with Cells
Of course, when you understand how to use spreadsheets to their full power you will want to build formulas with cells, not plain numbers. This is done by using cell addresses. The formulas work in pretty much the same way, but would look like this:
As before, first you multiply whatever is in cell D3 to whatever is in cell E3, take the product, add it to whatever is in cell B4, then take that sum and multiply it by whatever is in cell A2. If A2 happened to have a 2, and B4 happened to have a 3, and D3 happened to have a 4, and E3 happened to have a 5, it would be exactly the equivalent of our previous example:
But, as we will see in a later tutorial this method is much more powerful and opens up ways of modeling and doing “what-if” analysis that using simple numbers can never accomplish. So it pays to get used to using cell addresses to build your formulas. And Calc makes it easy. If you were to click on a cell, then place your insertion mark in the Input Line, you could type the whole formula, and that would work. But there is an even easier way. When you are editing a formula, you can click on another cell, and instead of moving the focus to that cell, Calc will stay with your formula and simply insert that cell address into the formula. So I could build my formula this way:
- Press the Equals sign
- Click on cell A2
- Type an asterisk (for multiplication)
- Type an open parenthesis
- Click on cell B4
- Type a Plus sign
- Type an open parenthesis
- Click on cell D3
- Type an asterisk
- Click on cell E3
- Type a close parenthesis
- Type another close parenthesis
- Click the green Check Mark
Note that when you do this, the cell you click on is highlighted in a color which helps you see what you are doing. It literally takes longer to describe this than it does to actually do it, and when you get comfortable with building formulas you may find it very natural and much faster. Either way works, so figure out what works best you. But this can be an area where that red X will come in handy. Sometimes, you may be working on a formula, and then click a cell without realizing what you are doing and suddenly you have an errant cell address appear in your formula. One way to get out of that is to click the red X to discard your changes and revert to the previous state. And clicking either the red X or the green Check Mark will take you out of editing mode so that you can now click on other cells to inspect them with having them added to your formula.
One last thing. If you look at the SUM formula we used at the beginning of this tutorial, you may note that inside the parentheses was the expression B2:B13. This denotes a range of cells, and is interpreted as every cell from B2 through to B13 inclusive. So this short-hand is a good way to handle a bunch of numbers as long as they are in a contiguous range. So the formula
is precisely the same as
But I think you will agree it is a lot easier to write this using ranges and a function.
Listen to the audio version of this post on Hacker Public Radio!