Excel 2007 : How do I Rank data in 3 seperate columns?

**liam_bettinson** · 04-20-2012, 05:49 AM

Hi All, having a bit of trouble finding a soultion to this without having to use VBA...

Basically, I have a league table laid out as follows :

A - B - C - D - E - F
1 - TEAM - PLD - GF - GA - GD - PTS
2 - Team 1 - 3 - 3 - 3 - 0 - 3
3 - Team 2 - 3 - 4 - 4 - 0 - 3
4 - Team 3 - 3 - 5 - 4 - 1 - 5
5 - Team 4 - 3 - 5 - 6 - -1 - 2

I am currently using the following RANK and SUMPRODUCT code to rank teams based on columns F and E using :

=IF(C4=0,"",RANK(E4,E$4:E$7)+SUMPRODUCT(--(E4=E$4:E$7),--(F4<F$4:F$7)))

(I've included for a 'blank' return in the active cell if games played = '0') However I also want to include column C in this RANK/SUMPRODUCT in the event that the values in columns E & F are tied for 2 or more teams as per rows 2 & 3 above? Please can anybody modify my code above to account for colummn C or advise a completely new piece of code if this is deemed necassary? I want to avoid VBA so hopefulyl this is possible?

Many thanks in advance!

**Mallycat** · 04-20-2012, 06:15 AM

As a general rule, sort the table by the least important column first, followed by the next most important, finally by the most important.

Does that do it for you?

If I you could create a simple macro to do this! Or do it manually

**liam_bettinson** · 04-20-2012, 06:18 AM

I'd rather avoid the use of Macros for this and just use a relatively simple formula. Is it a case of adding another condition into the above code? If so how do I do this please?