Math Functions

Quick reference

• 00:04 Let's look at some basic math functions now.
• 00:07 We've got a couple of listing for products over five week periods.
• 00:11 Now, these two sets of data are relatively the same shape,
• 00:15 which is gonna make some of this a little bit easy.
• 00:17 What we're gonna do,
• 00:18 is we're gonna start by summarizing all of the first week's products here.
• 00:22 And to do that, of course, we'll use the SUM function.
• 00:24 So I'll type in =SUM, Tab, and we'll go and we'll grab this entire range of data,
• 00:31 from B6 to F6, close the parenthesis, and hit Enter.
• 00:36 And we'll now take this and we'll roll it all the way down into the five cells here.
• 00:41 And then, of course, because the next table is the same shape,
• 00:45 we can copy this, and we can paste it right here as well.
• 00:49 And at this point, we've now got some good totals that are moving across that
• 00:52 are nice and consistent.
• 00:54 I'm also gonna come across over here and
• 00:57 I'm gonna sum the data that's above it as well.
• 00:59 So we'll say, SUM, tab and we'll grab the mouse and
• 01:03 select this range, close the parenthesis and hit Enter.
• 01:08 We'll drag those formulas across and
• 01:11 then again because the data is exactly the same shape, it's five rows in both cases.
• 01:15 We can go copy it and paste it here.
• 01:19 So that's nice and easy.
• 01:20 We've got a pretty consistent setup at this point.
• 01:22 But what I really wanna focus on is actually the totals section and
• 01:25 let's see what happens here.
• 01:27 I'm gonna select my cell down in B20, and I'm gonna press the Escape key to
• 01:32 get rid of the marching ants that are surrounding the B11 through G11 range.
• 01:37 Now, I have a few different ways that I could add these things up.
• 01:40 The first thing I could do is I could say equals and I could grab B11 and
• 01:46 add B19, and say Enter, and that'll work just fine.
• 01:53 I can get the same result by doing something similar.
• 01:56 I can say equals SUM, tab, and
• 02:01 then we can grab C11, and C19.
• 02:06 Again, the functionality of what you just saw with the plus versus the sum of 2
• 02:10 individual components, it works out the same.
• 02:14 This is just a stylistic thing, I personally like the SUM function because
• 02:17 it shows intent, which is why I actually use it.
• 02:20 And I can say Enter, and what you can see is that we get 5,443.
• 02:25 And if I were to grab these two components right here,
• 02:28 in my status bar, you can see that it's totaling up nicely.
• 02:31 So I could very easily go and drag these guys across and
• 02:35 everything would work great.
• 02:37 Now, I wanna show you something else though.
• 02:40 What if I were to grab something and say =SUM.
• 02:43 And I were to sum up this entire range, what would happen?
• 02:48 We hit Enter.
• 02:50 We'll takes us to $9,030, and if I press Ctrl+Z,
• 02:53 we can see that that obviously isn't the same as what we had.
• 02:56 As a matter of fact, it was exactly double, why?
• 02:59 Well, because it was summing the entire range including these totals that had
• 03:03 actually been summed below.
• 03:06 Now, I wanna show you another function that's kind of interesting and
• 03:09 it works slightly differently way.
• 03:11 This one here is called SUBTOTAL.
• 03:14 So I'm gonna arrow down and I'm gonna grab subtotal and tab.
• 03:18 And the first thing it does, it asks me for a function name and
• 03:20 you'll see that there is averages, and counts, all kinds of stuff.
• 03:24 The one that I actually want is this one here, number 9 for sum, so
• 03:27 I can click on it, double-click on it, or actually take it.
• 03:31 And then it says, where is the range that you want to subtotal?
• 03:35 I'm gonna say, you know what, let's grab this entire range of data to E5 to E19,
• 03:41 close the parentheses, and hit Enter.
• 03:44 And you'll notice that it gives me $9,800,
• 03:46 which obviously is more than the 2 subtotals that I had.
• 03:50 Again, it's double.
• 03:51 So you go well, but you just did that with a SUM function,
• 03:53 why would you do it with subtotal?
• 03:55 Well, I'm gonna show you.
• 03:56 There's something really interesting about this particular function.
• 03:59 If we go with SUBTOTAL, tab, I'm going to type in 9 for
• 04:04 sum, and I'm gonna sum the data it goes from this area here.
• 04:11 And what I want you to watch is when I hit Enter on this,
• 04:14 I want you to watch what's gonna happen down in cell E20.
• 04:17 If I go and hit Enter, you'll notice that it drops to 77.29.
• 04:21 Now it's still obviously not right.
• 04:24 But if I replace this function, the SUBTOTAL 9 for a sum,
• 04:29 and I grab this range close the parenthesis and hit Enter,
• 04:34 you'll now notice this two values sum to 4,919.
• 04:38 Now this obviously did not happen with the SUM function in place because it just
• 04:43 summed everything, but SUBTOTAL doesn't include previous subtotals.
• 04:47 Why is that important?
• 04:49 It's important because of this, when we look at this subtotal, the range
• 04:54 is from where I have this D here, but it doesn't pick up the cell above.
• 04:58 This subtotal is picking up this area, so
• 05:00 it's also not picking up this particular cell.
• 05:03 But this subtotal is, because it picks up this entire range.
• 05:07 So here's what's interesting, is if I go and type $100 in here,
• 05:12 it picks up in the total even though it's outside the subtotal ranges.
• 05:14 So if it's picked up in a subtotal, it doesn't get added again, but
• 05:18 if it isn't, it'll get picked up, which is kinda nice.
• 05:22 One more function I wanna focus on, I'd like to show you about rounding inputs.
• 05:25 When I see a lot of people do is they'll just drop decimals off things.
• 05:28 But if you look, and you copy and paste, this still has those decimals on it, so
• 05:32 that's not a good thing to do.
• 05:34 If you wanna round a number off, we can actually use a special function for
• 05:38 this called ROUND.
• 05:40 And what ROUND does, I'm gonna hit tab right now,
• 05:43 you'll notice it puts in my parenthesis.
• 05:45 It says, what's the number you want to round and
• 05:47 then comma, how many digits would you like to round it to?
• 05:50 I'm gonna round this to zero decimals.
• 05:52 And at this point, what you'll see is that not it gives me a zero decimal number,
• 05:57 which is rounded off correctly.
• 05:59 So that's the rounding function, and the SUM function and
• 06:01 the SUBTOTAL all working in different ways for some basic math.

Lesson notes are only available for subscribers.