Unit 1g
Unit 1g - Creating a Spreadsheet
Well, the bike riding competition was a great success! Your flyer and letters have really seemed to drum up business and we’ve had many more customer enquiries. I have also been told that a spreadsheet could produce graphs and present my information really well. This maybe really handy in the near future as I will want to let everybody know the results in the next newsletter I have planned. |
Task 1 Instructions
Using a spreadsheet
Jay needs to work out who won the competition. He has sent you a list of the results which includes the times for each trial and the number of penalties.
Download the information and type it into a spreadsheet.
Adjust the fonts, row heights and column widths to make sure all the information is displayed.
Make sure you have used sensible headings for each column
Task 1 Files
download_list.jpg
Task 2 Instructions
Using formulae
In the next available column, write in a formula that will work out the number of seconds to be added to Peter Jones's time from penalties. Allow 5 seconds for each penalty.
Use a formula, don't just work it out yourself.
Copy down the formula so that it works out the penalties for the other riders
Task 2 Help
The inpictures website can help you with lots of different spreadsheet tasks
Task 3 Instructions
Using functions
Use the SUM function to total up the five trial times for each rider. Then add on the penalty time to find a grand total for each rider.
Use the AVERAGE function to work out the average time that each trial took to work out which trial was the longest.
Task 4 Instructions
Formatting a spreadsheet
Format the spreasheet so that the penalty seconds and average times stand out. Screenshot how to format a cell
Add borders and titles to make the spreadsheet easy to understand and to improve the way it looks.
Put the words RESULTS, the date, and page numbers into the header
Save the spreadsheet and call it Cwm Bryn Competition Results
Task 4 Files
screenshots.doc 
Task 5 Instructions
Inserting rows
The work you have done so far is great but Jay needs to make some quick adjustment to the spreadsheet. Two more riders results need to be added.
Insert two more rows under Richard Faraday. Screenshot how to do this.
Now add the information
- John Edwards, 240, 286, 249, 255, 262 - 5 penalties
- Nelly Phelps,
265, 290, 285, 279, 289 - 3 penalties
Save the spreadsheet and call it Competition top ten
Task 5 Files
Task 6 Instructions
What if...
Jay wants to announce the results of the competition, but the number of penalties for Barry Davies is still to be announced.
Change the number of penalities that Barry is given to see the effect that they would have on the results.
Work out the highest number of penalites Barry can have if he is still going to win.
Save the spreadsheet and call it final competition results
Task 7 Instructions
More formulae
Dave wants to present the results in the same way as professional races. He wants the result to show how many extra seconds it took each rider, longer than the winner.
Use a formula for each rider to subtract the winning time from their times to give each riders extra time.
Save the spreadsheet and call it added time results
Task 8 Instructions
Deleting Rows
Sarah Evans has been disqualified from the competition because she lied about her age on her entrants form.
Delete the Sarah Evans row from the spreadsheet and shift cells up. Screenshot how to do this.
Save the spreadsheet and call it final spreadsheet
Task 8 Files
Task 9 Instructions
Create a chart
Create a chart showing the total seconds for each rider in the competition.
Format the chart so that it has a correct title, and use colour in the chart to make it suitable for use in a newletter to customers.
Task 9 Help
You could try this lesson from Office Online which shows how to create a chart.
To make your chart look better, this lesson might help
Task 10 Instructions
Final Printing Checklist
Use Set Print Area and suitable landscape or portrait and print:
Cwm Bryn Competition Results spreadsheet
Screenshots showing formatting
Competition top ten spreadsheet
Screenshots showing add row
added time results spreadsheet
final spreadsheet
final spreadsheet in formula view
Screenshots showing delete row
the chart you have created
Task 10 Files
checklist.pdf
Task 10 Help
You can compare your work to this portfolio.
Thanks for your help so far, its time to move on. You can go straight to the next task from here See you there!
|
