Math Functions

Quick reference

• 00:04 In this video, we're gonna look at three of Excel's math functions.
• 00:07 We are gonna look at the sum function, the sub total function, and
• 00:10 the round function.
• 00:11 Now, the sum function you already know.
• 00:14 You've seen this before, it's equal sum open brackets.
• 00:17 And we can go and select a range of cells.
• 00:21 Close the brackets and it will obviously sum them up for us,
• 00:24 totalling them all for us.
• 00:26 I'm just gonna drag this guy across here,
• 00:28 cuz we're gonna use this a little bit more in a minute.
• 00:31 I'm also gonna go down and do the same thing down here.
• 00:35 And sum these cells and copy them across as well.
• 00:41 Now, what about when we wanna start sub totaling our data though?
• 00:45 We can do this in a variety of different ways.
• 00:48 We can use the sum function to subtotal our data
• 00:52 by providing the first subtotal comma the second subtotal.
• 00:56 So they're not contiguous, but we could join these together and
• 00:59 come up with the correct answer.
• 01:02 Another way that we could do is, we could say =sum, provide the first subtotal
• 01:08 comma, and then we could recommit the entire range that we've totaled up above.
• 01:13 Now, this would be an odd thing to do and it's really not recommended.
• 01:16 But the point that I wanna show you here, is that you don't have to have data ranges
• 01:20 that are the same size when you're working with the subtotal.
• 01:22 That doesn't matter, this will work just equally well.
• 01:26 It may be harder to maintain but it will definitely actually work.
• 01:30 Now, what if we wanted to actually make sure that we weren't
• 01:34 gonna lose out if somebody inserted a new row somewhere, and
• 01:37 we said, I really wanna sum all the numbers from this range.
• 01:41 You might think well no, that's not gonna work because we've got text in there, and
• 01:45 the sum function can't handle text.
• 01:47 In actual fact, that's not true.
• 01:49 If I close the bracket and hit enter,
• 01:51 you'll notice that it just ignores the text and sub totals everything else.
• 01:55 The problem though is that it adds
• 01:57 all of these numbers plus the sub totals that we've already done.
• 02:01 So we've essentially come up with twice the value that we originally wanted.
• 02:05 In order to fix this, we need to go back and divide it by two.
• 02:10 So you notice we can do subsequent operations on the functions we've used
• 02:14 that will give the correct answer to this end.
• 02:17 Though there's a better function a better way to do this, so
• 02:20 were gonna use the sub-total function in order to sub-total this data now.
• 02:26 Now, you'll notice it ask for function number you say well,
• 02:28 how do I know what function number to provide?
• 02:30 Well, that's where you can click on this little link where it says subtotal,
• 02:33 and it will pull up the excel help article that shows you the function number
• 02:39 as well as the function it actually pulls off.
• 02:41 So, if we scroll down this list we can see that sum is number nine.
• 02:48 So let's try that.
• 02:49 We'll go with subtotal nine comma and then it says where's the reference?
• 02:53 What's the range you're gonna use?
• 02:55 So we'll grab this range.
• 02:58 Close the brackets and say okay.
• 02:59 And it gave us the same total.
• 03:01 So let's try this too.
• 03:02 We'll say Command C, Command V, and
• 03:06 we'll just make sure that the range is the same because the number didn't change.
• 03:09 So that's all fine.
• 03:11 So what's the difference between the sum and the subtotal function?
• 03:14 Here it is right here.
• 03:15 If we go to subtotal nine, and
• 03:19 we provide that exact same range, close our brackets and
• 03:24 hit enter, you'll notice that this time it comes up with the correct answer.
• 03:28 I don't need to divide anything by 2.
• 03:29 And the reason for this is that the subtotal looks at all the cells and
• 03:33 says hey, this was fed into this subtotal function, and
• 03:37 therefore I'm not gonna add any of these.
• 03:39 I'm just gonna grab the stuff from the subtotal function.
• 03:41 So that's kinda nice, because then if something gets inserted in the middle that
• 03:45 isn't included in the subtotal, it will now get added into this particular value.
• 03:50 So that's a really useful thing with the subtotal function.
• 03:55 The last thing I wanna show you is what would happen if we wanted to round
• 03:58 this off.
• 03:58 We wanted this to be 13 or 1355.
• 04:02 How about 1356?
• 04:03 What we would do is we would actually wrap this function in a different function?
• 04:09 This is a concept called nesting.
• 04:12 So we type right after the = key the round function, open brackets and
• 04:17 we've now nested in our sum function in place of our number.
• 04:21 So we say well that's good, I know what the number is right now its 1355.5.
• 04:25 Say comma, it says how many digits would you like to round to?
• 04:28 I'm gonna choose zero, so that'll round to 1356 but I could easily go one, two,
• 04:32 three, depending on how many decimal places I wanted.
• 04:35 So we'll just say enter on that.
• 04:37 You can see that I've got 1356 is rounded off nicely.
• 04:41 This guy here has already rounded to a nice round number.
• 04:45 I could still go and copy this formula down, paste it, and then,
• 04:49 of course, I could go back and
• 04:51 sum these individual components to put them together into my grand total.
• 04:57 So that's the way that you can actually force the decimal places off.

Lesson notes are only available for subscribers.