Skip to content
Skip to main content
header search
ahihi OpenLearn
OpenLearn
Menu
sticky search

About this free course

Become an OU student

Download this course

Share this free course

Course content Course content
Succeed with maths: part 2
Succeed with maths: part 2

Start this free course now. Just create an account and sign in. Enrol and complete the course for a free statement of participation or digital badge if available.

More free courses

1.2 Spreadsheet formulas

There is one final activity in this section on spreadsheets, bringing all these ideas together.

Activity 3 Spreadsheet formulas

Timing: Allow approximately 10 minutes

The formulas used in a spreadsheet are displayed as shown in Figure 3. Note that the asterisk (*) is the notation for multiplication used in spreadsheets and that all entries have been rounded to the closest penny. This example shows UK VAT, which was 20 per cent in 2014 (20 per cent as a decimal is 0.2).

Described image
Figure 3 Spreadsheet formulas
Show description|Hide description

The following spreadsheet is shown: Item Cost (£) Tax (£) Radio 64.99 =B2*0.06 =B2+C2 Kettle 16.99 =B3*0.06 =B3+C3 Fan 12.99 =B4*0.06 =B4+C4

Figure 3 Spreadsheet formulas
  • a.The values in columns C and D will be displayed to two decimal places because they represent an amount of money. What values will be displayed in cells C3 and D3?

Answer

  • a.The formula in C3 is: value in cap c three equals value in cap b three multiplication 0.2 (to 2 decimal places)

    So, value in multirelation cap c three equals 16.49 multiplication 0.2 almost equals 3.30

    The value in D3 is obtained by adding together the values in B3 and C3.

    So, value in cap d three equals value in cap b three plus value in equation sequence cap c three equals 16.49 plus 3.30 equals 19.79

  • b.What do you think is being calculated in the cells in column D? Can you suggest a suitable heading for this column to be entered into D1?

Answer

  • b.Column D represents the total cost of the item plus VAT. A suitable heading might be ‘Total (£)’. You can think of other correct titles such as ‘Final Price (£)’.
  • c.Cell D5 calculates the sum of the values in D2, D3 and D4. Write down a formula that could be entered in cell D5. What does this value represent?

Answer

  • c.The sum can be found by adding together the values in cells D2, D3 and D4. The formula ‘=D2+D3+D4’ could therefore be entered into cell D5. (Note that you do not use the formulas that are present in each of the cells you are adding. Just the cell title is sufficient.) Alternatively, you could use ‘SUM(D2:D4)’, which also adds together the cells from D2 to D4. The resulting entry represents the total cost (including VAT) of the radio, kettle and fan together.

    With these changes, and the title ‘Total (£)’ typed into cell C5, the spreadsheet will look like the example shown here:

    This shows a spreadsheet made up of four columns and 5 rows.
    Figure 4 Spreadsheet
    Show description|Hide description

    This shows a spreadsheet made up of four columns and 5 rows. The columns are labelled as A to D and the rows numbered on the left side from 1 to 5. The rows contain the following information.

    Row 1: Column A ‘Item’, column B ‘Cost (£)’, column C ‘VAT (£)’, column D ‘Total (£)’

    Row 2: Column A ‘Radio’, column B 64.99, column C 13, column D 77.99

    Row 3: Column A ‘Kettle’, column B 16.49, column C 3.30, column D 19.79

    Row 4: Column A ‘Fan’, column B 12.99, column C 2.60, column D 15.59

    Row 5: Column A blank, column B blank, column C ‘Total’, column D £113.36

    Figure 4 Spreadsheet

This section has served a few purposes. It has introduced spreadsheets and shown you how you can use these to carry out calculations as well as helping you make a start on writing your own formula. The next section leaves spreadsheets behind to continue with this latter skill, to build your confidence with formulas further.