Many Excel formulas include arithmetic operators such as the plus sign and the
minus sign, but Excel doesn't just read your formula from left to right and
take the operations in order. Instead Excel has a strict order in which it
performs the operation. This worksheet displays the list of operators
you can use in Excel and the order in which it processes them.
I'll explain what the symbols mean and how they work within formulas.
First, Excel checks whether a value is negative. That is if it has the minus sign in front
of it. After that it checks if the value is expressed
as a percentage. If the value is expressed as a percentage,
then Excel treats it as if it were divided by 100. In other words 75% is the
same as .75. So just by a way of a quick example.
If I type in the value -15, Excel treats it as a value. -15.
If I were to create a formula where I have -15 plus 75% then I will get
the result -1425.00%. Now there are two things to note about this
calculation. The first is that I wasn't taking 75% of 15
or in this case -15. Instead what I was doing was adding 75% or .75 to a negative
number, so the answer would be -14.25. However, when you add, subtract, multiply
or divide a number by a percentage, Excel expresses the result as a percentage.
So -14.25 is actually -1425%, because again you're multiplying the value by 100.
After verifying whether value is negative or whether it's expressed as a
percentage, Excel starts with the other operations. The first priority after the negative sign
and percentage is exponentiation where the value is multiplied by itself a
specified number of times. The symbol for an exponent is the caret, which
you type by pressing Shift+6 or as I think of it a capital 6.
The formula equals two raised to the power of 3, 2 ^ 3, would produce the result of 8.
That's 2x2x2. If I were to type in the formula = 2^4, I
would get the result of 16. The next operations in order are multiplication
and division followed by addition and subtraction.
So let's say that I wanted to multiply 3x4. I would get 12 and if I subtract 3 from 4,
I get 1. But what happens when you combine one or more
of these operations, especially operations at a different level into a single
formula? When you do, Excel examines the formula before
it starts making any calculations and if it finds two operators at the same
level, it performs those operations in left to right order.
That is if you type in the formula =10/2*9+3, 10 divided by 2 would evaluate to 5,
multiply that result by 9, which is 45, plus 3 which is the last operation in
priority order, you type Enter and get the result of 48.
You can change the order of operations in a formula by adding parentheses.
As an example, let's take the formula =9*2+3. In this case it would be 9 times 2, which
is 18, add 3 to the result and you would get 21.
If you want to change the order so the addition happens first and then you
multiply that result by 9, you would write the formula =9*(2+3), which equals 9 times
5 or 45. Finally, you can use the comparison operators
equal, greater than, greater than or equal to, less than, less than or equal
to and does not equal to create formulas that return a result of true or false.
For example the formula =10=9 would return the result of FALSE.
The formula =10 is not equal to, that's the less than sign and greater than sign,
9 would return a result of TRUE and finally 10>9 would return the result of TRUE.
So when would you want to create a formula using these comparison operators?
Well, comparing 10 to 9 isn't really a meaningful comparison, because it's
obvious that 10 is greater than 9. Where you might use this type of comparison
formula is if you have one cell that contains your sales goal for a month
and another cell that contains your actual sales results.
If your sales results are greater than your sales goal, then you could print
TRUE to indicate that you should receive a bonus.
If your sales results are less than your goal, then Excel could indicate FALSE
which tells your supervisor that you don't get a bonus this month.
Always be sure to verify that your formula's operations occur in the desired order.
If you're getting an answer you don't expect, that's a great first place to