Posted by Gary Rosenzweig on 6/19/09.
You can follow Gary on Twitter.
Learn how to use basic spreadsheet formulas in iWork 09’s Numbers program. You can use basic mathematical functions and also use functions to get the sum or average of a list of numbers.
Video Transcript (Click to Expand)
Gary Rosenzweig: Hi this is Gary with MacMost Now. Today’s episode, let’s learn how to create formulas inside Numbers.
So iWork 09 includes Numbers. Now Numbers is a great spreadsheet program but if you’ve never used a spreadsheet program you may not be aware of what they can do. Let’s take a look at the most basic functions in spreadsheet programs which is to be able to create formulas.
So here we are in the basic, blank spreadsheet with formulas in Numbers. You get this by using the blank template when you create a new spreadsheet. Now you’ve got a bunch of different things here called ‘cells.’ You can see that I’m navigating around them. I can use my arrow keys or I can click with the mouse on a different cell. And I can click on any cell and start typing and add something to it. So I can add a number for instance. I can go ahead and continue to add different numbers like so.
Now, formulas are things that operate on more than one cell. So you can basically, say for instance add two cells together. If I wanted to add this cell together with this cell, 42 plus 56, I can do that by going over here and instead of typing a number, I start off by typing equals (‘=’). This immediately changes things and I’m now typing a formula. And you can see by this little box here, this gray box with the X and check mark next to it. Now I can go ahead and select the different cells here. So I’ve got this one, I can click on it and you can see it instantly puts B4 I in there. You can see that this is column B, row 4. Now I can go ahead and use a symbol like a plus (‘+’) sign. And I can click on another box and you can see it says B5 and you can see also there’s some color coding. 42, B4 is in blue and you can see it here in blue. B5 is in orange and here it is in orange. Now if I simply hit return or click on the plus symbol here, I will go ahead and activate this formula and it will perform the calculation. So you can see the total is 98.
Now the great thing about spreadsheets is that it’s always alive. So if you change one of these numbers, like say change this 56 to 43, this number instantly changes to reflect the difference. Matter of fact, if I click on it I can see up here that it’s showing me what the formula is. So I click on say 42 it shows me 42 is in there, 43 is in there. Click here it shows me that this is a formula of B4+B5.
Now if you can imagine, you can use all sorts of different operations inside of a formula. You can even add regular numbers. So for instance, if I want to subtract 2 from this, I could put minus (‘-‘) and then 2 and hit return and I will basically get the addition of this cell plus this cell minus 2 in there. I can see there’s the number, there’s the formula.
Now one of the most common things that you want to do in a spreadsheet is add up numbers. So suppose I wanted to add up these three numbers and put the total at the bottom. I can click down here, press equals, click here then the plus sign, then the plus sign again and there you go. B4+B5+B6, hit return I get the total of 163. I change one of these numbers and say I make it 232 and you can see that number down here changes and this formula over here changes still as well.
Now there’s an easier way to do that. Instead of having to do plus, plus, plus; I mean what if this column was like 100 numbers long? That would create a very long formula. You can use a function. So let’s go ahead and erase this formula here and put equals and use the function ‘SUM’, which might be the most commonly used function and put a parenthesis around it. So S-U-M and then I can go ahead and click here. But instead of actually clicking and releasing, I’m going to drag and create this area. And you can see it puts up there B4:B6, in other words the area of B4 to B6. I’m going to release and then I’m going to add a right parenthesis there, hit return and I get this same sum here but now I’ve put that together. So I can go ahead and change any one of these numbers and you can see it’s reflected there in the sum.
So another great thing that you can do with the SUM formula is you can add cells and it will follow suit. So for instance, I’m going to go ahead and select 78 right here and I’m going to add another cell below that. I’m going to go to “table” and “insert row below.” Now when I click on this formula here you can actually see that it’s highlighting all four cells because it figured that I was adding another cell to this table. So I want to go ahead and put number five and you can see it updates. And go ahead and add a couple more here. Add row below. Add row below and we’ll follow suit and know what these numbers are.
Now you can note that when I select this formula here, it actually highlights in blue all of the cells that are involved. It does the same thing for this formula here. It highlights these two. It actually does it color coded. So it’s very easy to click on a formula and figure out which cells it involves.
So there’re a lot of different functions you can use in a formula. So here I’ve got SUM but I can actually change that to another common one is AVERAGE and it gave me the average there.
Now you can also copy and paste functions. So if I created a second column of numbers like so and I decided I wanted the average of this column as well, I can copy the formula here, paste it there and you can see it gives me a different number. It’s actually showing that this is the average of B4 to B9 and this is the average of C4 to C9. It’s smart enough to know that I’m copying and pasting from one column to another, that I probably want to change which cells the formula point to.
You can also go ahead and have formulas act on other formulas. So for instance, if I wanted to do a sum here that was adding together these two columns which are actually themselves the results of formulas, I can do it. So now I get the sum of these two which are the averages of each of these other columns. So you can keep doing that and adding formulas that rely on other formulas and as you change these numbers, you can see everything changes. This number changes which then changes this number.
That’s a quick primer on how to use formulas in a spreadsheet like Numbers. There’s a ton of different functions that you can use; all sorts of scientific functions and statistical functions, things like that. The best thing to do to learn how to use a spreadsheet is to start just playing around with it. Playing around with some different numbers and also looking at sample spreadsheets like some of the templates that come with numbers.
Hope you found this useful. Til next time this is Gary Rosenzweig with MacMost Now.
Gary Rosenzweig: Hi this is Gary with MacMost Now. Today’s episode, let’s learn how to create formulas inside Numbers.
So iWork 09 includes Numbers. Now Numbers is a great spreadsheet program but if you’ve never used a spreadsheet program you may not be aware of what they can do. Let’s take a look at the most basic functions in spreadsheet programs which is to be able to create formulas.
So here we are in the basic, blank spreadsheet with formulas in Numbers. You get this by using the blank template when you create a new spreadsheet. Now you’ve got a bunch of different things here called ‘cells.’ You can see that I’m navigating around them. I can use my arrow keys or I can click with the mouse on a different cell. And I can click on any cell and start typing and add something to it. So I can add a number for instance. I can go ahead and continue to add different numbers like so.
Now, formulas are things that operate on more than one cell. So you can basically, say for instance add two cells together. If I wanted to add this cell together with this cell, 42 plus 56, I can do that by going over here and instead of typing a number, I start off by typing equals (‘=’). This immediately changes things and I’m now typing a formula. And you can see by this little box here, this gray box with the X and check mark next to it. Now I can go ahead and select the different cells here. So I’ve got this one, I can click on it and you can see it instantly puts B4 I in there. You can see that this is column B, row 4. Now I can go ahead and use a symbol like a plus (‘+’) sign. And I can click on another box and you can see it says B5 and you can see also there’s some color coding. 42, B4 is in blue and you can see it here in blue. B5 is in orange and here it is in orange. Now if I simply hit return or click on the plus symbol here, I will go ahead and activate this formula and it will perform the calculation. So you can see the total is 98.
Now the great thing about spreadsheets is that it’s always alive. So if you change one of these numbers, like say change this 56 to 43, this number instantly changes to reflect the difference. Matter of fact, if I click on it I can see up here that it’s showing me what the formula is. So I click on say 42 it shows me 42 is in there, 43 is in there. Click here it shows me that this is a formula of B4+B5.
Now if you can imagine, you can use all sorts of different operations inside of a formula. You can even add regular numbers. So for instance, if I want to subtract 2 from this, I could put minus (‘-‘) and then 2 and hit return and I will basically get the addition of this cell plus this cell minus 2 in there. I can see there’s the number, there’s the formula.
Now one of the most common things that you want to do in a spreadsheet is add up numbers. So suppose I wanted to add up these three numbers and put the total at the bottom. I can click down here, press equals, click here then the plus sign, then the plus sign again and there you go. B4+B5+B6, hit return I get the total of 163. I change one of these numbers and say I make it 232 and you can see that number down here changes and this formula over here changes still as well.
Now there’s an easier way to do that. Instead of having to do plus, plus, plus; I mean what if this column was like 100 numbers long? That would create a very long formula. You can use a function. So let’s go ahead and erase this formula here and put equals and use the function ‘SUM’, which might be the most commonly used function and put a parenthesis around it. So S-U-M and then I can go ahead and click here. But instead of actually clicking and releasing, I’m going to drag and create this area. And you can see it puts up there B4:B6, in other words the area of B4 to B6. I’m going to release and then I’m going to add a right parenthesis there, hit return and I get this same sum here but now I’ve put that together. So I can go ahead and change any one of these numbers and you can see it’s reflected there in the sum.
So another great thing that you can do with the SUM formula is you can add cells and it will follow suit. So for instance, I’m going to go ahead and select 78 right here and I’m going to add another cell below that. I’m going to go to “table” and “insert row below.” Now when I click on this formula here you can actually see that it’s highlighting all four cells because it figured that I was adding another cell to this table. So I want to go ahead and put number five and you can see it updates. And go ahead and add a couple more here. Add row below. Add row below and we’ll follow suit and know what these numbers are.
Now you can note that when I select this formula here, it actually highlights in blue all of the cells that are involved. It does the same thing for this formula here. It highlights these two. It actually does it color coded. So it’s very easy to click on a formula and figure out which cells it involves.
So there’re a lot of different functions you can use in a formula. So here I’ve got SUM but I can actually change that to another common one is AVERAGE and it gave me the average there.
Now you can also copy and paste functions. So if I created a second column of numbers like so and I decided I wanted the average of this column as well, I can copy the formula here, paste it there and you can see it gives me a different number. It’s actually showing that this is the average of B4 to B9 and this is the average of C4 to C9. It’s smart enough to know that I’m copying and pasting from one column to another, that I probably want to change which cells the formula point to.
You can also go ahead and have formulas act on other formulas. So for instance, if I wanted to do a sum here that was adding together these two columns which are actually themselves the results of formulas, I can do it. So now I get the sum of these two which are the averages of each of these other columns. So you can keep doing that and adding formulas that rely on other formulas and as you change these numbers, you can see everything changes. This number changes which then changes this number.
That’s a quick primer on how to use formulas in a spreadsheet like Numbers. There’s a ton of different functions that you can use; all sorts of scientific functions and statistical functions, things like that. The best thing to do to learn how to use a spreadsheet is to start just playing around with it. Playing around with some different numbers and also looking at sample spreadsheets like some of the templates that come with numbers.
Hope you found this useful. Til next time this is Gary Rosenzweig with MacMost Now.
Mention this post on Twitter!
Submit this post to Digg.
Check out the free MacMost weekly email newsletter.
Become a fan of MacMost on Facebook.
This is a great episode! It also works in iWork ‘08 if anyone is wondering…
Thanks for the simple introduction to spreadsheet formulas. I very much like the color coding in formulas for use with young students. One issue I can’t seem to resolve, though, is how to handle a formula which uses the same cell twice. For example:
=B1 + 2*(A1 + B1)
While I can type this formula in directly, I can’t get the second B1 by clicking on the cell- clicking on cell B1 takes me back to the first B1 in the formula.
Is this a bug or a “feature” that I don’t understand?
Aha! I figured the work-around: hold the Command key before clicking. The icon changes to allow the cell to be used more than once in the formula.
I don’t think much of this “feature”…
Yes, that’s the trick. I guess the feature is the ability to find a previous reference to a cell in the formula.
Hi and thanks for all. I have a big issue (may be for me), if I want create a questionnaire and I want ask for example: how many employees you have? the answer is “5″ is possible to create others five question list for each employee?
(like a repeating as many as the answer).
Not sure what you are trying to do. You could always sit down with someone at an Apple store and have them show you.
Thanks for sharing this information. I’ve been an Excel spreadsheet user but couldn’t figure out how to create a formula in Numbers. Or more specifically, what to do when the calc box was displayed in the blank cell where I wanted the results to be displayed. Excellent job!