Excel 2010 Tutorial - SUM, Average, Max, Min Functions
In this video from the Excel 2010 Tutorial series, School of Technology Program Coordinator, Justin Denton, teaches how to find and use the SUM, Average, Max and Min Functions in Microsoft Excel 2010. This tutorial video is a great beginning step to your courses in technology at Rasmussen College.
So then we go into a little bit harder. We're going to start working with formulas a little bit. Has anybody used formulas in Excel a little? No? Yes? Cool.
So they're not as totally hard as they should be. And I'll explain it to you, and it'll make sense. The nice thing about formulas is you just need the basis of what you want to do. And that packet has a lot of the sample formulas, some of the key formulas that you would normally use. Now, you could make them really, really involved, and it'll take some time. But to at least get you just started, if I go into this Excel formulas file, you'll see I've got a subset of data here. Cool. I just got some numbers I want to work with.
You'll also see that my titles are already lined up with what is actually the formula. So I want to sum something. That's actually the formula that we're going to use.
So if I hit sum, the formula that I would use is just equal sum, and just think, when you're building a formula, and I'll show you right off to the side, when I'm building a formula, I'm going to always start that formula with an equal sign. It's just like a unique character that symbols to Excel that you're getting ready to build a formula.
So if I type equal sum, and then you want to start with a parentheses, because whatever is inside that parentheses is what you're going to do. So you're going to back to basic math. What was it? Please excuse my dear Aunt Sue, or Sally, or whatever it was back in the day. It works the same way. So I'm going to use that same principle.
So for the life of me, i can't remember is it parentheses, something, multiplication, subtraction, addition, and division? I'm missing the E. Is it equal sign or something like that? Exponent. Exponent it is. So I'm going to use that same principle. So this is where those cell references is going to come and play.
I'll have equal sum. I got my open parenthesis. I need to start out with what my formula is. So I just want to add whatever is in A4 and do the plus sign, that what I'm going to do. And I want to add that to whatever is in A13. I really don't care what's in those two cells. I just want to add those together.
So after you're done with that, we've created the formula, A4 plus A13. I'll end parentheses, which basically signifies the end of the formula. Hit Enter. And it'll automatically total it for us.
So if I go up here, change this to 12, it'll change to 43. If I change it to 112, it'll change it to 143. So it'll automatically keep track of that, because we use the cell references to create the formula.
And you can get a lot more involved and combine everything. So if we go into like subtraction, it's the same thing, a minus sign. That will subtract. So now, I'll subtract-- what is it-- A1 from A13 or A4 from A13, multiplication, asterisk. Division is the forward slash. It'll do division.
And then, at that point, you can actually start combining these into a really more in-depth cell or formula. So if I want to start out with A4, I'll do A4. then I'm going to do this a little different. I want to add it to, let's say, A5. And usually, this is where I'm going to work out is in the formula bar.
So I'm bracketing A4 plus A5, because I want to do that first. So according to those age-old rules of grade school mathematics, whatever is in the parentheses is what you do first.
So I'm going to do A4 plus A5. And maybe I need to divide that by A8, let's say minus A10. So I'm going to do the first set of parentheses, get my answer, do the second set of parentheses, get my answer, And then the third set of parentheses, which happens to be the division portion of it, and get my final answer.
So when I do that, it comes out to negative 26.4. But now, you're using those grade school mathematics and actually being able to build that formula out. So instead of using the actual values, instead of taking 112 plus 20 and divide that by 45 minus 10, I'm just using the cell references that actually have those values in them.
And by using those cell references, you've got a pretty fluid report. Because if someone comes in, maybe this statistic is up a little higher, I'll change it from 60 to 50. On the fly, it'll go ahead and recalculate that lengthy formula. The same kind of process works-- oh yeah?
Do you have to the auto brackets? Or does it do it according to the math protocol in that it'll do what's inside the inner brackets and then the overall--
It'll do according to math protocol.
You don't have to have a double bracket?
Which one? The one on the very outer edges? The outer edges is what's your function that you're building. So you always have to have that, because that's saying that anything inside of it is what you're going to do and then your other brackets is how to break it up and actually go ahead and go with math protocol at that point.
So if we go down to average, average really works the same way. So if you've got the principles of sum down, average works the same way, except you just do equals and type in average, if I spell it right. And then you do the same brackets.
If I wanted a range, I can just highlight a whole range of cells. You'll see it's a range because it'll say A4 colon to A13. Put an end bracket on that to say I'm done with my formula. And when I hit Enter, it happens to be the same one because I use the same formula before. It'll average that entire cell.
You could also get a little more detailed if you want to combine it. I want to find the average of A4 plus A5. Now, you can combine your normal sum formulas that you would've used a minute ago along with the actual average formula. So you can build out some pretty lengthy averages as well to kind of find out an average for home loan rates or something to that effect. And once we start getting through all these, a lot of these will work the same. So equals max, anybody got a guess what that's going to do?
Your highest number?
Find your highest number in a range. So whatever range I have, like in this instance, if you look up towards the formula bar here, equals max is A4 through the range A13. Pretty quick and easy. It will automatically find the highest range.
Ranges could be an entire spreadsheet. You want to just find the highest number in this one spreadsheet. You can build a range up to that fact.
Minimum. Same thing. Finds the minimum number.