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.

Transcript

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?

The cell.

The cell?

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.

Oh, yes.

So you've used equals sum.

[INAUDIBLE].

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?

No.

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.

Transcript

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?

The cell.

The cell?

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.

Oh, yes.

So you've used equals sum.

[INAUDIBLE].

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?

No.

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.

Kendall Bird

Kendall is a Social Media Strategist at Collegis Education who is focused on bringing awareness and engagement to Rasmussen College's social media properties. She is passionate about helping others, the power of education and building strategies that put the needs of students first. 

writer

Related Content

This piece of ad content was created by Rasmussen College to support its educational programs. Rasmussen College may not prepare students for all positions featured within this content. Please visit www.rasmussen.edu/degrees for a list of programs offered. External links provided on rasmussen.edu are for reference only. Rasmussen College does not guarantee, approve, control, or specifically endorse the information or products available on websites linked to, and is not endorsed by website owners, authors and/or organizations referenced. Rasmussen College is a regionally accredited private college and Public Benefit Corporation.

Add your comment

*

Please enter your name.

*

Please enter your email.

*

Please enter your comment.

Take the Next Step—Talk to Us!

Request More Information

Talk with a program manager today.

Fill out the form to receive information about:
  • Program Details and Applying for Classes 
  • Financial Aid and FAFSA
  • Customized Support Services
  • Detailed Program Plan

Step 1 of 3

What's Your Name?

Please enter your first name.

Please enter your last name.

Step 2 of 3

Contact Information

Please enter your email address.

Please enter your phone number.

Please enter your five digit zip code.

Step 3 of 3

Program Preferences

Please choose a school of study.

Please choose a program.

Please choose a degree.

By requesting information, I authorize Rasmussen College to contact me by email, phone or text message at the number provided. There is no obligation to enroll.

icon-colored-advance icon-colored-build icon-colored-certificate icon-colored-growth icon-colored-national icon-colored-prep icon-colored-regional icon-colored-state icon-colored-support logo-accreditation-acen logo-accreditation-ccne ras-logo-flame ras-logo-horizontal ras-logo-stacked icon-filter icon-info-circle icon-mail-forward icon-play-solid icon-share-square-o icon-spinner icon-tag icon-general-connect icon-general-degree icon-general-discuss icon-general-email icon-general-find icon-general-laptop icon-general-leader icon-general-map icon-general-paperwork icon-general-phone icon-general-speak-out icon-simple-chat icon-simple-desktop icon-simple-find icon-simple-hamburger icon-simple-phone icon-testimonial-quotes icon-social-facebook-square-colored icon-social-facebook-square icon-social-facebook icon-social-google-plus-square icon-social-google-plus icon-social-instagram icon-social-linkedin-square-colored icon-social-linkedin-square icon-social-linkedin icon-social-pinterest-p icon-social-twitter-square icon-social-twitter icon-social-youtube-play-colored icon-social-youtube-play icon-util-checkbox-white icon-util-checkbox icon-util-checked-white icon-util-checked icon-util-chevron-down icon-util-chevron-left icon-util-chevron-right icon-util-chevron-up icon-util-open-window-button icon-util-open-window-link icon-util-pdf-button icon-util-pdf-link icon-util-refresh icon-util-x