Ranking by two columns of data

[rireland]

Sure this has been asked before but can't find it:

Column A has faults, column B has time. The faults outrank the time so need to first rank by faults and then by time ( lowest best in both cases)

FAULTS TIME RANK

0 - 10 - 1
0 - 15 - 2
4 - 8 - 4
4 - 15 - 5
4 - 7 - 3

Richard

 

[Glove_Man]

If you put the value FAULTS in cell B2 so the first data is in row 3 then:

SUMPRODUCT(--($B$3:$B$7 < B3 ))
+ SUMPRODUCT ( -- ($B$3:$B$7 = B3) , --($C$3:$C$7 < C3) ) +1

as an array formula (Ctrl + shift + enter) works. Not sure if it's the simplest though.

Anyone know why only the first third or so of the formula displays if you put it on one line? And now I get the last 3 chracters as a bonus too...

 

[rireland]

Thanks but I'm totally confused now, can you be a bit more explicit for an inexperienced Exceller.
R

 

[Richard Schollar]

Hi

See the following - formula in C2 is copied down as far as required:
<b>Sheet2</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >FAULTS</td><td >TIME</td><td >RANK</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">0</td><td style="text-align:right; ">10</td><td style="text-align:right; ">1</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">0</td><td style="text-align:right; ">15</td><td style="text-align:right; ">2</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">4</td><td style="text-align:right; ">8</td><td style="text-align:right; ">4</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">4</td><td style="text-align:right; ">15</td><td style="text-align:right; ">5</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">4</td><td style="text-align:right; ">7</td><td style="text-align:right; ">3</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C2</td><td >=COUNTIF($A$2:$A$6,"<"&A2)+SUMPRODUCT(--<span style=' color:008000; '>($A$2:$A$6=A2)</span>,--<span style=' color:008000; '>($B$2:$B$6<B2)</span>)+1</td></tr></table></td></tr></table> <br /><br /

 

[rireland]

Brilliant!
Thanks

 

[rireland]

What does the -- do to a formula?

R