Top of the league

Charlie_Chalk

Board Regular
Joined
Sep 15, 2002
Messages
93
Hi

I am trying to create spreadsheet that orders a list even when the items on the list change value (its a league table).

Is this possible in Excel?

Any help would be gratefully recieved!

Chaz
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Charlie_Chalk

Board Regular
Joined
Sep 15, 2002
Messages
93
Thats great, thanks for replying so fast.

Will =RANK take into account the name of the team (in say, A1) and rank using the points (in A2) so both the team name and points are put into order.

Hope this makes sense!

Thanks again

Chaz
 

Chris Davison

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,790
yep....

see the first example with just ranks teams and points.... with an automatically-updating table to the right

and the second example whcih takes care of ties...

<CENTER><TABLE ALIGN=CENTER BORDER=1 BORDERCOLOR=#C0C0C0 CELLSPACING=0 WIDTH=60%><TR><TD COLSPAN=8 BGCOLOR=#0C266B ><TABLE ALIGN=CENTER BORDER=0 WIDTH=100%><TR><TD ALIGN=LEFT><FONT COLOR=WHITE>Microsoft Excel - charlie_chalk.xls</FONT></TD><TD ALIGN=RIGHT><FONT COLOR=WHITE SIZE=2>___Running: xl97 : OS = Windows (32-bit) 4.90</FONT></TD></TR></TABLE></TD></TR><TR><TD BGCOLOR=#D4D0C8 COLSPAN=8><TABLE BORDER=0 ALIGN=CENTER VALIGN=MIDDLE HEIGHT=10 WIDTH=100%><TR><TD>(<U>F</U>)ile (<U>E</U>)dit (<U>V</U>)iew (<U>I</U>)nsert (<U>O</U>)ptions (<U>T</U>)ools (<U>D</U>)ata (<U>W</U>)indow (<U>H</U>)elp</TD><TD ALIGN=RIGHT VALIGN=MIDDLE><FORM NAME='formCb7552370787'><INPUT TYPE='Button' NAME='btCb5705426622' value='Copy Formula' onClick='window.clipboardData.setData("Text",document.formFb0487398093.sltNb8859926925.value);'></FORM></TD></TR></TABLE></TD></TR><TR><TD BGCOLOR=WHITE COLSPAN=8><TABLE BORDER=0><TR><Form name='formFb0487398093'><TD WIDTH=5% ALIGN=CENTER BGCOLOR=White><SELECT NAME='sltNb8859926925' onChange='document.formFb0487398093.txbFb1960511720.value = document.formFb0487398093.sltNb8859926925.value'><option value='=RANK(C4,$C$4:$C$7)'>A4<option value='=VLOOKUP(E4,$A$4:$B$7,2,0)'>F4<option value='=VLOOKUP(F4,$B$4:$C$7,2,0)'>G4<option value='=RANK(C5,$C$4:$C$7)'>A5<option value='=VLOOKUP(E5,$A$4:$B$7,2,0)'>F5<option value='=VLOOKUP(F5,$B$4:$C$7,2,0)'>G5<option value='=RANK(C6,$C$4:$C$7)'>A6<option value='=VLOOKUP(E6,$A$4:$B$7,2,0)'>F6<option value='=VLOOKUP(F6,$B$4:$C$7,2,0)'>G6<option value='=RANK(C7,$C$4:$C$7)'>A7<option value='=VLOOKUP(E7,$A$4:$B$7,2,0)'>F7<option value='=VLOOKUP(F7,$B$4:$C$7,2,0)'>G7<option value='=RANK(C13,$C$13:$C$18)+COUNTIF($C$13:C13,C13)-1'>A13<option value='=VLOOKUP(E13,$A$13:$B$18,2,0)'>F13<option value='=VLOOKUP(F13,$B$13:$C$18,2,0)'>G13<option value='=RANK(C14,$C$13:$C$18)+COUNTIF($C$13:C14,C14)-1'>A14<option value='=VLOOKUP(E14,$A$13:$B$18,2,0)'>F14<option value='=VLOOKUP(F14,$B$13:$C$18,2,0)'>G14<option value='=RANK(C15,$C$13:$C$18)+COUNTIF($C$13:C15,C15)-1'>A15<option value='=VLOOKUP(E15,$A$13:$B$18,2,0)'>F15<option value='=VLOOKUP(F15,$B$13:$C$18,2,0)'>G15<option value='=RANK(C16,$C$13:$C$18)+COUNTIF($C$13:C16,C16)-1'>A16<option value='=VLOOKUP(E16,$A$13:$B$18,2,0)'>F16<option value='=VLOOKUP(F16,$B$13:$C$18,2,0)'>G16<option value='=RANK(C17,$C$13:$C$18)+COUNTIF($C$13:C17,C17)-1'>A17<option value='=VLOOKUP(E17,$A$13:$B$18,2,0)'>F17<option value='=VLOOKUP(F17,$B$13:$C$18,2,0)'>G17<option value='=RANK(C18,$C$13:$C$18)+COUNTIF($C$13:C18,C18)-1'>A18<option value='=VLOOKUP(E18,$A$13:$B$18,2,0)'>F18<option value='=VLOOKUP(F18,$B$13:$C$18,2,0)'>G18</select></TD><TD WIDTH=3% ALIGN=RIGHT BGCOLOR=#D4D0C8 >=</TD><TD ALIGN=LEFT BGCOLOR=White><input type='text' name='txbFb1960511720' size='120' value='=RANK(C4,$C$4:$C$7)'></TD></form></TR></TABLE></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER>
</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>A</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>B</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>C</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>D</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>E</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>F</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>G</CENTER></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>1</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Basic :</FONT></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>2</CENTER></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Automatically updating league</FONT></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>3</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>RANK</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Name</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Points</FONT></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>RANK</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Name</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Points</FONT></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>4</CENTER></TD><TD BGCOLOR=#CCFFCC ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=RANK(C4,$C$4:$C$7)')><FONT FACE=Arial COLOR=#000000>2</FONT></A></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>John</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>22</FONT></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>1</FONT></TD><TD BGCOLOR=#FF99CC ALIGN=Left VALIGN=BOTTOM ><A HREF=javascript:alert('=VLOOKUP(E4,$A$4:$B$7,2,0)')><FONT FACE=Arial COLOR=#000000>Paul</FONT></A></TD><TD BGCOLOR=#FF99CC ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=VLOOKUP(F4,$B$4:$C$7,2,0)')><FONT FACE=Arial COLOR=#000000>23</FONT></A></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>5</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=RANK(C5,$C$4:$C$7)')><FONT FACE=Arial COLOR=#000000>1</FONT></A></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Paul</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>23</FONT></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>2</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><A HREF=javascript:alert('=VLOOKUP(E5,$A$4:$B$7,2,0)')><FONT FACE=Arial COLOR=#000000>John</FONT></A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=VLOOKUP(F5,$B$4:$C$7,2,0)')><FONT FACE=Arial COLOR=#000000>22</FONT></A></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>6</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=RANK(C6,$C$4:$C$7)')><FONT FACE=Arial COLOR=#000000>3</FONT></A></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>George</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>21</FONT></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>3</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><A HREF=javascript:alert('=VLOOKUP(E6,$A$4:$B$7,2,0)')><FONT FACE=Arial COLOR=#000000>George</FONT></A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=VLOOKUP(F6,$B$4:$C$7,2,0)')><FONT FACE=Arial COLOR=#000000>21</FONT></A></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>7</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=RANK(C7,$C$4:$C$7)')><FONT FACE=Arial COLOR=#000000>4</FONT></A></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Ringo</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>14</FONT></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>4</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><A HREF=javascript:alert('=VLOOKUP(E7,$A$4:$B$7,2,0)')><FONT FACE=Arial COLOR=#000000>Ringo</FONT></A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=VLOOKUP(F7,$B$4:$C$7,2,0)')><FONT FACE=Arial COLOR=#000000>14</FONT></A></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>8</CENTER></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>9</CENTER></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>10</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>With ties :</FONT></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>11</CENTER></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Automatically updating league</FONT></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>12</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>RANK</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Name</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Points</FONT></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>RANK</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Name</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Points</FONT></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>13</CENTER></TD><TD BGCOLOR=#CCFFCC ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=RANK(C13,$C$13:$C$18)+COUNTIF($C$13:C13,C13)-1')><FONT FACE=Arial COLOR=#000000>5</FONT></A></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>John</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>18</FONT></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>1</FONT></TD><TD BGCOLOR=#FF99CC ALIGN=Left VALIGN=BOTTOM ><A HREF=javascript:alert('=VLOOKUP(E13,$A$13:$B$18,2,0)')><FONT FACE=Arial COLOR=#000000>Jack</FONT></A></TD><TD BGCOLOR=#FF99CC ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=VLOOKUP(F13,$B$13:$C$18,2,0)')><FONT FACE=Arial COLOR=#000000>26</FONT></A></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>14</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=RANK(C14,$C$13:$C$18)+COUNTIF($C$13:C14,C14)-1')><FONT FACE=Arial COLOR=#000000>4</FONT></A></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Paul</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>19</FONT></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>2</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><A HREF=javascript:alert('=VLOOKUP(E14,$A$13:$B$18,2,0)')><FONT FACE=Arial COLOR=#000000>Jack's imposter</FONT></A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=VLOOKUP(F14,$B$13:$C$18,2,0)')><FONT FACE=Arial COLOR=#000000>26</FONT></A></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>15</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=RANK(C15,$C$13:$C$18)+COUNTIF($C$13:C15,C15)-1')><FONT FACE=Arial COLOR=#000000>6</FONT></A></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>George</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>9</FONT></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>3</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><A HREF=javascript:alert('=VLOOKUP(E15,$A$13:$B$18,2,0)')><FONT FACE=Arial COLOR=#000000>Ringo</FONT></A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=VLOOKUP(F15,$B$13:$C$18,2,0)')><FONT FACE=Arial COLOR=#000000>21</FONT></A></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>16</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=RANK(C16,$C$13:$C$18)+COUNTIF($C$13:C16,C16)-1')><FONT FACE=Arial COLOR=#000000>3</FONT></A></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Ringo</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>21</FONT></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>4</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><A HREF=javascript:alert('=VLOOKUP(E16,$A$13:$B$18,2,0)')><FONT FACE=Arial COLOR=#000000>Paul</FONT></A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=VLOOKUP(F16,$B$13:$C$18,2,0)')><FONT FACE=Arial COLOR=#000000>19</FONT></A></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>17</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=RANK(C17,$C$13:$C$18)+COUNTIF($C$13:C17,C17)-1')><FONT FACE=Arial COLOR=#000000>1</FONT></A></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Jack</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>26</FONT></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>5</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><A HREF=javascript:alert('=VLOOKUP(E17,$A$13:$B$18,2,0)')><FONT FACE=Arial COLOR=#000000>John</FONT></A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=VLOOKUP(F17,$B$13:$C$18,2,0)')><FONT FACE=Arial COLOR=#000000>18</FONT></A></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>18</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=RANK(C18,$C$13:$C$18)+COUNTIF($C$13:C18,C18)-1')><FONT FACE=Arial COLOR=#000000>2</FONT></A></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Jack's imposter</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>26</FONT></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>6</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><A HREF=javascript:alert('=VLOOKUP(E18,$A$13:$B$18,2,0)')><FONT FACE=Arial COLOR=#000000>George</FONT></A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=VLOOKUP(F18,$B$13:$C$18,2,0)')><FONT FACE=Arial COLOR=#000000>9</FONT></A></TD></TR><TR><TD COLSPAN=8><U>Sheet1</U></TD></TR></TABLE>
<FONT COLOR=#339966>To see the formula in the cells just click on the cells hyperlink or click the Name box</FONT>
<FONT COLOR=RED SIZE=2>PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! Otherwise, the error of JavaScript occurs.</FONT>

<FONT COLOR=#339966 SIZE=1>The above image was automatically generated by [HtmlMaker 2.0�@BETA]</FONT><FONT COLOR=#339966 SIZE=1>If you want FREE SOFT, <A HREF=http://www28.tok2.com/home/corosuke/HtmlMaker.htm>click here</A> to download</FONT>
<FONT COLOR=#339966 SIZE=1>This code was graciously allowed to be modified: by All credit to <A HREF=mailto:corosuke@chan.co.jp>Colo</FONT>
</CENTER>

in both examples you should be able to copy the ranking formula in green down, ditto the pink formula which then sorts them automatically
 

Charlie_Chalk

Board Regular
Joined
Sep 15, 2002
Messages
93

ADVERTISEMENT

Thanks!

I've got to get my head round this, I'll go and grab a can of lager! (always helps the thinking process!)

Chaz
 

Chris Davison

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,790
Grolsche, here.... always good for the brain cells...

okay :

RANK will rank a number based on a set of numbers and return it's relative rank within that set

so ranking 6.8 in the following set 5.3, 2.7, 1.9, 5.5 and 6.8 will rank it number 1 as it's the highest.

syntax is =RANK(range, number to rank, order)

where order is either ommitted, 0 or 1

if it's ommitted or 0, the ranking will be based on an descending scale (highest first)

if 1 is used, the ranking will be based on an ascending scale (lowest first)

mtf
 

Chris Davison

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,790

ADVERTISEMENT

so in our first basic example, we are asking it to rank the number for each team within the range of numbers of each team..... omitting any 3rd arguement to default to descending (highest is first)

it brings back Paul as number 1 as it is the highest within the group, with Ringo being last (no surprise there then....)

since these will always change dependant upon the points, as the points change, this rank will change over time

now : over on the right, we have a static list of 1,2,3,4..... these are hard-coded and will not change. What we do now is use VLOOKUP to look at 1, find it in the table on the left and bring back the name next to number 1 (basic VLOOKUP functionality)

we can copy this formula down as the VLOOKUP cell will be relative while the VLOOKUP range is absolute due to us "anchoring" it with $ signs..... the second pink cell repeats this process, VLOOKUP-ing the name and returning the points associated with it

(if you're not familiar with VLOOKUP, this will be double-dutch : let me know and I'll send you my VLOOKUP example, written in plain english rather than anything remotely like Excel's help files!)

but again, since the 1-4 are static and the VLOOKUP references whatever they relate to, as the points change and the relative rankings change, the VLOOKUPS will change : ie they autoupdate, but always 1 to 4, which is what you're after : an automatic updating 1-4 league table

I'll let that sink in before I go on to dealing with ties, although it's not that difficult..... Dealing with points ties that then default to (say) goal difference or fouls committed is a bit trickier, but easily achievable : just shout when you reach that bridge
 

Charlie_Chalk

Board Regular
Joined
Sep 15, 2002
Messages
93
Grolsh! if only I could afford it, Carling I'm affraid!

I've taken the spread sheet sample you've supplied and translated it into my league layout.

I have used VLOOKUP in the past, but only scratched at the surface.

I think my problem was thinking there is one function that would solve it, rather than combining functions (not that I would have worked it out even if I had been a bit more lateral in my thoughts!).

If you could go through the last bit I'd appreciate it.

Chaz
 

Chris Davison

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,790
the ties bit ?

not sure if my second follow up was posted before your reply...
This message was edited by Chris Davison on 2002-09-16 12:44
 

Forum statistics

Threads
1,144,052
Messages
5,722,242
Members
422,418
Latest member
Chipsy

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
Top