The Football League Table
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.
to this data] [link to sample completed