Question 1.13: USING EXCEL TO PERFORM CALCULATIONS AND PLOT GRAPHS Part-tim......
USING EXCEL TO PERFORM CALCULATIONS
AND PLOT GRAPHS
Part-time staff are paid on an hourly basis. The number of hours worked per week with the hourly rate of pay for seven staff are as follows:
(a) Enter the data onto a spreadsheet.
(b) Enter a formula to calculate the total pay for each member of staff.
(c) Plot a bar chart, showing the total weekly pay received by each member of staff.
| Name | J.M | P.M | D.H | K.C | J.McM | A.B | C.McK |
| Hours | 6 | 12.6 | 34 | 23 | 45.8 | 18 | 12.6 |
| Rate | £27.5 | £27.5 | £16.6 | £19.2 | £50.5 | £27.5 | £27.5 |
(a) Enter the data onto the spreadsheet as shown in Figure 1.7. You may start entering the data anywhere in the spreadsheet. In this example the data was entered starting at cell A2.
(b) Next, enter a formula to calculate total pay for each individual in the table.
Total pay = number of hours × hourly rate
To indicate to the computer that a formula is being entered into a given cell, the first
character typed must be =, followed by the formula:
- Position the cursor in cell B5 (so that hours worked, the hourly rate and total pay for J.M are all in the same column).
- Now, from the keyboard, enter the formula to calculate total pay by typing = B3∗B4. See Figure 1.8b.
- Repeat this process for each cell in row 5: ‘= C3 ∗ C4’, ‘= D3 ∗ D4’, etc.
The results of the calculations are given in row 5, Figure 1.8a.
Note: The advantage of entering the cell names instead of the actual values is that the result is automatically recalculated if the numbers in these cells are changed. Try it!
Alternatively, ‘drag and drop’ the formula in cell B5 across row 5, instead of keying the formula into each individual cell. This is accomplished by clicking on cell B5. It is now the active cell with a black box around it. Point the mouse to the bottom right-hand corner of the black box until a solid black cross appears, see Figure 1.8b.
Hold down the left button on the mouse and drag across cells C5 to H5. (Use the
HELP button or consult some of the many reference books on Excel.)
(c) To plot the total pay for each individual select (by holding down the left mouse button) the names in row 2; then hold down the control (ctrl) button on the keyboard and select row 5, which contains the pay for each individual. See Figure 1.9. To plot the graph, click on Insert in the menu bar, followed by ‘column’ and ‘2-D Column’, also illustrated in Figure 1.9.
The basic bar graph is given in Figure 1.10.
Format the graph
Adding the overall title, axis titles, grid lines, etc., is called ‘formatting the graph’:
To add titles, first click anywhere on the graph to access ‘Chart tools’ from the main menu bar. Click on ‘Layout’; from the ‘Layout’ menu you may select ‘Chart Title’ and ‘Axis Titles’ to enter these on your graph. See Figure 1.11.
You should try (trial and error!) the various options offered on the ‘Chart tools’ menu to produce a clear, well-labelled graph. Figure 1.12 is an example of a very basic finished graph.
