The SUM Function in Excel
In earlier parts of this course, you used addition formula quite a lot. You saw that the basic way to add things up was by doing this:
=A1 + B1 + C1
You've also used the in-built SUM function:
=SUM(A1:C1)
Whichever of these two you used, the answer was the same - Excel will add up whatever numbers you have in the cells A1, B1, and C1. The two methods above are adding up consecutive cells. But what if you want to add up the following, non-consecutive cells: A1, B1, C1, D9?
Well, you can combine the two methods. So you can do it like this:
= Sum(A1:C1) + D9
or you can do it like this:
= Sum(A1:C1, D9)
For the first method, just type a plus sign after your SUM function, followed by the cell you want to include:
= Sum(A1:C1) + D9
You can include as many other cells as you like:
= Sum(A1:C1) + D9 + E12 + G25
You can even use another SUM function:
= Sum(A1:C1) + SUM(G1:H1)
But you are just telling Excel which cells in your spreadsheet that you want to include in your addition formula.
The second method to add up non-consecutive cells starts in the same way: use a SUM function, and separate your consecutive cells with a colon:
= Sum(A1:C1)
To include the non consecutive cells, type a comma, followed by the cell you want to include:
= Sum(A1:C1, D9)
You can include other cells, as well:
= Sum(A1:C1, D9, E12, G25)
The thing to note is that all the cells are between the round brackets of the SUM function. Excel knows that SUM means to add up, so it sees each cell reference separated by commas, and then includes them in the addition.
To give you some practice, try this exercise.
Exercise
Create a simple spreadsheet with the number 3 in cells A1, B1, C1 and D1. Enter another number 3 in cell A2. Use one of the non consecutive addition formulas above to add up the values in all five cells. Your spreadsheet will then look like this, once you have the correct formula:
In the picture above, cell A4 displays the correct answer.
Selecting Non Consecutive Cells
Another way to select non-consecutive cells for your SUM functions is by holding down the CTRL key on your keyboard, and then left click in the cell you want to add. Try this:
- Click inside a different cell in your spreadsheet (B4, for example). Then click inside the formula bar at the top
- Now type the following into the formula bar (Don't forget to add the colon at the end):
=SUM(A1:
- The cell A1 will be highlighted on the spreadsheet. It will have sizing handles, so that you can stretch the selection
- Hold your left mouse button over the bottom right blue square, and drag to cell D1. You spreadsheet should look like this:
Excel will add the cells to your formula. But it will also add a colon after D2. We don't want this, because a colon means "add up a range of cells". So delete the colon and type a comma instead.
Now that you have the cells A1 to D1 selected, hold down the left CTRL key on your keyboard. Keep it held down, and click inside cell A2 with your left mouse button:
The cell A2 is highlighted, in the image above. Excel will add this to your formula.. To finish off, add the right bracket ). Then press the enter key on your keyboard.
Using this method, you can add as many individual cells as you want for your formula.
Exercise
On a new sheet, enter the number 3 in the following cells: A1, B1, C1, D1, E1. Then type a 3 in the cells A3, C3 and E3. Using non-consecutive addition, display your answer in cell A5. The finished spreadsheet will then look like ours below:
The answer to the addition, 24, is displayed in A5. Only one Sum function was used here, with the other cells separated by commas.
Adding up shouldn't cause you too many problems. The tricky part is selecting all the cells that you want to include.
How to Multiply in Excel
In an earlier section, you saw how to multiply two numbers. You use the asterisk symbol between two cell references:
= A4 * B5
If you need to multiply more than two numbers, you don't have to do this:
= A4 * A5 * A6 * A7 * A8
You can use the colon (:) notation to shorten the formula. With addition, you used the word SUM, and placed your formula between round brackets:
= SUM(A4:A8)
With multiplication, you can use the word PRODUCT instead. Like this:
= PRODUCT(A4:A8)
The only thing that has changed here is the name of the inbuilt function: PRODUCT instead of SUM. But Excel 2007 will see the word Product and multiply whatever is between the round brackets.
You can use PRODUCT in the same way you did for SUM. For example, if you wanted to add up values in cells A4 to A8, and cells B4 and B5, you'd do it like this:
= PRODUCT(A4:A8, B4, B5)
To give you some practice, try these exercises.
Exercise
On a new worksheet, enter the number 1, 2, 3, 4 and 5. Put them into cells A1 to E1. Now use PRODUCT to multiply all five numbers. Place your answer in cell A3. If you get it right, your spreadsheet should look like ours:
Exercise
For this exercise, delete your answer in cell A3. (You can do this by clicking into cell A3, and then hitting the Delete key on your keyboard). Now type a 6 in cell A3, a 7 in cell C3, and an 8 in cell E3. Use PRODUCT to multiply all 8 numbers. Place your answer in cell A5. Your spreadsheet will look like ours below, when you have the correct answer:
How to Subtract and Divide in Excel
In this part of the tutorials, you'll see how to Subtract and Divide in Excel. First up is subtraction.
Subtraction in Excel
You saw that to subtract one number from another, you just use the minus sign:
= A1 - A2
The image below shows the value in cell A2 being deducted from the value in cell A1. The formula has been entered in cell A3.
If you want to subtract more than two cells you can do it like this:
= A1 - B1 - C1
In a later section, you'll see why that formula may not give you the answer you were expecting. But subtraction in Excel is fairly straightforward, and shouldn't cause you too many problems.
Division in Excel
Dividing one value from another involves using the forward slash symbol ( / ). An example of its use is this:
= A1 / C1
Here, we're just telling Excel to divide the cell value on the left of the slash symbol by the cell value on the right. Division is fairly straightforward, too.
You can combine all the basic arithmetic operators to produce more complex formulas. We'll see how to do that now.
Combining Arithmetic Operators
The basic operators you've just met can be combined to make more complex calculations. For example, you can add to cells together, and multiply by a third one. Like this:
= A1 + A2 * A3
Or this:
= A1 + A2 - A3
And even this:
=SUM(A1:A9) * B1
In the above formula, we're asking Excel to add up the numbers in the cells A1 to A9, and then multiply the answer by B1. You'll get some practise with combining the operators shortly. But there's something you need to be aware of called Operator Precedence.
Operator Precedence
Some of the operators you have just met are calculated before others. This is known as Operator Precedence. As an example, try this:
- Open a new Excel spreadsheet
- In cell A1 enter 25
- In cell A2 enter 50
- In cell A3 enter 2
Now click in cell A5 and enter the following formula:
=(A1 + A2) * A3
Hit the enter key on your keyboard, and you'll see an answer of 150.
The thing to pay attention to here is the brackets. When you place brackets around cell references, you section these cells off. Excel will then work out the answer to your formula inside of the brackets, A1 + A2 in our formula. Once it has the answer to whatever is inside of your round brackets, it will move on and calculate the rest of your formula. For us, this was multiply by 3. So Excel is doing this:
- Add up the A1 and A2 in between the round brackets
- Multiply that answer by A3
Now try this:
- Click inside A5 where your formula is
- Now click into the formula bar at the top
- Delete the two round brackets
- Hit the enter key on your keyboard
What answer did you get? The images below show the answers with brackets and without:
With Brackets
Without Brackets
So why did Excel give you two different answers? The reason it did so is because of operator precedence. Excel sees multiplication as more important than adding up, so it does that first. Without the brackets, our formula is this:
A1 + A2 * A3
You and I may work out the answer to that formula from left to right. So we'll add A1 + A2, and THEN multiply by A3. But because Excel sees multiplication as more important, it will do the calculation this way:
- Multiply A2 by A3 first
- THEN add the A1
We have 50 in cell A2, and in cell A3 we have the number 2. When you multiply 50 by 2 you get 100. Add the 25 in cell A1 and the answer is 125.
When we used the brackets, we forced Excel to do the addition first:
(A1 + A2) * A3
Add the 25 in cell A1 to the 50 in cell A2 and your get 75. Now multiply by the 2 in cell A3 and you 150.
One answer is not more correct than the other. But because of operator precedence it meant that the multiplication got done first, then the addition. We had to used round brackets to tell Excel what we wanted doing first. Here's another example of operator precedence.
Substitute the asterisk symbol from your formula above with the division symbol. So instead of this:
= (A1 + A2) * A3
the formula will be this:
= (A1 + A2) / A3
When you hit the enter key on your keyboard, you should get an answer of 37.5.
Now click into cell A5, and then click into the formula bar. Delete the two round brackets, and hit the enter key again. What answer did you get this time? Here's the two images:
With the brackets
Without the brackets
Just like multiplication, division is seen as more important than addition. So this will get done first. Without the brackets, Excel will first divide A2 by A3. When it has the answer, it will then add the A1. We used the round brackets to force Excel to calculate things differently. Hence the two different answers. One final example.
Change you formula in cell A5 to this:
= (A1 * A2) / A3
Hit the enter key, and you should get an answer of 625.
Again remove the brackets, and hit the enter key. You'll still have an answer of 625. That's because Excel treats multiplication the same as division: they have equal importance. When this happens, Excel will work out the answer from left to right.
Addition and subtraction are also seen as equal to each other. Try this formula in cell A5:
= A1 + A2 - A3
Now put some round brackets in. Try this first:
= (A1 + A2) - A3
And then see what happens when you try this:
= A1 + (A2 - A3)
Was there any difference? There shouldn't have been. You should have the same answer.
So keep Operator Precedence in mind - all sums are not treated equally!
To give you some practice with combination formulas, have a go at constructing the more complex Budget spreadsheet in the link below.
A Budget Spreadsheet
In the small town of Evercrease, the Council managed to collect half a million pounds from its citizens. Unfortunately, the Council spent all of this, and another 69 thousand besides. Naturally, the good people of Evercrease objected to being asked for another 69 thousand pounds. So they got rid of the council and appointed a new one. Have a look at last year's budget, and see if you can do better. Here's the budget where the previous council overspent by 69 thousand pounds:
The final budget figure is in cell B33. It says minus £69 000. Your job is to construct the same budget as above, but making sure that you do not have a minus figure in cell B33. Otherwise, the people will fire you as well!
The first thing to do is to create the budget exactly as you see it above. When you have exactly the same figures as in the image, you can then start to amend things. For example, do you really need all those ducks and swans? What happens to your budget if you reduce the number of teachers from 5 to 4? Or the number of police cars from 4 to 2?
As soon as you make your reductions, you should see the figure in cell B33 change. At least it will if you have entered the correct formulas! Here's a little help on the formulas you need.
Budget Help
The first thing to do is to enter a figure of 500 000 in cell B1. This is the budget - how much is available to spend. You'll be referring to this figure in later cells. Then start on the budget sections.
The first section is Salaries. In the cells A4 to A9, enter the same labels as our image (Police Officers, Ambulance Drivers, etc). Enter the salaries in cells B4 to B9. In cells D4 to D9, enter how many of each are on the payroll. In cells F4 to F9, enter a formula to work out the cost of each profession. So 3 Police Officers multiplied by £16 000 is … ?
In cells B11, calculate the total cost of the salaries. In cell B12, calculate how much you have left to spend once the salaries have been deducted.
Do the same for the Equipment and Costs section. When you get to cell B22, you need to add the Salaries to the Equipment and Costs. Then you need to deduct your answer from the budget in cell B1. A combination formula will get you this.
The main calculations are in cells B32 to B36. For cell B32, calculate the total cost of the extras. For cell B33, this is just the Money Left. You then need to work out your Total Spending, how much you are spending each month. And the budget minus all those extras.
There is a lot of work to do with this spreadsheet. But completing it will bring your Excel skills on a lot!
In the next section, we'll move on to the inbuilt functions in Excel. First up are Averages.
Comments
Post a Comment