Ranking by two columns of data

rireland

Board Regular
Joined
Feb 12, 2004
Messages
64
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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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... :-(
 
Last edited:
Upvote 0
Thanks but I'm totally confused now, can you be a bit more explicit for an inexperienced Exceller.
R
 
Upvote 0
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 /
 
Upvote 0

Forum statistics

Threads
1,226,693
Messages
6,192,464
Members
453,725
Latest member
cvsdatreas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top