WEBVTT
NOTE Copyright (c) GoSkills Ltd, 2013 - 2024
00:00:04.250 --> 00:00:05.750
Lets take a quick look at some math functions now.
00:00:05.750 --> 00:00:10.500
Basic SUM and ROUND and we'll even take a quick look at SUBTOTAL. So what we have here is
00:00:10.500 --> 00:00:17.750
we have a two month listing with the individual weeks and products and the actual sales in here and what we want to do is total these up.
00:00:17.750 --> 00:00:24.250
So we can do this by using the SUM function. We'll go across first, this is real easy. Type in =SUM
00:00:24.250 --> 00:00:30.000
open our bracket and ask for a number it also takes a range, so we can go and select that
00:00:30.000 --> 00:00:32.000
close our bracket and hit Enter.
00:00:32.000 --> 00:00:36.250
We get 1788.86 and drag this guy down.
00:00:36.250 --> 00:00:43.500
And because it's a relative formula you'll notice that it is actually picking up each individual row as we go down.
00:00:43.500 --> 00:00:45.750
I can also use the exact same formula
00:00:45.750 --> 00:00:52.250
copy it, paste it down into the second month's section and now it gives me the same thing;
00:00:52.250 --> 00:00:58.750
a nice relative formula that covers off from columns B to F, no matter what row its on. That's perfect.
00:00:58.750 --> 00:01:04.000
Now, what about vertical? Well sure we can do that too. Say =SUM
00:01:04.000 --> 00:01:08.250
Open our brackets we will select our range of numbers
00:01:08.250 --> 00:01:14.750
close our bracket, hit Enter so sum B4 through B8, Enter: 2409, perfect,
00:01:14.750 --> 00:01:19.000
and we will copy that across as well.
00:01:19.000 --> 00:01:22.750
And now we can copy this entire row again because it's relative because we have
00:01:22.750 --> 00:01:26.750
five weeks here we have five weeks here we know the date is the same shape
00:01:26.750 --> 00:01:34.750
so we can paste that directly into row 17 as well and we now have a fairly summarized table already which is great.
00:01:34.750 --> 00:01:40.250
Now here comes the questions though. What do we do with our grand total? How do we actually make this work?
00:01:40.250 --> 00:01:43.250
Well we could just say
00:01:43.250 --> 00:01:47.500
=B9+B17
00:01:47.500 --> 00:01:51.250
and that would give us a total of 4736. No problem.
00:01:51.250 --> 00:01:54.750
Could we use the SUM function for this? We could try =SUM
00:01:54.750 --> 00:02:01.000
open our bracket, we will sum the entire range of data and see what happens here.
00:02:01.000 --> 00:02:03.750
Close our bracket and hit Enter
00:02:03.750 --> 00:02:06.500
and it comes back with 10,887.
00:02:06.500 --> 00:02:11.500
Well plainly looking at 2610 and 2832
00:02:11.500 --> 00:02:16.500
when I go and sum these two together you can actually see from my tool tip text down on the bottom here it's only $5443.
00:02:16.500 --> 00:02:21.250
So why is it coming back with 10887?
00:02:21.250 --> 00:02:26.750
The answer to that is that it's actually summing every single cell that makes this up
00:02:26.750 --> 00:02:31.250
including the subtotals that come up with that value so plainly that's not going to work for us.
00:02:31.250 --> 00:02:39.500
Is there an alternative for the SUM function that will actually not do that? Well there is, we can say =SUM
00:02:39.500 --> 00:02:46.500
and you'll notice the tool tip text gives us number 1 and then comma and in square brackets it's got number 2. So 2 is optional.
00:02:46.500 --> 00:02:53.750
So if we go select our first number and then we hit that comma you'll see that number 2 now goes bold, it says that's what we're working with
00:02:53.750 --> 00:03:01.250
and we get an optional number 3. So we can keep carrying this chain on but I can actually go and click on this so I get sum, or D9,D17
00:03:01.250 --> 00:03:04.250
Close the bracket and hit Enter
00:03:04.250 --> 00:03:08.750
And it's $4500 which just happens to be the sum of these two numbers
00:03:08.750 --> 00:03:16.250
So this is very similar to what we see here using =B9+B17 this is just the way that we do it using a SUM function
00:03:16.250 --> 00:03:23.750
=SUM(D9,D17) then you could put comma something else comma something else to add more. So just copy that across
00:03:23.750 --> 00:03:28.500
Now what if we wanted to go and use the SUBTOTAL function?
00:03:28.500 --> 00:03:33.000
Well the way that SUBTOTAL works is we take SUBTOTAL, it has a function number
00:03:33.000 --> 00:03:39.000
so 9 is SUM. There's also all kinds of different ones but if I say 9,
00:03:39.000 --> 00:03:42.500
oops, missed that. Let's say 9 comma
00:03:42.500 --> 00:03:46.250
and we sum our entire range
00:03:46.250 --> 00:03:51.750
What you'll see now when we close the brackets is it comes back with 9839 which
00:03:51.750 --> 00:03:57.250
just happens to be, again if I select this entire thing, the entire range again. Well that doesn't work.
00:03:57.250 --> 00:04:02.500
But what if I go back and actually nest a subtotal function in here.
00:04:02.500 --> 00:04:05.250
With 9 comma
00:04:05.250 --> 00:04:06.500
and we'll go from
00:04:06.500 --> 00:04:11.750
E4 through E8 close the brackets, watch that subtotal in row 18
00:04:11.750 --> 00:04:13.750
it immediately drops down.
00:04:13.750 --> 00:04:19.250
Remember this is the same shape with five rows so if I copy this formula here to here
00:04:19.250 --> 00:04:23.750
it is now 4919 which just happens to be the correct answer.
00:04:23.750 --> 00:04:27.500
So what SUBTOTAL does is even though this is actually highlighting the
00:04:27.500 --> 00:04:31.250
entire range, what SUBTOTAL does is it only sums up numbers that
00:04:31.250 --> 00:04:39.000
are used in a later calculation. So it's going to pick up the subtotals but it won't pick up the things that fed into them which is kind of nice.
00:04:39.000 --> 00:04:43.000
So that can work out well for us but you have to use subtotals all the way through.
00:04:43.000 --> 00:04:49.500
The last thing I want to look at is what happens if we want to round this off? The way we do that if we say =ROUND.
00:04:49.500 --> 00:04:51.500
We are going to SUM
00:04:51.500 --> 00:04:54.250
our two values comma
00:04:54.250 --> 00:04:59.000
F9,F17 close and we will SUM it or ROUND it off to comma. How many decimal places?
00:04:59.000 --> 00:05:03.500
Zero so when nesting a SUM function inside a ROUND function
00:05:03.500 --> 00:05:11.000
close that bracket and this one will round off to $4639 even though it should end off in 0.5 too.
00:05:11.000 --> 00:05:15.000
So it rounds it up. So that's some basic mathematical functions in Excel.
NOTE Copyright (c) GoSkills Ltd, 2013 - 2024