Formula - rank numbers high to low

psrs0810

Well-known Member
Joined
Apr 14, 2009
Messages
1,088
I have a listing of values and I need to place a number 1-10 for the top 10 highest numbers.
For example, out of these 16 numbers I need a 1 next to the highest number, a 2 next to the second highest number, etc.

10,527.65
5,655.68
1,701.93
1,349.18
0.00
573.11
140.86
6,064.77
2,093.60
5,680.65
4,045.40
5,546.32
-1,033.02
-277.55
891.20
25,699.12
<colgroup><col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2560;"> <tbody> </tbody>
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,302
Thats just a RANK formula:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Score</td><td style=";">Rank</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">10,527.65</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">5,655.68</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">1,701.93</td><td style="text-align: right;;">9</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">1,349.18</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">0</td><td style="text-align: right;;">14</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">573.11</td><td style="text-align: right;;">12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">140.86</td><td style="text-align: right;;">13</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">6,064.77</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">2,093.60</td><td style="text-align: right;;">8</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">5,680.65</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">4,045.40</td><td style="text-align: right;;">7</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">5,546.32</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;">-1,033.02</td><td style="text-align: right;;">16</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;">-277.55</td><td style="text-align: right;;">15</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;">891.2</td><td style="text-align: right;;">11</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;;">25,699.12</td><td style="text-align: right;;">1</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet9</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><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: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><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: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=RANK(<font color="Blue">A2,$A$2:$A$17</font>)</td></tr></tbody></table></td></tr></table><br />
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,711
Office Version
365
Platform
Windows
How about

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">10,527.65</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">5,655.68</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">1,701.93</td><td style="text-align: right;;">9</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">1,349.18</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">0</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">573.11</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">140.86</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">6,064.77</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">2,093.60</td><td style="text-align: right;;">8</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">5,680.65</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">4,045.40</td><td style="text-align: right;;">7</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">5,546.32</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;">-1,033.02</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;">-277.55</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;">891.2</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;;">25,699.12</td><td style="text-align: right;;">1</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Database</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><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: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><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: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=IF(<font color="Blue">RANK(<font color="Red">A2,$A$2:$A$17,0</font>)>10,"",RANK(<font color="Red">A2,$A$2:$A$17,0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,211
Office Version
2007
Platform
Windows
delete.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,711
Office Version
365
Platform
Windows
Glad we could help & thanks for the feedback
 

psrs0810

Well-known Member
Joined
Apr 14, 2009
Messages
1,088
what if I want to make my Rank formula dynamic?
if I have 12 months of data and I have a cell that shows a particular month, I want to rank that month. but if I change the month, how do I have my formula automatically change columns?
Thanks
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,302
How is your data laid out? Do you have 12 columns, one per month? Or do you have 2 columns, 1 with the score, 1 with the month? Or something else?
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,302
I still have some doubts about how the layout looks, but consider:

Book1
ABCDEFGHIJKLMN
1MarJanFebMarAprMayJunJulAugSepOctNovDec
2710,527.6533.02397.59726.65774.09474.69246.65836.6449.46203.91389.42523.63
3105,655.68984.81262.9583.73900.6719.19482.91559.24468.9404.86160.57136.65
41,701.9369.435.81203.12218.46772.52434.61188.19958.3424.59695.02771.91
521,349.18614.72950.98766.8981.78131.79538.82330.68793.99847.16262.71872.69
66076.15413.53284.75638.54283.53976.48433.07322.0429.44220.76416.61
7573.11338.7913.25450.71636.39543.66975.36771.65533.66589.05825.8150.93
81140.86198.82952.19557.07936.01837.69546.78455.62223.74280.92173.59518
96,064.77135.2950.27327.2460.31404.06987.3679.87792.82544.91947.09427.8
1032,093.60622.32576.97658.59758.6643.79693.28875.44811.33295.23266.29722.26
1185,680.65330.64333.99704.61111.31912.585.21889.758.16501.19512.05490.85
124,045.40523.42261.19806.14367.15500.4774.38394.1443.63869.15901.2178.02
1395,546.32420.04300.1111.74677.75961.01612.94664.88800.59180.09795.51519.39
145-1,033.0278.36501.24662.54819.88984.94921.43656.52167.21311.72620.68542.24
15-277.5538.58122.45281.75128674.96953.14491.62672.53962.91610.27121.96
16891.2982.8736.23428.06224.24818.94306.01577.64369.24475.35173.87805.23
17425,699.1277.36532.22217.06376.79799.09436.24680.11650.94650.54809.2262.46
Sheet1
Cell Formulas
RangeFormula
A2:A17A2=IF(RANK(INDEX(C$2:N$17,ROWS(A$2:A2),MATCH(A$1,C$1:N$1,0)),INDEX(C$2:N$17,0,MATCH(A$1,C$1:N$1,0)))>10,"",RANK(INDEX(C$2:N$17,ROWS(A$2:A2),MATCH(A$1,C$1:N$1,0)),INDEX(C$2:N$17,0,MATCH(A$1,C$1:N$1,0))))


By changing the value in A1, it will rank the values from the corresponding column.
 

Forum statistics

Threads
1,089,662
Messages
5,409,597
Members
403,271
Latest member
Rajeev Muraleedharan

This Week's Hot Topics

Top