Hi Guys'n'Gals,

The PE department in my school have asked me to come up with a spreadsheet
which can show who (or which team) is leading a competition.

Therefore, I have allowed for 8 columns to show events and 10 rows for the
teams (in the development version). In the 9th column I have included an
=sum formula to add up the total score for each event. For example in Row
B - Team C could have scores of 10 8 4 0 2 4 3 2 - in column 9 the score
would reflect as 33.

That is the easy bit. What they then would like to see is a tenth column
which dependant on the scores in column 9 can say whether or not a team is
ranked 1st, 2nd, 3rd, 4th etc. When scores are added they would like to see
this 10th colum changing dynamically to show new leaders etc.

Has anyone got any ideas on how I would go about doing this? I don't want to
sort them every time, which I had thought of doing - they want it so that
they do not have to do anything.

Your advice is appreciated,

Best wishes

Gareth Edmondson


--
All of the moments that already passed
We'll try to go back and make them last

Gareth,

Put in the formula into J1 and fill down:
=RANK(I1,I:I,0)

This formula compares the referenced cell to all the values and returns its
rank.
(With 1 being the highest)

(this presupposes that you start in row 1, change it to I2, or I3 or what
ever row you start in)

--
steveb
(Remove 'NOSPAM' from email address if replying direct)


"Gareth Edmondson" wrote in message
...
Hi Guys'n'Gals,

The PE department in my school have asked me to come up with a spreadsheet
which can show who (or which team) is leading a competition.

Therefore, I have allowed for 8 columns to show events and 10 rows for the
teams (in the development version). In the 9th column I have included an
=sum formula to add up the total score for each event. For example in Row
B - Team C could have scores of 10 8 4 0 2 4 3 2 - in column 9 the score
would reflect as 33.

That is the easy bit. What they then would like to see is a tenth column
which dependant on the scores in column 9 can say whether or not a team is
ranked 1st, 2nd, 3rd, 4th etc. When scores are added they would like to
see
this 10th colum changing dynamically to show new leaders etc.

Has anyone got any ideas on how I would go about doing this? I don't want
to
sort them every time, which I had thought of doing - they want it so that
they do not have to do anything.

Your advice is appreciated,

Best wishes

Gareth Edmondson


--
All of the moments that already passed
We'll try to go back and make them last

Hi Steve,

Excellent. Thank you very much for the help. The PE department will be
delighted once I format the spreadsheet into something they can use.

Best wishes

Gareth Edmondson

"steveb" wrote in message
...
Gareth,

Put in the formula into J1 and fill down:
=RANK(I1,I:I,0)

This formula compares the referenced cell to all the values and returns
its
rank.
(With 1 being the highest)

(this presupposes that you start in row 1, change it to I2, or I3 or what
ever row you start in)

--
steveb
(Remove 'NOSPAM' from email address if replying direct)


"Gareth Edmondson" wrote in message
...
Hi Guys'n'Gals,

The PE department in my school have asked me to come up with a
spreadsheet
which can show who (or which team) is leading a competition.

Therefore, I have allowed for 8 columns to show events and 10 rows for
the
teams (in the development version). In the 9th column I have included an
=sum formula to add up the total score for each event. For example in
Row
B - Team C could have scores of 10 8 4 0 2 4 3 2 - in column 9 the score
would reflect as 33.

That is the easy bit. What they then would like to see is a tenth column
which dependant on the scores in column 9 can say whether or not a team
is
ranked 1st, 2nd, 3rd, 4th etc. When scores are added they would like to
see
this 10th colum changing dynamically to show new leaders etc.

Has anyone got any ideas on how I would go about doing this? I don't
want
to
sort them every time, which I had thought of doing - they want it so
that
they do not have to do anything.

Your advice is appreciated,

Best wishes

Gareth Edmondson


--
All of the moments that already passed
We'll try to go back and make them last

Gareth,

Glad to be of help!

keep on Exceling!

--
steveb
(Remove 'NOSPAM' from email address if replying direct)


"Gareth Edmondson" wrote in message
...
Hi Steve,

Excellent. Thank you very much for the help. The PE department will be
delighted once I format the spreadsheet into something they can use.

Best wishes

Gareth Edmondson

"steveb" wrote in message
...
Gareth,

Put in the formula into J1 and fill down:
=RANK(I1,I:I,0)

This formula compares the referenced cell to all the values and returns
its
rank.
(With 1 being the highest)

(this presupposes that you start in row 1, change it to I2, or I3 or
what
ever row you start in)

--
steveb
(Remove 'NOSPAM' from email address if replying direct)


"Gareth Edmondson" wrote in
message
...
Hi Guys'n'Gals,

The PE department in my school have asked me to come up with a
spreadsheet
which can show who (or which team) is leading a competition.

Therefore, I have allowed for 8 columns to show events and 10 rows for
the
teams (in the development version). In the 9th column I have included
an
=sum formula to add up the total score for each event. For example in
Row
B - Team C could have scores of 10 8 4 0 2 4 3 2 - in column 9 the
score
would reflect as 33.

That is the easy bit. What they then would like to see is a tenth
column
which dependant on the scores in column 9 can say whether or not a
team
is
ranked 1st, 2nd, 3rd, 4th etc. When scores are added they would like
to
see
this 10th colum changing dynamically to show new leaders etc.

Has anyone got any ideas on how I would go about doing this? I don't
want
to
sort them every time, which I had thought of doing - they want it so
that
they do not have to do anything.

Your advice is appreciated,

Best wishes

Gareth Edmondson


--
All of the moments that already passed
We'll try to go back and make them last