You've probably seen in Week 1 of the course that I have gone through a couple of different examples of putting in formulas and functions, but this screencast, I'm going to make everything official and go through how you can put Excel syntax into cells in Excel and I'm going to go over the order of operations of calculations in Excel. One of the main advantages of using Excel spreadsheets is they can do all different mathematical calculations. For example, I can add 4 plus 5, there's a built-in square root functions, you can take the square root of 45, if you need to. You can take numbers to different powers, so maybe 3 cubed. So there's all mathematical calculations you can do in Excel. All Excel formulas start with an equal sign and so you can just refer to cells, so I could add cells B3 and B2, if I wanted to. There's all different calculations that you can do. If you do put a formula inside a cell and you want to enter that, so equals 4 plus 5, you have to press Enter to signify that you're done entering that formula. Alternatively, you can go up here to something known as the Formula bar, so let's put a formula in here. You can go up to the Formula bar and you can start typing in, so maybe 4 plus 3 and when I type up here in the Formula bar, it's echoing down here in cell D7. If you want to just get out of there and you made a mistake, you can always press the Escape key, so Escape key, or if you start typing in formula, you can always go up here to the Formula bar and press Cancel. An alternative to pressing Enter, is just to select this checkmark and that's how you can signify that you are done. Common arithmetic operators in Excel, we have the plus sign that I've already talked to you about, we have the subtraction, so 4 minus 3. There's also division, which would be 4 forward slash 2, so that would be 4 divided by 2. We can also have exponentiation, which I've already touched upon and that's the caret or the circumflex. Then you can press Enter and that will do 2 squared. Excel has a specific order of operations and that is how it does its calculations, the order of its calculations. It turns out in Excel, negation is first, and I'll touch on that real quick and then exponentiation, then multiplication, division, and then plus and minus, so there's a big difference in the order of operations here. By the way, this is going to be the only screencasts in which I touch on complicated formulas in order of operations. So if you're not very good at math, it's not super important that you understand all this. So if it is just going over your head, then do the best that you can. Let's first talk about negation, Excel is quite unique in that negation comes first. Most people, if we took negative 3 caret 2, would assume that the 3 squared goes first, which is 9 and then the negation takes place. But Excel is unique in that negation comes before anything else. So here, Excel is going to take the negative 3 and then it's going to square it and so that's actually 9 and not negative 9. It's important to realize that all formulas in Excel are evaluated left to right and I'll go over some examples to show you that. If you want to group calculations together, you have to use parentheses. So let's go through an example here, we have this algebraic expression here, 1 plus 2 times 3, that's in the numerator and then in the denominator, we have 4 times 5. You all could probably do this in your head quite easily, the first thing you would do is you would multiply 2 times 3 to get a 6 and then you'd add 1 plus 6 to get a 7, then you divide by 4 times 5, which is 20 and that is actually 0.35. The way you would put this into Excel, you have to think about it though, because you need to group calculations, you need to group the numerator and you need to group the denominator. So you would use parentheses to group the numerator and then you'd write in 1 plus 2 times 3 and then you take that result that's denoted in parentheses and you divide by the denominator result, which is 4 times 5. So the order of evaluation in Excel would be to calculate the 2 times 3 result, which is 6 and then Excel would compute the 1 plus 6 result to get 7. It would then, because you have parentheses in the denominator, it would compute the 4 times 5 result to get 20. Then finally, you would divide the numerator result, which is 7, by the denominator result, which is 20, to get 0.35. This is how you do it in your head, when you write this out on a piece of paper. Some incorrect ways to write this out are the following, if you wrote 1 plus 2 times 3 divided by 4 times 5, this is not correct. Because really what you're saying is 1 plus 2 times 3 divided by 4 times 5, which is 1 plus 6 divided by 20 and that's 1.3, so that's not correct. Another incorrect way to write this would be 1 plus 2 times 3, divided by 4 times 5, because remember, Excel works left to right, so you would get 7 for the numerator result. But then it's going to take 7 and divide by 4 and that's 1.75. But then it's going to multiply by 5, because it's going to group that intermediate calculation together and then multiply by 5 and that equals 8.75 and that is not correct. By the way, an alternative correct way to write this would be 1 plus 2 times 3 in parentheses, divided by 4 and then you can divide by 5. What's happening is you're calculating this results and then you're dividing by 5, so really what that is, is the same as calculating 7 divided by 4 and then multiplying by 1 over 5, which is the exact same thing as what we did originally. So let me show you how to do this in Excel, we can just type in 1 plus 2 times 3, then we divide by 4 times 5 and press Enter and it calculates it to be 0.35. Now, I wanted to show you something that's really useful, especially when you are just learning Excel syntax. On the Formulas Tab here, if you go to Evaluate Formula, you can step through, so this is looking into the brains of Excel. You can step through to see how Excel is interpreting that formula. So this is really useful for troubleshooting, so I'm going to go ahead and click evaluate, you notice right now it's got the underline there, so that's going to be the next thing that's evaluated. Remember, it always does things in parentheses, left to right first, so it's going to calculate 2 times 3 to get 6. It's going to do 1 plus 6 to get 7 and then it removes a parentheses. It moves on to the next set of parentheses going left to right, 4 times 5 is 20, gets rid of the 20 and then does the final 7 divided by 20. Then you can close, so that's how you can see how Excel is performing your calculation. Let's go through another example here and you might want to just pause this screencast and work through this one on your own, remembering the order of operations. So when we go through this, the Excel syntax would be negative 2 squared plus 5, divided by square root of 3 plus 4 in parentheses. Now if you are uncertain of anything, you could add extra parentheses, adding extra parentheses does not penalize you, but it does make troubleshooting a lot harder when you have all these parentheses floating around. So the way that Excel evaluates this is it calculates the negative 2 squared result to get 4, it then adds the 4 plus the 5 to get a numerator result and then it computes the square root. So it computes functions to get 1.73 and then it adds that to 4 to get a denominator result. Then finally, it divides the numerator result of 9 divided by the denominator result of 5.73 to get 1.57. So here it is in Excel, I wanted to just mention one last thing. If you're in either up in the Formula Bar, or if you double-click in your formula and you do left and right, you can see that it'll highlight the parentheses that match, so making matching parentheses is really important. So if I'm way over here on the left, you see that the matching parentheses blink. So hopefully you guys learned a little bit more about Excel syntax and order of operations in this screencast. Thanks for watching.
