The 10 Functions That Every Mac Numbers User Should Know

Here are 10 Numbers functions that all beginners and expert users of Numbers should know. Learn how to use functions like SUM, SUMIF, LOOKUP, STOCK and more.



Check out The 10 Functions That Every Mac Numbers User Should Know at YouTube for closed captioning and more options.
Video Transcript: Hi, this is Gary with MacMost.com. Today let me show you ten functions that every Numbers user should know how to use.
MacMost is brought to you thanks to a great group of more than 1000 supporters. Go to MacMost.com/patreon. There you can read more about the Patreon Campaign. Join us and get exclusive content and course discounts.
Now there are a lot of functions that you can learn in Numbers. But don't get overwhelmed by it all. A lot of them are very specific to things like finance or engineering. But there are certain ones that are very general and that every Numbers user should know. Let's start off by looking at Sum. Now each time I show you one of these functions I'm going to show you the function's Help which you should read for each one of those to understand how to use it. There are great examples there. For instance for Sum I'm going to use the Equals key to start entering a formula. I'm going to type Sum here and notice function's Help comes up on the right. You need to search for the function there. Then you'll get all of this great help information here at the bottom. So it's important to look at this and see the examples that are there. They really are good and help you understand how the function works. I'm going to show you the basics for each function.
So for Sum here you need a Value and you can include a range of cells. For instance I can click here and select these cells and you could see it shows from the Sales table C2 to C7. Hit return and it gives me the Sum of those cells. If I select the formula there you can see it even highlights those cells again. But a better way to use this is to just click at the column header here, so the C column, and you see it inserts the name of that column. Now it's going to give me the sum of all of the values in this column even if I add some to the end.
Now similar to Sum there's Average. So I could start typing average here. If I look it up you could see the information for it there. I could do the same thing here getting the average for the entire column and that will do the division correctly no matter how many rows there are. So I add more rows and the average will still be calculated correctly. In addition to average you could also substitute Median and Mode for different types of averages.
Now it's important to know how to do tests in Numbers and the main thing you would use for a test is an IF statement. So in this case let's say there's a special charge for apples here but not for anything else. So you want to be able to record the charge here but only for apples. You could go into this first cell and use IF. If you look up IF here we could see it takes an expression and then a True value and then a False value. So in this case the expression would be IF this value here is equal to apples. Then if it's True let's say there is that special 10 cent charge and a False nothing. So we get a zero there. Now if I copy this and paste it in all these cells, I'm just going to double click on the column head there and notice how it doesn't choose the header cell just all the value cells, and I could paste it in. You could see here I get that 10 cent charge but only where apples are concerned.
Now if you want to have more complex tests you need to use things like AND, OR, NOT. These logical operators will help you combine things. So, for instance, let's say we want to test to see whether it's an orange OR an apple and have that special charge for it. So we want to test to see if either one is TRUE. Now to find that we're going to use OR which you can see here takes multiple expressions and if anyone of them is true then the total result is true. So we're going to surround this one expression here with OR like that. Then we're going add another test inside of it. So in this case the same value but whether it's equal to oranges. So B2 is either apples or oranges this will be true and you'll get the 10 cent charge. So we'll Copy that and Paste it in these cells here and you could see that apples and oranges both get the 10 cent charge. You could use AND to test for IF all of a bunch of conditions are true. You could also use NOT to reverse things.
Now that's great for performing a test on specific cells. Sometimes you want to perform a test and then get a total based on that. For instance what if I wanted to get a total for all of these sales of apples here. So let me put Apples in this cell and let's say I wanted to get the SUM of all of the apple sales. So to do that I could use the SUMIF. So that will ask for something to test and then what it matches. Then we'll add the values from another column together. So, for instance, if I do SUMIF let's test everything in column B and compare it to apples. We already have apples over here so let me just choose that. Then I'm going to add the amounts from Column C. So now I'm going to get the SUM of all sales of apples. So if I wanted to do the same thing for oranges I could Copy this. You could see how this refers to this column, this column, and this cell. So if I move it down one it will refer to these same two columns but now the cell below it. So it's a total for all oranges. I could do the same thing for bananas and get the totals for each kind of sale.
Now you can also use COUNTIF. That will count the number of items that are found. So for this we don't need an amount here we just need the test. In this case we're counting all the things from Column B that match what's in this cell here. So we get 40 there. I can copy and paste this in this too and we see there's 53 sales of oranges, and 33 sales of bananas. Now let's clear out these cells.
Let's say there are different prices for each one of these. Let's say there are different prices for each one of these. Let's say apples cost 25 cents, oranges cost 35 cents, and bananas cost 15 cents. Now we want to calculate the total price based on the amount and what type it is. So we want it to lookup the price of oranges and multiple that by 7. We can do that with the LOOKUP function. So we look at lookup. We see that we're searching for something in a place and getting a result from a different place. So, LOOKUP, and what are we searching for? We're searching for this value. Where are we searching for it? Well, over here. Then we're going to take the value from this column. So if we just look at that we can see for oranges it gets us 35 cents, for bananas it gets up 15 cents, and for apples 25. If we wanted to get the total price what we can do is simply multiple that by the amount there. That's the total price. I can Copy and Paste that throughout and now we have the total price for each one of these based on the price that's here. So apples changed to 30 cents. You can see the price for all the apples automatically changed.
Another very useful function that comes up in different situations is Ranking. So, for instance here I've got a bunch of students and their grades and I want to find out how they rank. So I could use the RANK function. If I look it up you could see it takes a value and then the set to look at the value and then whether the largest is high or low. So let's go ahead and for the value we'll set it to the score that they've got. Then the value set will be the entire column B. Then largest is high and we can select largest is low and now I can Copy that and Paste it here and we can see this is the top score right there and this is the lowest score right there.
Now here's a very different kind of function. A random function. Random functions are very important for testing things out. When you're designing a spreadsheet you need sample values. You don't want to design the entire thing after you have all the data. You like to design it before or at least before you use real data on it. So, for instance, when I designed this test spreadsheet here I have all these scores. But how did I get them? Well, I just used the RANBETWEEN function. So you can see Ranbetween and if I look it up here you could see it takes a lower and an upper. So I said, well, you know, let's range the scores from 75 to 99. There we go with a random number. You could see it puts random numbers in all of those. If I change any value the random numbers are going to be completely recalculated all the time. So it's also handy to know that you can Copy and then Paste formula results. Now instead of the function RANBETWEEN these are the actual values.
Now here's something completely different, the Stock function. This is one of the most asked about functions and it's pretty easy to use if you take the time to read the documentation. So here I have a Stock symbol right there. If I want to get the price it just simply Stock. I can look it up here and you could see that there's actually a symbol and the price or a different value for whatever it is that you want to look up. So you can leave this off and it defaults to Price. For Symbol I can just put in quotes Symbol or I can refer to this cell right here. Look it up for the current price for that day and then I can, you know, do that same thing for other stocks as well. You can see how it's going to grab the value from here. If you want to do something else you can grab the symbol and then choose something else. So, for instance, you can go for the Volume here and get the Volume amount. Note that there's also the stock H function which will give you historical value. So you provide the date for a day when the market was open and then you can get a lot of different values like the Closing Price or Opening Price or Volume for that day. There's also Currency which does kind of the same thing and will convert currency for you based on that day's current value.
So if you like learning those functions there are tons more to learn in the Function's Help here. You can go by Category and see many of the different things that are there. Look at the description for it. Look at some examples. Try it out yourself and a sample spreadsheet to get to know how to use it. Hope you found this useful. Thanks for watching. Related Subjects: Numbers (132 videos)
Related Video Tutorials:
Creating a Form In Mac Numbers Using a Script ― How To Mail Merge On Mac With Pages, Numbers and a Simple Script ― Sorting By Last Name in Mac Numbers ― Learning To Use Regular Expressions In Numbers