Spreadsheet Task 7
This spreadsheet shows the snowfall, recorded at six locations over a six-week winter period.
Load EXCEL.
1. Set up the spreadsheet and ensure all figures are formatted as shown below.
Snowfall
Location / Week 1 / Week 2 / Week 3 / Week 4 / Week 5 / Week 6Salzburg / 3.5 / 4.5 / 4.0 / 6.0 / 6.5 / 7.5
Les Deux Alpes / 3.5 / 4.0 / 3.5 / 4.0 / 4.5 / 5.0
Lausanne / 4.5 / 6.0 / 7.5 / 5.5 / 5.0 / 6.0
Geneva / 4.5 / 5.0 / 3.0 / 10.0 / 2.0 / 5.0
Berne / 4.0 / 5.0 / 6.0 / 9.0 / 3.0 / 6.0
Innsbruck / 5.0 / 4.5 / 3.0 / 8.0 / 5.0 / 5.0
2. Change column widths to suit, align headings to the right and put all headings in Britannic Bold font at 12-point size, embolden all headings.
3. Add a column-heading Total and calculate the total snowfall recorded at each location over the six-week period.
4. Add an appropriate heading and, using the same formula, work out the total snowfall recorded for the locations each week.
5. What was the overall snowfall total?
6. Add a column heading Average to the right of the total column.
7. Calculate the average snowfall for each resort over the six weeks.
8. Format all totals and averages to one decimal place.
9. Save your work under the name SPREAD7 and, after ensuring your name is on the work, print a copy.
10. Sort the locations (and data) alphabetically.
11. Save this version as SPREAD7a and print one copy.
12. Delete the row that contains readings for Innsbruck.
13. Insert a row, in the correct alphabetical order, to include the following readings for Leysin: 3, 4.5, 4, 5.5, 6, 3.5.
14. Ensure that the total and average snowfalls for Leysin are calculated and that all other formulas recalculate to include these new figures. Check that the format of all new figures and totals is consistent with the rest of the spreadsheet.
15. The figures for Berne were incorrect, please change: Week 3 should have read 5.5 not 6.0 and week 6, 6.0 not 7.0.
16. Save the changes as SPREAD7b and print a copy.
We are now going to add a chart to this spreadsheet to present the data in an easily understood form.
17. Delete any blank rows in your spreadsheet, such as between the headings and the first set of data.
18. Highlight the cells in the range Location (probably A3) to the end of Salzburg Average (probably H9). Do include the average column. DO NOT include the Total row.
19. From the menu bar: Select “Insert", Scroll down to “Chart”.
20. Choose the Standard types Tab.
21. Select “Column” type chart and the clustered column chart sub-type. Click on the Next button.
Follow the steps on the Chart Wizard Window that should now be on your screen. Click on Back button to see a previous screen.
22. In the Chart Wizard window step 2 of 4 Select the data range tab. You should see the data range displayed and a preview of the chart with the labels for the axis and the legend. Select the Data Series in “Columns”: Location Titles should appear along the bottom (x-axis) of the chart. Select Use First Row as x-axis labels and Use First Column for Legend Text. Click on Next
23. At Step 3 Choose the Titles tab. Add the chart title “Snowfall” and your name in the Chart Title box.
24. Enter the following Axis titles: x = Location: y = Depth of Snow. Click on Finish.
25. Save as SPREAD7c and print a copy of the chart.
We are now going to convert all of the figures in the spreadsheet we have already created to metric measurement. This introduces a “constant”, which can be thought of as a number that never changes. We also introduce “Absolute cell Addressing” at this point.
27. In Cell A15 type the heading “Inches to Centimeters”. In 8 point size.
28. In cell B15 enter 2.54 - When we enter the location of this constant in our spreadsheet we are going to make the address fixed by the use of “absolute” addressing. This means entering $ (dollar) signs in front of the normal cell reference: B15 is entered as $B$15.
29. Highlight all of the cells in the spreadsheet except the Snowfall title. Click on the “Copy” button on the tool bar.
30. Select Cell A20, Click on the “Paste” button on the toolbar. If you have carried out this correctly you should now see a duplicate copy of the spreadsheet starting at cell A20.
31. Delete all of the numbers in the duplicate spreadsheet.
32. At your First data entry point (Berne, Week1) type =$B$15*(location of data from Berne, Week1 on original spreadsheet, probably B4).
33. Using the fill handle (the small black cross) drag this down all locations and total row.
34. Select all the data in the week 1 column and using the fill handle drag across all columns as far as averages in order to copy all of the formulas. Check these formulas and you should find the absolute reference has remained as $B$15 but all others have changed “relative to the cell before or above”.
35. Save as SPREAD7d, print a copy. Repeat the Charting exercise. Save as SPREAD7e and print out the chart. (Ensure your name appears on your printout)