In this video, we're gonna look at three of Excel's math functions.
We are gonna look at the sum function, the sub total function, and
the round function.
Now, the sum function you already know.
You've seen this before, it's equal sum open brackets.
And we can go and select a range of cells.
Close the brackets and it will obviously sum them up for us,
totalling them all for us.
I'm just gonna drag this guy across here,
cuz we're gonna use this a little bit more in a minute.
I'm also gonna go down and do the same thing down here.
And sum these cells and copy them across as well.
Now, what about when we wanna start sub totaling our data though?
We can do this in a variety of different ways.
We can use the sum function to subtotal our data
by providing the first subtotal comma the second subtotal.
So they're not contiguous, but we could join these together and
come up with the correct answer.
Another way that we could do is, we could say =sum, provide the first subtotal
comma, and then we could recommit the entire range that we've totaled up above.
Now, this would be an odd thing to do and it's really not recommended.
But the point that I wanna show you here, is that you don't have to have data ranges
that are the same size when you're working with the subtotal.
That doesn't matter, this will work just equally well.
It may be harder to maintain but it will definitely actually work.
Now, what if we wanted to actually make sure that we weren't
gonna lose out if somebody inserted a new row somewhere, and
we said, I really wanna sum all the numbers from this range.
You might think well no, that's not gonna work because we've got text in there, and
the sum function can't handle text.
In actual fact, that's not true.
If I close the bracket and hit enter,
you'll notice that it just ignores the text and sub totals everything else.
The problem though is that it adds
all of these numbers plus the sub totals that we've already done.
So we've essentially come up with twice the value that we originally wanted.
In order to fix this, we need to go back and divide it by two.
So you notice we can do subsequent operations on the functions we've used
that will give the correct answer to this end.
Though there's a better function a better way to do this, so
were gonna use the sub-total function in order to sub-total this data now.
Now, you'll notice it ask for function number you say well,
how do I know what function number to provide?
Well, that's where you can click on this little link where it says subtotal,
and it will pull up the excel help article that shows you the function number
as well as the function it actually pulls off.
So, if we scroll down this list we can see that sum is number nine.
So let's try that.
We'll go with subtotal nine comma and then it says where's the reference?
What's the range you're gonna use?
So we'll grab this range.
Close the brackets and say okay.
And it gave us the same total.
So let's try this too.
We'll say Command C, Command V, and
we'll just make sure that the range is the same because the number didn't change.
So that's all fine.
So what's the difference between the sum and the subtotal function?
Here it is right here.
If we go to subtotal nine, and
we provide that exact same range, close our brackets and
hit enter, you'll notice that this time it comes up with the correct answer.
I don't need to divide anything by 2.
And the reason for this is that the subtotal looks at all the cells and
says hey, this was fed into this subtotal function, and
therefore I'm not gonna add any of these.
I'm just gonna grab the stuff from the subtotal function.
So that's kinda nice, because then if something gets inserted in the middle that
isn't included in the subtotal, it will now get added into this particular value.
So that's a really useful thing with the subtotal function.
The last thing I wanna show you is what would happen if we wanted to round
this off.
We wanted this to be 13 or 1355.
How about 1356?
What we would do is we would actually wrap this function in a different function?
This is a concept called nesting.
So we type right after the = key the round function, open brackets and
we've now nested in our sum function in place of our number.
So we say well that's good, I know what the number is right now its 1355.5.
Say comma, it says how many digits would you like to round to?
I'm gonna choose zero, so that'll round to 1356 but I could easily go one, two,
three, depending on how many decimal places I wanted.
So we'll just say enter on that.
You can see that I've got 1356 is rounded off nicely.
This guy here has already rounded to a nice round number.
I could still go and copy this formula down, paste it, and then,
of course, I could go back and
sum these individual components to put them together into my grand total.
So that's the way that you can actually force the decimal places off.