Math Functions

Quick reference

• 00:04 All right, let's look at some basic math functions here and
• 00:07 different ways we can total data.
• 00:10 We'll start with this one right here.
• 00:12 So what we're going to do so we're going to hit AutoSum, and
• 00:15 we'll sum it using the sum of B6 to B10.
• 00:17 All right, we could, of course, write this
• 00:22 formula manually with =sum(C6-C10).
• 00:27 We can also do this, we can say Alt =, and Alt =, and Alt = is a shortcut for
• 00:33 the AutoSum, which will actually give us sum of D6 to D10.
• 00:37 And naturally, of course, we could also use a fill handle and
• 00:41 copy this across or use Ctrl+C or Ctrl+V to get there as well.
• 00:45 Now, interestingly enough, we can also use Alt = when we're in a column to
• 00:50 the right-hand side here, and it will actually build our formula for
• 00:54 us looking to the left for a contiguous range of data.
• 00:58 And of course, once again, we could always =SUM,
• 01:01 write it manually in order to make it work as well, and so forth.
• 01:05 So there's a lot of different ways to be able to write these formulas to get this
• 01:09 stuff in here.
• 01:10 Now, I'm going to go and quickly cheat on this one a little bit here.
• 01:14 I'm going to copy Ctrl+C, Ctrl+V,
• 01:17 all of these sum functions down here across the bottom.
• 01:20 And in addition to that, I'm also going to go and put a sum function for
• 01:24 this data here in as well.
• 01:26 So I'm going to go and select all of this, and I'm going to say Alt =.
• 01:31 And you'll see that what's actually happened here is that I've selected
• 01:34 multiple cells and typed in Alt =.
• 01:36 And it actually gave me a sum function that is picking up each cell above
• 01:41 it all the way across.
• 01:42 So if you feel more comfortable writing them individually, that is totally fine.
• 01:47 If you want to get tricky, select multiple cells, say Alt =,
• 01:51 just look at the formula afterwards to make sure that it's doing
• 01:54 what you actually believe that it should be doing.
• 01:57 All right, now, what I really want to focus on here is the different
• 02:02 ways to add up these non-contiguous cells of data.
• 02:06 So we're going to start this way here.
• 02:08 What most Excel users learn at the beginning is a simple function
• 02:13 like this =, B11+B19, Enter.
• 02:20 Does it work?
• 02:21 Absolutely.
• 02:23 Let's take a look at another one.
• 02:25 = sum(, we know we can put in number one, number two.
• 02:31 We're going to grab this one, comma, and this one here, Enter.
• 02:36 It will also work and give us the answer that we're looking for,
• 02:40 which is great, okay?
• 02:41 Now, personally, if I'm going to be doing something else,
• 02:44 I would probably use the sum function in this way.
• 02:47 But I want to show you another function set as well that we can use for
• 02:50 some things.
• 02:51 But I'm going to have to do this just a little bit differently
• 02:53 than what I've done before.
• 02:55 So I'm going to actually do this in,
• 02:57 I'm going to do it right over here in column D.
• 03:00 I'm going to write a different function here.
• 03:01 This one here is going to be SUBTOTAL.
• 03:05 Now, SUBTOTAL comes back and asks you what function you want to work with.
• 03:09 I'm going to choose 9 for a SUM.
• 03:12 And notice that we can do averages and counts, all kinds of things.
• 03:14 But we're going to grab SUM, you're going to say comma, and it says,
• 03:18 what's the reference that you're looking to sum?
• 03:20 So I'm going to sum all of these, just like I would using a SUM function,
• 03:25 and it gives me the same result.
• 03:28 And we can actually prove that by saying Ctrl+C.
• 03:31 It's a fully relative formula, so I'm going to paste it here.
• 03:34 We're at 2809.36, we've got the same values, and it's picking up nicely here.
• 03:42 Why would I do that?
• 03:43 Well, it's because I can also use my SUBTOTAL on the very bottom row.
• 03:50 With a SUBTOTAL(9 for a sum, now I can pick up this entire range,
• 03:56 close parenthesis, and hit Enter, and I get 4,919.
• 04:02 Now, the interesting thing here is if you add 2,110 and 2,809, you get 4,919.
• 04:08 The question is, why would you do that?
• 04:10 And it comes down to this.
• 04:12 If I put $1,000 dollars here, notice that my grand total does not change.
• 04:17 But if I put $1,000 here, my grand total does.
• 04:21 It may not be picked up in my subtotals, but
• 04:24 because my SUBTOTAL9 is picking up this entire range, this gets picked up.
• 04:28 Remember, this guy here is only summing these two cells, and
• 04:32 they're only pointing at the data that's in here.
• 04:35 What's nice about the subtotal is, you point it to an entire range and
• 04:38 any subtotals that are in there don't get re-added back in with the original data.
• 04:43 Or at least the original data that feeds the subtotal is excluded,
• 04:47 which is kind of nice.
• 04:49 Another function that we might want to use is we might want to
• 04:53 say let's do a sum of this one and this one again.
• 04:58 But what if we wanted to round this off, because right now, it's at 4638.52?
• 05:05 Well, I'd like to round it to zero decimal places.
• 05:08 We can do this by actually nesting a function within a function.
• 05:12 So here's what I'm going to do, is I'm going to come to the formula bar,
• 05:17 and I'm going to wrap my SUM function with a ROUND function.
• 05:22 So I'm going to type in ROUND, that's the function name.
• 05:24 I'm going to open the parentheses, and
• 05:26 you'll notice that the ROUND function says, give me a number.
• 05:30 Well, I'm saying the number that I want is the sum of F19 or F11 and F19.
• 05:35 That's the first parameter.
• 05:37 I'm going to hit comma, and
• 05:38 then it asks me how many digits would I like to round it to.
• 05:42 And I'm going to round it off to 0 decimal places, and close the parenthesis.
• 05:47 And what you'll notice is that we now get 4639.00.
• 05:51 Now, I'm going to press Ctrl+Z and undo this for a second.
• 05:54 Notice 4638.52, I'm going to press Ctrl+Y to reapply the rounding function.
• 06:01 And there we go, it's now rounded off quite nicely there.
• 06:04 So there's a few different ways to actually go and sum these things.
• 06:09 We can sum them using the SUM function with non-contiguous ranges.
• 06:13 We can use things like the SUBTOTAL function here, which picks up things that
• 06:17 are being inserted in between,, and we can also round things off.
• 06:21 So that's some of the basic mathematics functions that you might be interested
• 06:25 when working with data to dynamically solve it or to dynamically sum it and
• 06:29 update it.
• 06:30 because the beauty of this, of course, is that if anything changes in here,
• 06:34 all these totals will update to be exactly right.

Lesson notes are only available for subscribers.