11. Case Study in Spreadsheets
The following case study brings many of the advanced techniques you've acquired over the last few exercises.
You will be expected to use amongst others, the skills of
- Replication
- Absolute and Relative Referencing.
- Use of the IF Conditional Function
- Use of the RANK function..
Consider the League table below. Make sure you read the Competition rules very carefully.
Competition Rules
- There are no draws - penalties decide games which finish level.
- 3 points are awarded for each win.
- Bonus points - any team scoring more than 50 points in the season is awarded 5 bonus points. More than 50 goals results in 3 bonus points.
Note - Goal difference is found by subtracting the goals against from the goals scored column. It is acceptable for this figure to be a negative number.
What you have to do!
- Download the file league tables.xls, and,
- Insert a new column before column B, entitled Position, and then use the RANK function to display teams final standing from 1 to 16, 1 being the team with most points.
- Create a new column at the end of the spreadsheets with the entry saying "Champions" for the team with most points and nothing for all other teams. A formula should be used in the column to display the appropriate message.
- Sort the spreadsheet so that teams are listed in order of the final ranking for the season, with the 1st ranked team displayed at the top of the list.
- Save your file and show it to your teacher.
Mark off Exercise 19 in your Progress Grids
