Formula and Function Basics In Numbers

Learn how to use simple formulas in Numbers to calculate results. You can use basic math operations or complex functions from a library. You can feed these functions single cells or ranges of cells. You can also copy and paste formulas to perform the same calculations on other rows or columns.

You can also watch this video at YouTube (but with ads).

Video Transcript: Hi this is Gary with MacMost.com. Let me show you the basics of using Formulas and Functions in Numbers.
I'm using Numbers version 3.0. We're going to look at some of the basics of using functions and formulas. So I'm just going to open up the simplest blank template and it is going to give me a single spreadsheet with a single table in it. 
You can put values in these cells. So let's say put 2 and 7 in here. The basic idea of a formula is you can perform operations like, for instance, adding these two together. 
In order to start a formula instead of typing in a number, if say I wanted to type the number twenty in here I would type 20. But instead I'm going to hit the equals key on my keyboard which tells Numbers that I want to enter a formula. You can see it comes up with a special interface there for entering a formula. 
Now I can type in the names of the cells if I want to. I could say B2 and then I could hit a plus symbol for adding two together and then C2. You can see when I hit return it is going to give me the result. If I hover over this one you can see at the bottom here I've got the formula B2+C2. 
An easier way to do this, let me delete that, is I start typing equals and then I actually click rather than type B2. So I click on B2 and then I hit + and I click on C2, hit return, and I get the same result. I don't even have to type it in and figure out the name of the individual cells.
Now I can get really complex with these formulas. Let me delete that one here and put in another number and another number. Then in here I could use parentheses and say this plus this and then I could close the parentheses and add this and say divided by this. You can see I've created a pretty complex formula there and I get the answer.
The cool thing about formulas is they update automatically. So if I change this 2 to a 3 you could see this changed. I change the six to an eight and you can see it also changed. It is always going to be the result of this. If I change these values then the results of this will change and it will automatically update to reflect that.
So that is using simple math. But you can go further than simple math. Let's delete this here and let's hit equals again but instead of using basic things like plus and multiply we will go and click on the Function thing up here and we get several different functions. Let's say let's do minimum. The minimum function is going to take a range of values. So to give it a range of values I can just select the range like that. You can see it fills it in with B2:E2 telling me it is this range. When I hit return I can see it gives me the answer of 3, three being the minimum value here. If I were to change this to say 13 the answer is four because now this is the minimum value. So you can see how I used this minimum function to perform an operation on this stuff so I didn't have to just use math I can also use functions.
Now you may have also noticed when I double clicked in here I brought up this function browser on the right. This gives me a list of all the functions available in Numbers. There are a ton of them. So many they have to break them up into categories. So, for instance, I can look statistical functions. I can look at functions that deal with logic. I can look at functions that deal with numbers. So for instance if I wanted to figure out the square root or something like that I've got it all in here. 
I can easily search for the function as well. So if I wanted to see all the functions that have to do with average I start typing average and I can see there is a bunch of different ones.
If I want to find out what any of these means and an example how to use them I simply select it from the list and I can look down here below and I can see a description of it and some examples of how to use that function.
Now a lot of formulas take a range of values. For instance sum. I can do equals sum and I can take a range like that, B2 to E2. I can also say get the sum of an entire column. So just click on the top here and you can see I just put C there and it shows me exactly what it is going to do. It is going to do the sum of that. There is only one number in there but if I were to add another one you can see it automatically updates. Likewise you could do the sum of a row like that. It will work just the same. It will show you what it is going to take the sum of. Notice Header and Footer cells are not included when you want to use the range of an entire row or column.
One more thing you should know about formulas is that they can copy and paste really well. So let's say I have a second row of numbers. I create a formula here which is going to be the sum of these. So you can see this is the sum of B2 to E2. If I were to copy this, let me select the cell just once, copy it, paste it, I get the formula that is not the sum of B2 to E2 but the sum of B3 to E3. It recognizes that I copied from F2 and I put it into F3, I moved it down one. So it moved the numbers in the formula down by one. So that it makes it very easy to copy and paste. As a matter of fact if I had a ton of random numbers in here, like that, I could simply select it once and selected this range here and pasted that same formula across all of these it is smart enough to know this one should be B4-E4, B5-E5, etc., all the way down.

Comments: 9 Responses to “Formula and Function Basics In Numbers”

Pat K

11 years ago

Gary, After using Excel forever I was lost with Numbers and then I found your videos. Thanks for making the transition so much easier, in fact I finally even enjoy using Numbers.

Barb

Thanks, Gary,
That is so simple and I was always having trouble with formulas.
Barb

Danny

Thanks Gary, you example of how to search formulas and find examples of their use helped me to find and use the roundup formula i needed to keep track of credit card pos fees.

Jose

Just update my numbers to mavericks and when create a table on new numbers, I cannot center on a page portrait/Landscape, also auto dil cells when doing a list. Please help me out.

Thanks

Gary Rosenzweig

Haven't played with printing. Just experiment and try different things, like moving the table. As for filling, it works similar to how it did before.

Piet van 't Zelfde (Pete)

Hi Gary, Wonderful your explanation, I myself have been working with excel but love to change to numbers. I have one with this: =ALS(EN(C26"";D26"");D26/C26;"")
ALS = as EN = and. Can I put this formula into numbers?
Thanks for your reply.
Regards
Piet van 't Zelfde The Netherlands

Sorry, I don't know what that formula does. Looking at a list of Excel functions, I don't see an AS function and I'm not sure what the quote marks mean in your formula. What are you trying to do?

Paul

Any chance you can help with a SUMPRODUCT issue? I know that Numbers does not work with arrays in the sam way as Excel so I am having problems with a spreadsheet I migrated. The Excel sheet uses the following formula:

=SUMPRODUCT((Scores::Table 1::$F$1:$F$5000=$B4)×(Scores::Table 1::$E$1:$E$5000>Scores::Table 1::$D$1:$D$5000))

Basically, it checks to see how many home wins there are by a specific team (B4). I get an error that says the formula uses a Boolean instead of a number.

I don't see how using SUMPRODUCT makes sense for what you are trying to do. There are many ways I can think of to do it. For instance, you could add another column to each row that uses IF to put the name of the winner of each game. Another column could then repeat that name only if the home team won. Then use COUNTIF to get the total number of times each team name appears in that collection of cells. Or, use COUNTIFS to do it. There are so many ways, but I don't see how SUMPRODUCT helps.

Comments Closed.

Formula and Function Basics In Numbers

Comments: 9 Responses to “Formula and Function Basics In Numbers”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information