We're going to start with the AVERAGE function and we're going to want
to get the average of the order amount. Since I took my mouse and I already
selected the amounts that we want to average, I can cheat a little bit and go
in the lower right hand corner and I select this. It will tell me that the
average is about $340. We're going know if we're right if
we come up with that number. If we type in the function, you can do it
lowercase or uppercase it doesn't matter. Once you type the beginning
parenthesis, this box of instructions comes up and the highlight is on the
section that I'm out on right now. Since I put in the starting parenthesis,
there's a little gray line underneath and it says that that's waiting for
input and the input that it's waiting for is what's highlighted here. It wants
the first value and the value is going to be the range of F3:F8.
Now you see that little gray line is not here anymore.
You can be done with the values. It doesn't need anything to complete the
function and it's already telling you in the blue font what the answer is going
to be if you end it right now. I'm going to put in a closing parenthesis
and type enter and there it is. $339. That's what we saw in the lower right
hand corner so we know that's probably right.
The next three functions that you're going to use all the time are SUM
functions. There's different types of SUM functions and we're gonna use them
all on the amount column. The first one is just a straight-up regular SUM
function. It's already in here. I'll double click on it.
It's just SUM and then it takes the range. That is going to add up
all the orders no matter what and it comes up to $2,037.
You can also sum them according to criteria that you set.
You can say, sum these if, SUMIF, then you select the range with the criteria
in it. i'm not summing the sales rep range. I'm using this range to decide
whether or not I want to sum this range. this function goes one row at a time
and it looks to see if column C has the word Rex in it and, if so, it's going to
add the corresponding value in column F for Row 3. So it'll add $261.96, it'll
skip these, it'll come down and pick up $48.86. Then you'll be done. If I
hit enter, that's how it comes up with $310.82. The next one is called
SUMIFS and that's just a compound SUMIF statement. It's saying, sum these if a
couple things are happening. In this case, it's saying sum it if the sales rep is
Rex and Rex sold those items in Henderson. That happens to only be this first line
because the other Rex was sold in Emsworth, Pennsylvania. That should
just sum up to $261.96. Three different types of SUM formulas and they can get you
completely different answers. We've gone through AVERAGE and we've gone through
SUM, another one that you'll probably use a lot is the COUNT function. We're
going to use it on the extra column because it can act differently when
there's text. With a straight COUNT function, all it does is it counts
the occurrences of numbers. I'll look in the helper text here. It returns the count of
the number of numeric values in the data set. There's only one here, 22. If you use
COUNTA, it will count all of the values in here.
The only thing it won't count is blanks. Right now, it's getting 4 which looks
like what it should be getting. But, you have to be careful with the COUNTA
because, if I go into cell K5 and I hit the space key, which is a way of putting
some information in here, but you can't actually see it. You'll see the count
goes from 4 to 5. Maybe that's still what you wanted, right? Because that
is data in there; it's just a space and you can't see it. But, be careful. If
you're using COUNTA, and you're getting a value that you don't expect, you may have
to go about this differently. Then COUNTIF can be used in any way you want.
In this case, we're saying let's count it if it's has the letters VIP in it.
That's your criteria. That happens up here twice and so it's two. Again, if
I put a space before the VIP, it's not going to count this. It only found one
of them. If there's a space now you can get into the TRIM function if you need
to clean up your data, but that didn't make the top ten! The next functions are
TODAY and NOW. These can be used to figure out the amount of time that's
elapsed. They're very similar functions, but one of them has a little bit extra
compared to the others. If you do TODAY, that takes just the value of the
day today. This works a little bit differently in that it takes nothing in
the parentheses but it still needs them because all functions need parentheses.
So just close this one off. That's TODAY. If you subtract the time at
which this sale was made, you can tell it was done 258 days ago. The now function,
like I said, works very similar to it and it's gonna return the day and the time.
It's early in the morning right now. If I want to find the exact amount of time
that has elapsed from these orders, I could do NOW and then I can subtract
one of these dates and, so add them together, one of the times. Do the
subtraction. It's almost 258 days ago that that sale was made. You can tell the
times come out just as fractions of a day in Google Sheets. If one of these
dates was noon, it'd be a half. The the last function that you'll probably
find yourself using a lot is CONCATENATE and concatenate joins values together.
We could go through this table and we can join these to make it look like an
address. How you would do that, you would just start out with =
and then you type CONCATENATE. Sheets is guessing what I'm gonna type here. I'll
select the second one. Left click on it it. It says, hey give me the first thing to
concatenate. I'll give it Henderson, then I'm gonna want a comma and a space.
To add those as the exact value, just type it in as a string. Make sure you put
quotes around it. That's what tells Sheets that you're typing in a string
and not a number. And, you want the value of Arizona from I3. I'm grabbing those
just by left-clicking with my mouse. Another way to get them, let me put a
space in here is again, you see the little gray bar at the bottom that shows
it's waiting for input. Take your arrow key and just start moving the cell
selector around and go over to the postal code. You see it's filled in
J3 and the blue text is telling you what its gonna look like if you just hit
enter now and that's right so just close it off with the parenthesis and type enter.
And that's CONCATENATE. If you want to do that for the line below it,
just like any function, after you enter it, you have a blue rectangle. In the
lower right hand corner of the blue rectangle you have a little square. Hover
over that so your mouse becomes a plus. Hold down your left mouse key and just
drag it down. I'll go further so you'll see it stops working. These are
relative cell references so the next one picked up this line,
the one after that picked up the third line. So that should do it. That covers the top
10 functions that you're going to use working with spreadsheets. Almost all
the time, these are going to be all that you need to get done what you need to get
done. If they're not what you need, you can find all the others if you go up to
Insert and select Function. This is some of the more common ones. We didn't even hit on
MAX and MIN. You do More. This is going to list all the functions so you can
look them all up here to find what you want. I hope that was helpful.
If you like that video, and you want to see others just like it, please subscribe
in the lower right-hand corner. Thanks.