Locked lesson.

Upgrade

About this lesson

Learn basic math functions including SUM, ROUND and SUBTOTAL.

Exercise files

Download this lesson’s related exercise files.

Math Functions.xlsx
15.3 KB Math Functions - Completed.xlsx
15.6 KB

Quick reference

Topic

Math functions.

Description

Basic math functions including SUM, ROUND and SUBTOTAL.

Where/when to use the technique

Use to add multiple cells together and return totals and subtotals.

Instructions

Ways to use the SUM function

=SUM(A1:A10)

Add up all cells from A1 through A10. (A1 up to A10.)

=SUM(A1,A10)

Add the individual cells separated by the commas. (Just A1 and A10.)

=SUM(A1,A5:A10)

Add A1 plus the range from A5 through A10)

Key points to remember:  There is no limit to how many cells or ranges you can add by appending cells or ranges separated by a comma.

The ROUND function

  • Takes a number, plus the number of decimals places
  • =ROUND(A1,2)   Will round the value in A1 to two decimal places

Key points to remember:  You should always round multiplication or division in Excel to make sure the value you see in the cell doesn’t have any hidden decimals that will influence your end result.

The SUBTOTAL function

  • Has many different “totaling” options, such as SUM, AVERAGE, MIN, etc…
  • =SUBTOTAL(9,A1:A10)    Sums up all numbers in A1:A10, unless they have been included in a previous subtotal
Login to download
  • 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.

Gift this course