Need Formula For Rank

[CARBOB]

In column B:D, the values can be from 0-9. Need formula to rank them 1=lowest;2=middle;3=highest

some examples

Excel Workbook
	A	B	C	D	E
7	07/21/11	5	2	9	213
8	07/20/11	7	9	9	133
9	07/19/11	2	6	5	132
10	07/18/11	2	4	8	123
Sheet1

Excel 2007

 

[villy]

You can try this.. quite long but it works... I will try to make it short and update you but meanwhile you can try this. it works for me

[E7]=IF(AND(B7<=C7,C7<=D7),1&2&3,IF(AND(C7<=B7,D7<=B7),2&3&1,IF(AND(D7<=B7,B7<=C7),3&1&2,IF(AND(B7<=C7,D7<=C7),1&3&2,2&1&3))))

Thanks

 

[CARBOB]

It does work, thank you!!

 

[CARBOB]

It does work, thank you!!

Made a mistake, doesn't return correct rank if value has double digits

003=112
300=211

 

[villy]

If I input 003 it gives me 123
and 300 gives me 231 is that wrong?

 

[villy]

Or you can use this always start with 1 if B & D are same value...

=IF(AND(B10<=C10,C10<=D10),1&2&3,IF(AND(C10<=B10,D10<B10),2&3&1,IF(AND(D10<B10,B10<=C10),3&1&2,IF(AND(B10<=C10,D10<=C10),1&3&2,2&1&3))))

 

[CARBOB]

Found the solution in a former post, that I forgot about. Sorry, for that. Thanks for your help.

=SUMPRODUCT(MMULT((MID($E10,{1;2;3},1)>MID($E10,{1,2,3},1))+0,{1;1;1})+1,10^{2;1;0})

 

[villy]

Sorry for my last post, I think this is what you want..
[E7]=IF(AND(B9<=C9,C9<=D9),1&2&3,IF(AND(C9<B9,D9<B9),3&1&2,IF(AND(D9<B9,B9<=C9),2&3&1,IF(AND(B9<=C9,D9<C9),1&3&2,2&1&3))))

Thanks

 

[villy]

That's great! alternative formula..
I checked it with what I did brings the same result...

 

[markmzz]

Another formula:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;background-color: #BFBFBF;;">7/21/2011</td><td style="text-align: center;background-color: #BFBFBF;;">5</td><td style="text-align: center;background-color: #BFBFBF;;">2</td><td style="text-align: center;background-color: #BFBFBF;;">9</td><td style="text-align: right;background-color: #FFFF00;;">213</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;background-color: #BFBFBF;;">7/20/2011</td><td style="text-align: center;background-color: #BFBFBF;;">7</td><td style="text-align: center;background-color: #BFBFBF;;">9</td><td style="text-align: center;background-color: #BFBFBF;;">9</td><td style="text-align: right;background-color: #FFFF00;;">133</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;background-color: #BFBFBF;;">7/19/2011</td><td style="text-align: center;background-color: #BFBFBF;;">2</td><td style="text-align: center;background-color: #BFBFBF;;">6</td><td style="text-align: center;background-color: #BFBFBF;;">5</td><td style="text-align: right;background-color: #FFFF00;;">132</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;background-color: #BFBFBF;;">7/18/2011</td><td style="text-align: center;background-color: #BFBFBF;;">2</td><td style="text-align: center;background-color: #BFBFBF;;">4</td><td style="text-align: center;background-color: #BFBFBF;;">8</td><td style="text-align: right;background-color: #FFFF00;;">123</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;background-color: #BFBFBF;;">7/16/2011</td><td style="text-align: center;background-color: #BFBFBF;;">1</td><td style="text-align: center;background-color: #BFBFBF;;">1</td><td style="text-align: center;background-color: #BFBFBF;;">2</td><td style="text-align: right;;">113</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;background-color: #BFBFBF;;">7/15/2011</td><td style="text-align: center;background-color: #BFBFBF;;">2</td><td style="text-align: center;background-color: #BFBFBF;;">1</td><td style="text-align: center;background-color: #BFBFBF;;">1</td><td style="text-align: right;;">311</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;background-color: #BFBFBF;;">7/14/2011</td><td style="text-align: center;background-color: #BFBFBF;;">1</td><td style="text-align: center;background-color: #BFBFBF;;">2</td><td style="text-align: center;background-color: #BFBFBF;;">2</td><td style="text-align: right;;">133</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;background-color: #BFBFBF;;">7/13/2011</td><td style="text-align: center;background-color: #BFBFBF;;">2</td><td style="text-align: center;background-color: #BFBFBF;;">2</td><td style="text-align: center;background-color: #BFBFBF;;">1</td><td style="text-align: right;;">331</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;background-color: #BFBFBF;;">7/12/2011</td><td style="text-align: center;background-color: #BFBFBF;;">1</td><td style="text-align: center;background-color: #BFBFBF;;">1</td><td style="text-align: center;background-color: #BFBFBF;;">1</td><td style="text-align: right;;">111</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">*********</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td></tr></tbody></table><p style="width:2.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Rank</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E1</th><td style="text-align:left">=SUBSTITUTE(<font color="Blue">RANK(<font color="Red">B1,B1:D1,1</font>)&RANK(<font color="Red">C1,B1:D1,1</font>)&RANK(<font color="Red">D1,B1:D1,1</font>),"22","33"</font>)*1</td></tr></tbody></table></td></tr></table><br />

Markmzz