Excel Formulas - Combining the Mathematical Operators

 

There are times when you will want to combine the arithmetic operators in your calculations. Here are a few examples of combining the operators:

Example 1

=(A1 + A2) * A3

The answer you should have got was 150. Notice the brackets in the formula. The brackets group part of your sum together. Without them, Excel will normally calculate from left to right. But it does some calculation before others. Excel sees multiplication as more important than adding up. To see what happens without the brackets, do this:

Now the answer is different! This time you should have gotten 125. Here are the picture versions of both formulas:

You might think the second one is wrong. But it's not. It's just the way Excel works things out. Because it sees multiplication as more important than adding up, it will multiply cell A2 by cell A3 first. That gets the answer of 100. Excel will then add this answer to cell A1, which gives the answer 125.

With the brackets in, you force Excel to work things out your way. You're saying "Do the sum in brackets first, then multiply". When you do the brackets first, you get a different answer. A1 + A2 = 75. Multiply 75 by cell A3 and you get the answer 150.

The moral is: take care when you are grouping operators together. And force Excel to work things out your way by using brackets to group your sums.

Example 2

Substitute the asterisk symbol from example 1 with the forward slash. So the formula will be changed from this:

= (A1 + A2) * A3

to this:

= (A1 + A2) / A3

Press the Return key on your keyboard to reveal the answer. It should be 37.5. Now take the brackets out and try again. Again, you get a different answer. The total will now be 50!

Again the same process is at work. Excel sees division as more important than adding up, so it does that first. So it will divide 50 by 2 to get 25. Then it will add the contents of cell A1 to get 50. With the brackets, we force Excel to do the adding up first, then divide by the cell A3.

Example 3

This time, enter the following formula for cell A5:

= (A1 * A2) / A3

Press the Return key on your keyboard to reveal the answer. It should be 625.

Remove the brackets and try again. The answer should be 625 again. This is because Excel sees Multiplication and division as being of equal importance. When all things are equal, Excel calculates from left to right.

Likewise, Excel sees addition and subtraction as being of equal importance. So it will calculate from left to right if you use addition and subtraction in the same formula. As an example, study this formula below:

As you can see, the formula in cell A5 is = A1 + A2 - A3. Now does that mean this:

= (A1 + A2) - A3

Or this:

= A1 + (A2 - A3)

Work it out on paper to see if there is any difference.

 

Despite the answer above, operator precedence is important: All sums are not equal!

<-- Back One Page Move on to the Next Part -->

<--Back to the Excel Contents Page