Before we got into creating calculations inside of Excel I mentioned as long as you know what you want to do addition subtraction and multiplication division, so you understand the basics of the math. Excel does the rest for you but there is something else that we need to be aware of and this is going to take us back to our early days of math.
Go through what happens first. What if you have got a more complex calculation it is got some addition and multiplication in there, what takes place first.
This is the order of operation
Now I am going to give you a little acronym one that you may recall from your elementary days of school. Maybe not, Took me a little wild kind of dig that back up from my past. I am going to introduce you to it here and hopefully spark a little bit of thought that you had before.
open in front of you.
I have got a completed exercise file that is called OrderOfOperation-01, It is an Excel document and it is a part of this lecture you can download it at the link in this book.
Now it is a simple one, Not much going on in here, I got an imaginary Bill, It is got three items in it. 100 dollars for the first one 150 for the next and 55 for the third item, We have a subtotal, I want to get a sum of all those values and then we got a total. Where I want to get the sum of the items plus the tax.
So how do we go about completing this.
What if I hop into Cell C6 and I am just going to do some basic arithmetic, I mean and do some additions C6.
So, I set in cell C6 “=C3+C4+C5”, then hit Enter key
Then we got we have got our 305 worth of those three items.
Now I want to get the grand total which includes the eight percent tax. It is another formula I am a set of Cell C8, I set C8 =C6*C7, then het Enter key.
We can go about completed this one a couple of different ways, I am going to show you just one way here. You may have other ways that you want to accomplish this through whatever method, The important part is the order that the operators go through, what happens first then what happens next.
Some editing back in here I mean to say equals calculation.
I want to take C6*C7, So I am going to say plus one point zero. So, essentially, we are going to take the 8% and add one or add 100% to it. we will end up with 100 and 8%. then hit Enter key.
What happened there 25.40.
We wanted that 305 + 24.40 that we saw earlier. So, what is happened?
I am going to go to my formulas tab top your screen we are going to talk much more in depth about the formulas tab as we continue later into this book. But on the formulas tab you have a section called formula auditing and there is so many goodies in there.
I am going to introduce you to one of them here called Evaluate Formula. So I have cell C8 a formula cell currently selected then I am going to go to evaluate formula and this is going to allow us to evaluate this formula right here, It is going to allow us to evaluate a piece at a time and figure out what is going on.
Click Evaluate Formula then open-up my Evaluate Formula window.
In here I have an evaluate button and currently you can see my formula here C6*C7+1
So it is going to evaluate C6 that is underlined denoting that that is going to get evaluated first, then I am going to hit Evaluate button, C6 evaluated then it is going to evaluate C7, C7 evaluated 0.08
Next you can hit Evaluated button to see the final result
So, I mean I write something here, so Please Excuse My Dear Aunt Sally.
Here is an acronym for the you know here.
This is what I was taught going through grammar school and it stuck with me somewhat.
It is an acronym and it stands for the order of operation.
It is Parentheses Exponent Multiplication Division Add Subtract.
So, this is the order that your mathematical operators will go through the precedents that they take. Parentheses happens first, then you are exponents, then your multiplication division and then your adding and subtract them.
Now in some regions and this was brought to my attention I was teaching Excel one day and somebody said, I cannot remember where they were from where originally a native North Americans they came they came from wherever whatever other country and they introduced me to another acronym. Same thing but they just call them something a little different. And I know multiple countries use this, but this was an I go look and see it was BODMAS, so we got Brackets Orders then your Division Multiplication and you are Adding Subtraction.
So, brackets were the same as being parentheses.
Orders was the same as exponents.
Then you and your multiplication division and then you are adding subtraction.
Excel follows the principle: multiplication, division first and addition, subtraction, which is the same principle in math.
If there are parentheses in your formula, the elements in parentheses will be calculated first
Let us take it back to our formula at cell C8
How do we get the addition to happen first parentheses, here I am going to wrap this inside of princes, Get that to happen first.
Then hit Enter key, then Select cell C8 and click Envalute Fomula on Fomulas Tab to see the order of this operation
C6 is underlined, which means that the operator in cell C6 will be executed first, click Evaluate button to see the operation order of formular.
That is your order of operation, Very important.
Again, as long as we understand what we want to have happen Excel does the math for us, but we need to be aware of the order, The operators go through.
Just remember that Please Excuse My Dear Aunt Sally parentheses exponent motivation division Add subtract.
Try it out, jump in and I could download this file it is called OrderOfOperation-01, you could try it on your own and get a feel for it understand why Excel needs it done that way. That is just math in general not just Excel uses math but the order of operation is universally within math, try it out.
You can get paperback version of this book at here: