Join David Rivers for an in-depth discussion in this video Using formulas, part of Numbers 3 Essential Training.
We're ready now to get into the heart of what Numbers is really all about and that's the formulas and functions that allow you to calculate your data. So we're going to continue working with our Kinet Eco budget file, and if you've skipped to this movie, you can open up KEcobudget0501 from the chapter four folder of your exercise files. We're on the profit/loss summary sheet and the first table we see here, the annual budget overview has some labels but no numerical data at this point. That's okay, we can add a formula and then add the data later.
Those formulas will use the data once it appears in the table. I'll show you want I'm talking about. Let's go to the last column, the difference column, in the first row under that label, which is cell D3. Now there are a couple of different ways to start a formula. We touched on it already. One option is to go to the insert menu, down to function, and you'll see "Create Formula" right there. Clicking that will start a formula. Now a couple of things happen. The flashing cursor is waiting for our input. We have buttons to accept or reject the formula we add here.
Also over here on the right-hand side, look what's happened to our inspector. We're looking at a list of functions and there are many, many built-in functions here that we'll get to in the next movie, but for now to add a simple formula, we'd simply start typing. Let's just click anywhere outside the table though, and make sure the cell is still selected because you could also go to the function button and select create formula from there, same thing. Or, if you prefer using the keyboard, hit the equals sign. The exact same thing will happen. So we see the equal sign there, waiting for our formula and now it's a matter of adding the formula itself.
We can do that with our mouse by clicking the cells we want to use or we can type them in using the cell addresses. In other words C3 minus B3. That would give us our formula. So you could type in C3, and as soon as you do you'll see that cell address appear, then the minus sign, but if you prefer, I'm going to hit the backspace key to take that out. You can click those cells so you're really sure you're getting the right address. So C3 minus and click B3.
That's it. We'll click the check mark to accept that and you can see the result for now is zero. That's because there's no data showing up in the cells, but once we add the data we'll get the right answer. Before we start adding the data though, let's go back to this cell because we want that formula in the next two rows as well. So we could go to those cells and add the formula but the fastest way is to go to that selection handle in the bottom center. When you hover over it and see the double arrow that means you can click and drag to copy it down, and by default when you copy a formula down like that you're using relative addressing.
All that means is if we go back to cell D3 and look down at the bottom of the formula bar there's our original formula, C3 minus B3. Well Numbers knows when we copy it to row four to use C4 minus B4 and in row five where we copied it, it's going to use C5 and B5. It's relative to the cell where we copied the formula. That's relative cell addressing. On occasion you want an absolute address. We'll be talking about that in a couple of movies, but for now we have the formula we want.
Now all we need is the data. So let's just experiment with some numbers. Maybe we estimated 455 and the actual was 502, press return. You can see the calculations happening on the fly. Now obviously these numbers are going to come from somewhere else. So we'll just go to the cells and hit our delete key to remove that data, and when we do, you can see the formula is updated again to zero. These numbers come from other sheets.
We're going to be using functions to add those to those cells, but for now we have our simple formula in here. Let's say we also wanted to calculate the difference in another currency. So for me being in Canada, maybe I want a Canadian value showing up here. I'm going to add an extra column. So we can do that by going to the column button at the top right-hand corner. In the top cell in row two I'm going to add difference and in brackets CDN.
So the Canadian value is going to use an exchange rate to give me the value, which is going to be the difference multiplied by that exchange rate. Let's add the table for the exchange rate. We'll go up to table, we can select just this one here with the header row at the top, and we're going to shrink it down. We only need one column and two rows, the header row and our cell down below. Next, we'll move it by going to the top left corner. I'm going to move it up next to my original table here, get things lined up nicely, across the top perhaps.
Also I'm going to change the style to match the other tables. Go back to the table tab here. We created this style in an earlier movie. There we go. For the name, just double-click to get inside, double-click to select table one, and type in "Exchange Rate," like so. If we need to, we can stretch this out. Notice the plus sign that appears, indicating we're not seeing everything, so let's go right up here just to the right of the header for A, column A, and stretch it out until we see exchange rate.
We really don't need the header row, so either we use the table name or the header row, it's up to you. I'm going to go back to this table and decide maybe I won't use the table name. I'm going to deselect it here from the headers and footers and instead type it in the header row. It will probably look better there anyway. Now down below, we can add the rate. Let's say it's one point zero nine, and return. So the difference now is going to be in Canadian, the difference column times that exchange rate.
We'll just keep it there for now. I'm going to move this table down to line it up properly. There we go. That's a good spot right there. We'll keep that in mind when we talk about absolute addressing in a couple of movies. For now though, we have our table, we have a simple formula in there, and the next thing we need is to get the data from those other sheets, where it already exists using some functions. That's coming up next.
- Creating and laying out spreadsheets
- Importing data
- Working with tables
- Adding images to cells
- Performing calculations with formulas and functions
- Creating charts
- Formatting text and numbers
- Including audio, video, and shapes
- Designing templates
- Printing and sharing spreadsheets