Skip to main content

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
  • Start a new spreadsheet
  • Enter the number 25 in cell A1
  • Enter the number 50 in cell A2
  • Enter the number 2 in cell A3
  • In cell A5 enter the following formula
=(A1 + A2) * A3
  • Press the Return key on your keyboard to get the answer
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:
  • Click on cell A5
  • Click inside the formula bar at the top
  • Delete both the brackets from the formula
  • Press the return key on your keyboard to see the answer
Now the answer is different! This time you should have gotten 125. Here are the picture versions of both formulas:

Formula with Brackets
With Brackets
 
Formula without Brackets
Without Brackets

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 2Substitute 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:
Example 3
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!
Labels:

Comments

Post a Comment

Popular posts from this blog

Beginners PHP  -This is a complete and free PHP programming course for beginners. It's assumed that you already have some HTML skills. But you don't need to be a guru, by any means. If you need a refresher on HTML, then click the link for the Web Design course on the left of this page. Everything you need to get started with this PHP course is set out in section one below. Good luck! Home Page > PHP Section One - An Introduction to PHP 1. What is PHP and Why do I need it? 2. What you need to get started 3. Installing and testing Wampserver 4. Troubleshooting > PHP Two - Getting Started With Variables 1. What is a Variable? 2. Putting text into variables 3. Variables - some practice 4. More variable practice 5. Joining direct text and variable data 6. Adding up in PHP 7. Subtraction 8. Multiplication 9. Division 10. Floating point numbers > PHP Three - Conditional Logic 1. If Statements 2. Using If Statements 3....
Post a Comment
Read more
Visual Basic .NET Contents Page   -This computer course is an introduction to Visual Basic.NET programming for beginners. This course assumes that you have no programming experience whatsoever. It's a lot easier than you think, and can be a very rewarding hobby! You don't need to buy any software for this course! You can use the new FREE Visual Basic Express Edition from Microsoft. To see which version you need, click below: Getting the free Visual Studio Express - Which version do I need? > VB .NET One - Getting Started   1. Getting started with VB.NET 2. Visual Basic .NET Forms 3. Adding Controls using the Toolbox Home Page 4. Adding a Textbox to the Form 5. Visual Basic .NET and Properties 6. The Text Property 7. Adding a splash of colour 8. Saving your work 9. Create a New Project >   VB .NET Two - Write your first .NET code   1. What is a Variable? 2. Add a coding button to the Form 3. Writing y...
Post a Comment
Read more
The Excel SumIF Function  - Another useful Excel function is SumIF. This function is like CountIf, except it adds one more argument: SUMIF( range ,  criteria ,  sum_range ) Range and criteria are the same as with  CountIF  - the range of cells to search, and what you want Excel to look for. The Sum_Range is like range, but it searches a new range of cells. To clarify all that, here's what we'll use SumIF for. (Start a new spreadsheet for this.) Five people have ordered goods from us. Some have paid us, but some haven't. The five people are Elisa, Kelly, Steven, Euan, and Holly. We'll use SumIF to calculate how much in total has been paid to us, and how much is still owed. So in Column A, enter the names: In Column B enter how much each person owes: In Column C, enter TRUE or FALSE values. TRUE means they have paid up, and FALSE means they haven't: Add two more labels: Total Paid, and Still Owed. Your spreadsheet should look something li...
Post a Comment
Read more