A short spreadsheet course

Task Menu

Student data

Data
Tasks
Notes
Solution

Peter The Carpenter

Data
Task
Notes
Solution

Football League

Data
Tasks
Notes
Solution

ICT Charts

Task
Notes

Katie's Kitchen

Task
Data sheet
Sample Solution

The Big Guide to Excel2007 [1.7MB PDF]

 

The Football League Table
Scenario

It is nearly the end of the season and, with just two matches remaining, the person responsible for producing the tables has resigned. You have been asked to take over and have been supplied with a table. This needs some information to be added to it so that you can produce a current table of positions and then, when the last two match results are known, a final table has to be produced.

[link to this data]

Develop the table with formulae and add new scores
Transfer the league table data to a spreadsheet and complete the match entries with these two results:

Castle Abbey beat Mer City 7 – 3

Ferton Town beat Mer City 5 – 2

Add formulae to the sheet to calculate Goal difference and add a new column Points (3 for a win, 1 for a draw)

Present the information attractively with suitable data types and format
Using a separate sheet, enhance the presentation of the table with your choice of layout, colours, fonts, borders and alignment. Include a new column to display the Position of each team (1st to 8th).
On a separate sheet, create a charts to display (i) each team, their Position and total Points and (ii) each team and their Goals For and Goals Against in accurate and effective ways.

[Link to notes to help you with these bits]

Part 2 Add new features
Prepare a new blank table for the next year's results. These are to be entered by staff on a separate sheet which needs to be included in the file. Formulae should be included as appropriate in the table so that results entered on the results grid are automatically entered in the league table and Points totals revised.

Add a feature that will enable the order of the table to be reset as required to show the teams in descending order of Points.


A sample form for recording results

Warning! This bit is tough!!

[Some notes with my suggested solution (but there could be others and better ones!)]