Transcript
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 a calculator. Well if I wanted to add two plus two 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+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+2 when I press Enter now I will get the result.
The Excel Way
Unfortunately, this is not the best practice when it comes to you 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. so 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 in 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 three zero plus ten 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 to the person completing this exercise for the person who's doing that presentation.
We 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. so now I'm going to continue and come up with the answer for 30 minus 10 this will be equal to 20 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 gonna go back to the last exercise as a force of habit. I need to subtract so instead of entering the plus sign here I'm gonna enter the minus sign. That's gonna fix that and then I'll press tab. 30 minus 10 is 20.
Multiplication & Division
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 3. so those are basic calculations. let's continue and take a look at the exercise below. exercise 2, I want to come up with a total price of 10 apples at $0.50 15 oranges at $1 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 a 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.
PEMDAS
Now I'm gonna 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 how calculations multiply, divide, add or subtract different values in a certain order. now for this first value, I may think to myself that 1 plus 1 divided by 2 is gonna be equal to 1 because if you add 1 plus 1 you get 2 and a 2 divided by 2 will give you 1. that's actually not what's gonna happen here because according to the order of operations division comes before addition. so what's gonna happen here is 1 will get divided by 2 it'll become 0.5 and then the answer will be 1.5 instead of 2. 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. I'm gonna 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 1 plus 1 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 1 plus 1 will give me 2. then 2 divided by 2 should give me what I want. what I was originally looking for which is 1 so I'm going to take away the space and see what the result is. backspace and press Enter and that gives me 1. what I usually say regarding this exercise is if an accountant needed to multiply 1 million dollars by one of these two formulas, if they selected the wrong formula they would make a 500,000 dollar mistake because they didn't learn something in grade school.
Average
In this next exercise, we want you to come up with the average for these 4 students. if you just rush into the exercise you'll select 75 you'll add it to 78 and you'll divide it by 2 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 a hundred and fourteen so that is not right. I should have remembered what I did in PEMDAS and math 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 autofill 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 gonna have to worry about PEMDAS that much because there is another way for you to get the answer you're gonna type the equal sign and you're gonna type in this really weird word it's weird that you would have to type this but I'm gonna 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 these 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.