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.