Excel 2010 Tutorial - Basic Structures of Formulas in Excel
In this video of the Excel 2010 Tutorial series, School of Technology Program Coordinator, Justin Denton, describes the basic structures of formulas in Microsoft Excel 2010. This tutorial video is a great beginning step to your courses in the School of Technology program at Rasmussen College.
Welcome to Excel 2010 Intermediate. What I wanted to do is kind of start out with formulas and talk about the basic structures of formulas.
So when you're working with formulas-- it sounds like a couple of you have already worked with formulas-- typically, how are you starting out your formulas?
With an equals sign.
With an equals sign-- got to do that. It won't work without the equals sign.
So we've got our equals sign. And then after you've typed your equals sign in, what are you typically typing?
Yeah, one cell divided by another cell.
So you're going to have some sort of cell reference, and then either divide it, multiply, add, subtract, whatever, by another cell reference. Is that the consensus, is how everybody's currently using formulas? Is there any alternatives that you might be using?
Well, you can put what you're doing in front of the equals sign or after the equals sign. My formulas have always been pretty simple, but I know that there's other ways.
No, that's perfect. That's where I was kind of leading to. Has anybody tried that, where you, let's say-- I throw some terms out there-- so if you do equals sum.
So you've used equals sum.
OK. And then after equals sum you have a bracket, and then you've got that same reference for the most part inside the bracket and then you end with another bracket. So we've got that.
There's quite a few others that we can work with.
So you've got equals sum, minimum, maximum, average-- there's a whole list of options.
There's no way we could cover it even in a day of all the various options that we've got for the equals formulas.
So what we're doing is we're doing equals sum. And when you work with the brackets-- we all have had grade school math, which I like the term.
And it's the order of operations that goes in play with creating that formula, because there has to be a rhyme or reason when you create that formula. Otherwise, it's not going to work right. You're always going to end up with the wrong value or something like that. It could have all the right cell references, but if it's not formatted out properly with the appropriate brackets and everything, then it's not going to calculate properly.
So has anybody ever seen PEMDAS?
Like, have you ever heard of a phrase please excuse my dear Aunt Sally or my dear Aunt Sue or something like that? So they used to do this-- maybe it was an '80s thing, but back in the '80s. Through some going to school and some students-- maybe it's the schooling system.
And it came down to what's called the order of operations. And what the order of operations is is, just to kind of keep in your mind-- it's got parentheses, exponents, multiplication, division, addition, and subtraction. And it's pretty much the way your formulas are going to work.
So when we work with these, we know that parentheses is going to come first. We build in and we start-- and this is why I like actually working with the equals sum function and getting into that habit, because when we get into the more advanced formulas, you're going to spend a lot of time with building similar formulas.
So if we try to build this formula out, we do equals sum, and we start with parentheses, because that instantly tells us that's the first order of operations. Anything inside the parentheses is what we're going to do first.
So if I wanted to make a little bit more of an advanced cell reference and do equals sum-- let's do A1 plus A2 minus A3 and then in parentheses, I know that I have to do whatever is in the parentheses first. And then based on order of operations, if I go back in here and I look at this, I need to do addition next. So I'm going to add A1 to A2, and then I need to subtract A3, which happens to be in the correct order here.
But if we want to make this a little bit more elaborate formula and we want to use multiple parentheses and multiple formulas, we can state-- I've got my first parentheses. I'm going to make another subparentheses in here and then do A1 times A2 and end that with another parentheses. So now I'm saying to the formula I need to do A1 times A2 before I do anything else. I can then do another multiplication and maybe multiply it by A3.
So I know at the same level multiply should be ran at the same time. But I know based on what's called the order of operations-- actually, I better end this-- is that I have to do whatever's in the parentheses first and then be able to multiply it.
If you wanted to make it a little trickier and say we're going to subtract it-- if we didn't have the parentheses here, if I took this parentheses out, took this one out-- based on A1 minus A2 times A3 and based on the order of operations, how would I calculate this formula? So I'll have A1 minus A2 times A3.
Let's see, you multiply A2 by A3, right?
Multiply A2 by A3, correct. After you've got your answer, then subtract A1, because that's how it's specified. Based on order of operations, I'm going to have to multiply these two and then subtract that from the answer.
But if you utilize and kind of inject parentheses into this-- and I'll bracket out A1 to A2-- it changes the whole entire result. Because if we do this, and let's say A1 is 1, 2, and let's do 4, then we're going to-- and the way you just stated would be 2 times 4 is 8 minus 1 equals 7. That's what our answer would be.
But if we inject parentheses into here, we know that we've got to first minus 1 from 2, which would end up being a negative 1, then multiply that times 4, which would equal negative 4. It would give you a totally different answer just by putting in the parentheses and following along with order of operations.
So when we're working with the formulas today, we're going to make sure that we-- you've got to keep an eye, per se, on how you build your formula, because it's so much of a dramatic result. 7 versus a negative 4 could be a mean of a profit versus a loss in a kind of accounting statement. So we could definitely run into some issues there.
We'll get into the minimum and the maximum and a few other formulas as well. And keep in mind when you're working with all of these, you can still utilize the same order of operations. Any of those ones that have brackets around them or even no brackets, if we were back up here to the original cell reference that we brought up where we did equals without sum and do A1 minus A2 times A3, it'll work the same way as the sum function. We've probably already done that before, so it just works the same way.
Just one of the things to keep in mind is when you do pull that, if you have a weird calculation that's coming out wrong, then you might go back and check the order of operations to make sure it's actually calculating out the way you want it to.