Lets take a quick look at some math functions now.
Basic SUM and ROUND and we'll even take a quick look at SUBTOTAL. So what we have here is
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.
So we can do this by using the SUM function. We'll go across first, this is real easy. Type in =SUM
open our bracket and ask for a number it also takes a range, so we can go and select that
close our bracket and hit Enter.
We get 1788.86 and drag this guy down.
And because it's a relative formula you'll notice that it is actually picking up each individual row as we go down.
I can also use the exact same formula
copy it, paste it down into the second month's section and now it gives me the same thing;
a nice relative formula that covers off from columns B to F, no matter what row its on. That's perfect.
Now, what about vertical? Well sure we can do that too. Say =SUM
Open our brackets we will select our range of numbers
close our bracket, hit Enter so sum B4 through B8, Enter: 2409, perfect,
and we will copy that across as well.
And now we can copy this entire row again because it's relative because we have
five weeks here we have five weeks here we know the date is the same shape
so we can paste that directly into row 17 as well and we now have a fairly summarized table already which is great.
Now here comes the questions though. What do we do with our grand total? How do we actually make this work?
Well we could just say
=B9+B17
and that would give us a total of 4736. No problem.
Could we use the SUM function for this? We could try =SUM
open our bracket, we will sum the entire range of data and see what happens here.
Close our bracket and hit Enter
and it comes back with 10,887.
Well plainly looking at 2610 and 2832
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.
So why is it coming back with 10887?
The answer to that is that it's actually summing every single cell that makes this up
including the subtotals that come up with that value so plainly that's not going to work for us.
Is there an alternative for the SUM function that will actually not do that? Well there is, we can say =SUM
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.
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
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
Close the bracket and hit Enter
And it's $4500 which just happens to be the sum of these two numbers
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
=SUM(D9,D17) then you could put comma something else comma something else to add more. So just copy that across
Now what if we wanted to go and use the SUBTOTAL function?
Well the way that SUBTOTAL works is we take SUBTOTAL, it has a function number
so 9 is SUM. There's also all kinds of different ones but if I say 9,
oops, missed that. Let's say 9 comma
and we sum our entire range
What you'll see now when we close the brackets is it comes back with 9839 which
just happens to be, again if I select this entire thing, the entire range again. Well that doesn't work.
But what if I go back and actually nest a subtotal function in here.
With 9 comma
and we'll go from
E4 through E8 close the brackets, watch that subtotal in row 18
it immediately drops down.
Remember this is the same shape with five rows so if I copy this formula here to here
it is now 4919 which just happens to be the correct answer.
So what SUBTOTAL does is even though this is actually highlighting the
entire range, what SUBTOTAL does is it only sums up numbers that
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.
So that can work out well for us but you have to use subtotals all the way through.
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.
We are going to SUM
our two values comma
F9,F17 close and we will SUM it or ROUND it off to comma. How many decimal places?
Zero so when nesting a SUM function inside a ROUND function
close that bracket and this one will round off to $4639 even though it should end off in 0.5 too.
So it rounds it up. So that's some basic mathematical functions in Excel.