Math Functions

Quick reference

• 00:03 Let's take a quick look at some math functions now.
• 00:06 We have two sets of product data, both for five weeks and five products.
• 00:10 So, they're the same kind of shape.
• 00:12 We'd like to summarize these numbers.
• 00:14 So we start by saying =SUM.
• 00:16 And we'll select the numbers in week one for
• 00:20 products A through E, close our parenthesis and say, Enter.
• 00:23 And that gives us our nice total
• 00:25 that we're pretty happy we can rely on as correct.
• 00:28 I'm going to left click and drag the fill handle down to extend this.
• 00:32 Then in addition, because I know these formulas are relative and
• 00:35 the data's the same shape,
• 00:37 I can copy and paste it down here because I know that these will work
• 00:42 to keep consistent sums which is good.
• 00:46 I'm also going to go and summarize the numbers up above here as well.
• 00:50 So, we'll sum all of the items for product A and again we can left click and drag.
• 00:59 And because we know we have five products in the next area,
• 01:02 we can paste this data in here as well.
• 01:05 As you can see everything is working quite nicely.
• 01:09 Now, what we want to focus on though is the total, the grand total.
• 01:13 So how can we go about doing this?
• 01:15 Well there's a few different ways.
• 01:17 We could say equals A9 plus B17.
• 01:22 It's rather B9 plus B17 and hit enter and that will work out quite nicely.
• 01:28 And we can check from the IntelliSense that it actually is working for us.
• 01:32 Another way that we might try and
• 01:34 approach this, is to actually use the sum function to sum these numbers.
• 01:38 Your first intent might be to do something like this where we sum the entire range.
• 01:43 Say okay, but if you look at this number now, I see 10,887.
• 01:46 That's a little bit big.
• 01:48 When I take these two and actually sum those together, I come up with 5443.50.
• 01:53 Which is exactly half of this number.
• 01:57 The reason is, because this function is actually summing everything.
• 02:01 But these numbers here have already been summed into this area, and
• 02:06 these numbers have already been summed into this area.
• 02:07 So, essentially we end up with double, so that won't work for us.
• 02:10 Let's look at a way that does.
• 02:13 We can still use the SUM function by saying =SUM.
• 02:16 You'll notice in the IntelliSense we've got Number 1, Number 2,
• 02:19 so we can provide multiple options here.
• 02:23 Instead of using a range, we'll grab a specific cell.
• 02:26 We'll hit comma and it says what you want for number two, we'll say this one here.
• 02:31 Close our parenthesis and say enter.
• 02:33 We now get 4512, which you can see is the exact number that we're actually after.
• 02:40 So, let's drag this guy across here and say okay.
• 02:45 And I want to show you one more function here.
• 02:46 That's actually specifically targeted to subtotals and totals and
• 02:50 that is the subtotal function.
• 02:53 So, I'm going to hit tab to complete that.
• 02:55 And you'll notice that right away, it comes up with a list and
• 02:59 this is the function number that we want it to do.
• 03:02 And what we'd like, is we'd like number nine for sum.
• 03:05 You can also use this to average or count or max or whatnot.
• 03:07 But we're going to say number nine, so double click on that, and
• 03:11 it says that's the function number.
• 03:13 Now where's the reference that you'd like to subtotal?
• 03:15 Let's say, well, I'm going to take this entire set of cells,
• 03:20 close my parenthesis and say Enter.
• 03:23 And you're thinking 9839, that seems too high, well indeed it does.
• 03:26 Because again, it's exactly double the sum of these.
• 03:30 So you say, well why would you use subtotal then?
• 03:32 I mean it works the same as sum.
• 03:34 Not exactly, watch this now.
• 03:36 We're going to go and replace this sum with a subtotal.
• 03:41 We're going to use function number nine to sum and
• 03:43 we're going to sum the values up above.
• 03:46 Close our parenthesis and say equals but before I hit Enter here,
• 03:50 what I'd like you to do is I'd like you to watch cell E18.
• 03:54 When I hit Enter, that number drops and that's interesting.
• 03:59 Let's try this here as well.
• 04:01 We'll say, equals subtotal 9 comma and
• 04:05 we'll grab the cell range above us.
• 04:10 Close our parentheses, and say equals and now, we come to 4919.66.
• 04:15 So, if I grab these two cells and check, that is indeed the number.
• 04:20 What's happening here, is that subtotal works a little bit differently than sum.
• 04:24 What subtotal does is it looks at the entire range that you've selected and
• 04:29 it finds any cells that have subtotals.
• 04:31 And it ignores the cells that are fed into them.
• 04:34 So, that's kind of cool, because if somebody adds another value,
• 04:37 that hasn't been included in the sub total
• 04:38 it will get picked up in this number. As you can see here,
• 04:41 if I were to add 10 it increases.
• 04:46 Because it's outside of a range that's already been subtotaled, so that's cool.
• 04:50 Subtotal is a great function for using in those scenarios.
• 04:53 Now, I'd like to round the sum of the next two numbers off.
• 04:58 So, in this case here,
• 04:59 what I'm gonna do is I'm gonna type in equals round open brackets.
• 05:02 And it says, what's the number you'd like to round?
• 05:06 I say well, I already know that SUM of this comma,
• 05:12 this gives me the subtotal, so I close the brackets there.
• 05:16 So, I can use the SUM function as a number in my rounding input.
• 05:20 Then, I can say comma.
• 05:21 How many digits would I like to round it to?
• 05:23 And we'll say zero and close our parenthesis.
• 05:27 This is what we call formula nesting,
• 05:28 where we nest one function inside a formula.
• 05:32 We'll now say enter, you'll notice we get 4639.
• 05:36 If I take a look at these two, it's 4638.52, it's rounded up to 4639.
• 05:41 So, there's a few different functions, different ways we can use them
• 05:46 in order to do some basic summarization and math concepts inside Excel.

Lesson notes are only available for subscribers.