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
Notes

The data can actually be copied directly from the web spreadsheet but there isn't much if you want to type it.

Remember to copy the sheet you have just made before changing the data. Adjust the data for the new results - just work out the new entries required in your head one at a time, remembering to include the data for the losing team each time as well!

Each team should be shown as having played 7 matches

Add a heading Goal difference if it's not there already. Put a formula in for the first team. If your headings go across from cell A1, Goal difference will be =F2-G2. Type that in H2 and press Enter.

You can either type similar formulae in H3,H4 etc for the other teams or use the replication tool (dragging the black cross at the bottom left of a cell down as many rows as required).

The calculation of Points is quite straightforward too. Put Points in a heading and then use a formula like =(C2*3)+(D2*1). That too can be replicated down for the other teams.

The next job is to add their Positions in the table. This is best as a new first column so right click in column A and use Insert Column to get a new 'column A'. Add the heading Position and you could simply type 1,2,3 as appropriate. If there were lots and lots of entries, though, that would not be so easy so it is a good idea to learn a new formula that can do this for you.

Type =rank( in the cell where a Position is to be shown, probably A2, and the next parts will be what you are ranking, i.e Points as in J2, and the group in which they are to be ranked, i.e the range J2:J9 or similar. The complete formula will be along these lines: =Rank(J2,J2:J9,0). The 0 is to display in ascending order. 1 would display descending. Hit Enter and the Position of the first team in your list will be displayed. Now you can't just copy the formula down because that would change the range from J2:J9 to J3:J10, J4:J11 by Excel trying to be helpful.

You will need to freeze the formula (make the cell references absolute) and use $J$2:$J:$9 instead. That can then be copied.

As an alternative to typing lots of complicated stuff you can give a name to the range that is the 8 cells with points. Select those 8 cells and at the top left of the sheet there is a Namebox - just above the Row numbers, probably showing J2 or the first cell you highlighted. Type a name like points in the box. You can now use that Name in your formula instead of the complicated range letters and numbers.

So the formula could be =RANK(J2,points,0) and that will copy correctly too.

The last job in this 'simple' section for the Football Table task is to put the teams in order. That requires a Sort.

It is a good idea to save the sheet and maybe make a copy of it as Sorting can often go wrong! With this small amount of data there shouldn't be a problem however. It is a good idea to select all the data you wish to sort, not just one column. Older versions of Excel were inclined to go ahead and change the order of just what you had selected, leaving all the other bits of data where they were - a nightmare to correct if you hadn't kept a copy! So although later versions tend to warn you about things it's a good habit to get into (and necessary for the advanced section of this task).

Once highlighted, use the Sort button on the Toolbar and choose Points as the first thing to sort by and then by Goal difference, to separate those with similar Points totals.

(That should work fine and change the order accordingly. The Rank formula may show several with the same position as it cannot distinguish with more than one criteria.)

That completes the first section of the Football Table task. the next bit is much more complicated so suitable for those who like a challenge. However, there are some very useful formulae in the answers for thsoe who are interested.

[link to this data] [link to sample completed task]