Tuesday, June 07, 2005

Footy Tipping Rankings in Excel

A subscriber asked me today how to manage the rankings for their footy tipping comp in Excel. I suggested RANK as the function to use. However it was not quite so simple as that.

For example if you have 3 people on equal first place then they are all ranked 1 and the next place is ranked 4th. In this case they wanted to show the following places as second and third etc regardless of what the actual ranking was.
After some discussion with Mark (the footy tipping guru) I came up with the following formula - illustrated below.
=IF(RANK(E2,$E$2:$E$14)>RANK(E1,$E$2:$E$14),F1+1,F1)


This worked for every cell except the first where I used the formula =RANK(E2,E2:E14).
If I wanted to be real smart I would add in an IF statement testing for the first cell in the list as follows:
=IF(ISNUMBER(E1)=FALSE,RANK(E2,$E$2:$E$14), IF(RANK(E2,$E$2:$E$14)>RANK(E1,$E$2:$E$14),F1+1,F1))


Posted by: Jethro | 9:42 PM |