Guest Author - Chris Curtis
Operators are the keyboard characters that Excel understands to perform a mathematical calculation. These operators are familiar to you, as they are the same ones that you would use when working with a calculator. They are as follows:
^ Exponents (raise to the power of)
& Concatenate (combine text strings together)
Excel uses the same order of operations that you may have forgotten from your school days. Groan! You knew you should have been paying attention that day, didn’t you?
Here is an easy way to remember them.
Please = Parenthesis
Excuse = Exponents
My = Multiplication
Dear = Division
Aunt = Addition
Sally = Subtraction
Excel will calculate any part of the equation that is enclosed by Parenthesis first (beginning with the inner most parenthesis and working outward in more complex formulas). The second calculation will be any Exponents (i.e., when you raise a number to some power, squared, cubed, etc.) contained in the equation. The third and fourth calculations (made on the same level; calculated left to right) are Multiplication and Division. The fifth and sixth calculations (made on the same level; calculated left to right) are Addition and Subtraction. Effective use of parenthesis, even when not mathematically necessary, will make your equations easier to understand.
Let’s think through some basic math problems and you will remember exactly how easy this really is.
1) 2 + 6 / 2 = 5 –The division takes place before the addition so you would calculate it 6/2 = 3 +2
2) (2 + 6) / 2 = 4 - The 2+6 are enclosed in a parentheses thus are calculated first – 2=6 = 8/2
3) 4 + 23 / 2 = 8 – The 2 is raised to the 3rd power first (2x2x2 = 8) then the division comes next (8/2 = 4), then the addition (4+4)
4) (4 + 2) 3 / 2 = 108 - The 4 + 2 is enclosed in the parentheses thus is added 1st before being raised to the 3rd power (4+2=6) raised to the 3rd power (6x6x6=216), the division is last (216/2 = 108)
5) 12 + 60 / 2 – 3 * 22 = 30 - First calculate the exponent (2 square = 4) then the division and multiplication comes next (60/2 = 30) and (3*4=12); last calculate the addition and subtraction (12+30-12=30)
6) (12 + 60) / 2 – (3 * 22) = 24 – reason the answer out for your self.
Adding the parenthesis to the (3 * 22) part of this last equation is not mathematically necessary but it makes the formula easier to understand.
Very good! If you can handle those, then you understand the basics, which is all you really need. The concept doesn't change with the complexity of the formula.