I have a formula that I use in Excel 2003 for a sports competition which counts up points from P28:P37 then ranks them:

=IF(P28="","",RANK(P28,P28:P37)&CHOOSE(AND(RANK(P28,P28:P37)<>{11,12,13})*MIN(4,MOD(RANK(P28,P28:P37),10))+1,"th","st","nd","rd","th"))=IF(P28="","",RANK(P28,P28:P37)&CHOOSE(AND(RANK(P28,P28:P37)<>{11,12,13})*MIN(4,MOD(RANK(P28,P28:P37),10))+1,"th","st","nd","rd","th"))

This means there could be teams with an equal number of wins and I would now like to improve on this by looking at a further set of data in order to establish a clear winner and if still tied a further set.

Say the above formula from cells P28:P37, after ranking, produces three 1st’ out of the10 teams I can then eliminate the other seven and this is where I want to add to my original formula and rank these three by referring to another group of cells R28:R27 which contain totals of individual measurements in numbers with the lower numbers ranking higher.

Say P30,P32 & P33 are equal 1st then by referring to the lowest to the highest numbers in R30, R32 & R33 (of a group from C38:C37) I might be able to rank them 1,2 & 3 and that would be acceptable. However as they might still be equal is it then possible to apply another criteria to rank the tied teams by referring to 9 columns of cells D12:D21, F12:F21, H,J,L,N,P,R & T all 12 to 21 and the criteria used there is that again the lowest number between the tied teams in the first column be used to rank them higher and if they are still tied by having the same measurement number move to the next of the 9 col. and so on until we hopefully find a clear winner.

Maybe it isn’t possible to produce a formula to cover all of what I would like but I would be grateful for any help and of course could put the formula online if necessary.