Learn essential Excel calculations and enhance accuracy by learning proper formula techniques and Excel's order of operations. Gain proficiency to avoid common mistakes and streamline your spreadsheet workflows professionally.
Key Insights
- Begin every Excel calculation formula with an equal sign (=) to ensure Excel processes it as a calculation, rather than text.
- Reference cell locations instead of typing direct numbers within formulas to automatically update calculations when corresponding cell values change.
- Apply correct mathematical order of operations using PEMDAS (Parentheses, Exponents, Multiplication, Division, Addition, Subtraction) or use built-in Excel functions like AVERAGE to simplify complex calculations.
Note: These materials offer prospective students a preview of how our classes are structured. Students enrolled in this course will receive access to the full set of materials, including video lectures, project-based assignments, and instructor feedback.
Calculations. Let's take a look at how you can come up with answers to very simple calculations in Excel. I usually start off with this section by pretending that someone is new to Excel and they've only used the calculator.
Well, if I wanted to add 2 plus 2 on a calculator, what I usually do is type 2 plus 2 and then I may type the equal sign and then press ENTER. Now in Excel, this will not give me the answer. I'll be very disappointed not to see the result and I'll wonder what is all this fuss about Excel being very good at coming up with answers to calculations.
Apparently it's not doing a really good job here. Well, what we're not understanding about how this works in Excel is the first rule when you're writing formulas is all formulas must begin with the equal sign. Excel doesn't know that you wanted to actually come up with the answer.
It thinks that you just want to show people what 2 plus 2 looks like so it treats that value as text. If I want to write a formula, I must start with the equal sign. Then I can type in my formula, in this case 2 plus 2. When I press ENTER, now I will get the result.
Unfortunately, this is not the best practice when it comes to calculating a value. What I did is I typed numbers directly inside of the cell. I shouldn't do that.
What I should do is refer to cells that contain those values. If I start over, what I would do is go over to another cell and type in 2 and then another cell and type in 2 and then here when I rewrite my formula, it's going to be equal to this value plus that value. When I press ENTER, I get 4 which is the same result.
So someone might say, well what's the difference? I'll show you the difference and I'll tell you a story to illustrate this. Let's pretend there's someone who's about to do a presentation. They've told their intern to please add 30 plus 10.
The intern goes in and types equal 30 plus 10. They press ENTER and they get the result. Now the person doing the presentation is not in the room so this looks good.
They go and do the presentation and they tell their audience, I want to show you what we do to add numbers together in Excel. Here we have a perfect example where we're adding 30 plus 10. What if we increase the value of 30 to let's say 80.
This will automatically give me the result of 80 plus 10. Now the presenter has egg on their face. 80 plus 10 is not equal to 40 and they're wondering why the formula didn't update.
Well if they actually take a look at the values that are in the cell, they will see 30 plus 10. The original values that were written into the formula and don't change when you update the values outside of the formula. So I'll press escape and let's go back in time.
The person completing this exercise for the person who's doing the presentation would have typed equal and then they would have selected 30 because what you're saying here is I want whatever in that cell, whatever is in that cell to be equal to that value past, present, or future plus that value past, present, or future. When you press ENTER you get the same result but during the presentation if we change this to 80 it automatically updates. Now I'm going to continue and come up with the answer for 30 minus 10.
This will be equal to 30 which I'll select with my mouse. Enter the plus sign and then I'll select 10. Then I'll press TAB and I'll start using my keyboard.
I'll type in equal, press the up arrow twice, one, two, then enter the symbol for multiplication which is the asterisk. Then I'll enter in the value for 10 and then I'll press ENTER. Now I'm going to go back to the last exercise.
Force of habit. I need to subtract so instead of entering the plus sign here I'm going to enter the minus sign. That's going to fix that and then I'll press TAB.
30 minus 10 is 20. Here I want to be able to come up with the total amount of 30 divided by 10. I'll type in equal, select 30, divided by 10 and then press ENTER and I get three.
So those are basic calculations. Let's continue and take a look at the exercise below. Exercise two.
I want to come up with a total price of 10 apples at 50 cents, 15 oranges at a dollar, 20 bananas at 25 cents and 12 tomatoes at 45 cents. Now this calculation is a multiplication exercise so I'm going to type equal. I'm going to use the arrow key and go to the left twice and then I'm going to enter the asterisk and then select the amount for price and unit.
I'll press ENTER. Learning what I learned in autofill, I will not have to rewrite that formula. I want to replicate a pattern here and the pattern that I want to replicate is the multiplication of two cells on the left.
So I'll go to the black plus sign, I'll click and drag and I get my results right there. Now I'm going to briefly explain PEMDAS. PEMDAS stands for please excuse my dear Aunt Sally.
This is a phrase that teachers usually tell students to help them remember the order of operations, a mathematical concept that determines, that explains how calculations multiply, divide, add or subtract different values in a certain order. Now for this first value, I may think to myself that one plus one divided by two is going to be equal to one because if you add one plus one you get two and a two divided by two will give you one. That's actually not what's going to happen here because according to the order of operations, division comes before addition.
So what's going to happen here is one will get divided by two, it'll become 0.5 and then the answer will be 1.5 instead of two. Now if you're wondering why we're not seeing the answer here, well it's because I put a space in front of the equal sign. So the equal sign is not actually the first character.
Excel will treat this as text. We do this so you can see the value of the, so you can see the formula before we show you the value. So I'm going to take away the space, I'll press ENTER and I have 1.5. We left the original formula over to the right so you can see the formula that generated the result.
Now if I did want one plus one to get added first, following the order of operations, I would look to see what is higher up the chain than division that could help me here. The answer is going to be the parentheses. One plus one will give me two, then two divided by two should give me what I want, what I was originally looking for which is one.
So I'm going to take away the space and see what the result is. Backspace and press ENTER and that gives me one. What I usually say regarding this exercise is if an Accountant needed to multiply one million dollars by one of these two formulas, if they selected the wrong formula, they would make a five hundred thousand dollar mistake because they didn't learn something in grade school.
So in this exercise, next exercise, we want you to come up with the average for these four students. Now if you just rush into the exercise, you'll select 75, you'll add it to 78 and you'll divide it by two because that's how you come up with the average. You add the two values together and you divide it by the number of values.
I get 114. So that is not right. I should have remembered what I did in PEMDAS and I should have made sure to add the parentheses around the addition part of that formula.
That will give me a total number that will then be divided by two and I'll get 77. Then I can auto fill down and get my values here. I want it to be more exact.
I can increase the decimal and that's the actual average. Now going forward, you're not going to have to worry about PEMDAS that much because there is another way for you to get the answer. You're going to type the equal sign and you're going to type in this really weird word.
It's weird that you would have to type this, but I'm going to type it now to get the answer. Average. So you would actually type average to get the average.
You would select the first value, enter a comma, and select the second value. And when you press ENTER, you get the same average that you got using PEMDAS. But this is a little easier because you won't have to worry about the correct order of the plus sign and division symbol in the formula.
So that's basic calculations.