Working with spreadsheets/Spreadsheet calculations/Arithmetic
Working with Spreadsheets
Basic skills applicable to planning, creating and formatting an Excel spreadsheet.
An OP Online course from the National Certificate in Computing (Level 2) programme.
| Working with spreadsheets | |
|---|---|
| Spreadsheet calculations | Introduction | Arithmetic | Functions | Formulas | Key points | Assessment |
Contents
|
Please note: the following link will open in a new window/tab. When you have finished, simply close the window/tab and you'll return to this page.
Don't forget to check out the video on page 2.
|
So far we have only used Excel to lay out costs for our business. We are now going to show you how we can get Excel to work out and display the answers to some maths problems, and how we would apply that to a real-life example.
- Open Excel: we are going to work in a new blank sheet.
- Enter in the following information. Please bold the headings and left align the text:
| A | B | C | D | |
| 1 | ||||
| 2 | Income 1 | Income 2 | Total | |
| 3 | Example 1 | $123.00 | $265.00 | |
| 4 | ||||
| 5 | Income | Costs | Net | |
| 6 | Example 2 | $562.00 | $601.00 | |
| 7 | ||||
| 8 | Cost per person | People booking | Total cost | |
| 9 | Example 3 | $23.60 | 18 | |
| 10 | ||||
| 11 | Trip Cost | Number going | $ per person | |
| 12 | Example 4 | $6,300.50 | 15 | |
| 13 |
Addition
In this example, we have an exercise in adding together two sources of income. If we get a calculator, we can see that the answer is $388.
We want the answer to this to appear in cell D3. But furthermore we want Excel to calculate the answer for us.
- Click in cell D3 and type in: =B3+C3
- Press Enter.
| A | B | C | D | |
| 2 | Income 1 | Income 2 | Total | |
| 3 | Example 1 | $123.00 | $265.00 | =B3+C3 |
| 4 |
What has happened? The equals sign makes Excel calculate the equation and display the result. Without the equals sign, cell D3 would just display B3+C3.
We have also used cell references instead of typing in the number. Why? Because this means in the future we can change the cell contents and not have to worry about changing our equation.
| A | B | C | D | |
| 2 | Income 1 | Income 2 | Total | |
| 3 | Example 1 | $123.00 | $265.00 | $388.00 |
| 4 |
Subtraction
| A | B | C | D | |
| 5 | Income | Costs | Net | |
| 6 | Example 2 | $562.00 | $601.00 | =B6-C6 |
In the subtraction example, we are going to subtract Cost 1 from Income 1. Click in cell D7 and type in the following: =B6-C6
| A | B | C | D | |
| 5 | Income | Costs | Net | |
| 6 | Example 2 | $562.00 | $601.00 | - $39.00 |
Here we can see that our costs exceed our income, and so Excel gives us a negative answer.
Multiplying
| A | B | C | D | |
| 8 | Cost per person | People booking | Total cost | |
| 9 | Example 3 | $23.60 | 18 |
In the multiplying example we want to multiply the cost by the number of people booking. If we were putting this problem down, we’d write: 23.60 x 18. Excel does not use x for its multiplication symbol: instead, it uses the asterisk * (Found above the 8 key). Please enter what you think will be the correct formula in D9.
|
Dividing
| A | B | C | D | |
| 11 | Trip Cost | Number going | $ per person | |
| 12 | Example 4 | $6,300.50 | 15 |
Once again, Excel uses a different symbol for division. Instead of ÷ it uses the forward-slash key / . In cell D12 enter the formula you think would work for this.
|
| 
