Join Curt Frye for an in-depth discussion in this video Introducing arithmetic operators, part of Excel 2007: Introduction to Formulas and Functions.
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 look for an error.
- Copying and pasting the value results of a formula
- Creating formulas to count cells that meet certain conditions
- Connecting Excel to an external data source
- Building a two-input data table
- Summarizing data using an Excel table