ranks

[abee4life]

By using either rank or vlookup, how can i do a table that calculates in order

* Points
* Goal Difference
* Goals Scored
* Alphabetical Order

I can do 1 for points, then goal difference, and 1 for points and goal scored, but cant do one for all of them together.

I can send the table to anyone who wants it!

 

[jonesy241]

I am not sure what you are trying to do. It doesn't sound like you want to sort but I don't know what you mean by not being able to rank these.
Could you post the data (using colo's HTML maker) and explain a little more about what you want.

 

[abee4life]

ive tried using the html maker before with no success...

basically i want to make a league table for games in League 1 of the English football(soccer) league.

The way the league goes about sorting teams is

Highest Points, then if level, goal difference, then still level, goals scored, then finally alphabetical order.

 

[erik.van.geit]

ive tried using the html maker before with no success...

then try again
it's THE tool to be succesful in getting acurate response
there are plenty of possibilities to get help to make it work
On the "Excel Questions" main page there is a link to HTML Maker FAQ. You'll find out how to post correctly.
On the main page of the Forum there is a link to a Test here forum and a link to About this Board - to ask questions about HTLMMaker ...

kind regards,
Erik

 

[jonesy241]

I think I understand what you are trying to do. Check out this post and if that doesn't help you out post again.

http://www.mrexcel.com/board2/viewtopic.php?t=83667

 

[abee4life]

Thanks for all your help dandyrandy... can you please help me out this one last time and explain how to continue the fixtures down?

 

[Domenic]

Assumptions:

A1:I6 contains your table

First row contains your headers

Column A contains 'Team Name'

Column F contains 'Goal Scored'

Colum H contains 'Goal Difference'

Column I contains 'Points'


Formulas:

K2, copied down:

=RANK(I2,$I$2:$I$6)

L2, copied down:

=IF(COUNTIF($I$2:$I$6,I2)>1,MATCH(H2,LARGE(IF($I$2:$I$6=I2,$H$2:$H$6),ROW(INDIRECT("1:"&COUNTIF($I$2:$I$6,I2)))),0)-1,0)

...confirmed with CONTROL+SHIFT+ENTER

M2, copied down:

=IF(SUM(($I$2:$I$6=I2)*($H$2:$H$6=H2))>1,MATCH(F2,LARGE(IF(($I$2:$I$6=I2)*($H$2:$H$6=H2),$F$2:$F$6),ROW(INDIRECT("1:"&SUM(($I$2:$I$6=I2)*($H$2:$H$6=H2))))),0)-1,0)

...confirmed with CONTROL+SHIFT+ENTER

N2, copied down:

=IF(SUM(($I$2:$I$6=I2)*($H$2:$H$6=H2)*($F$2:$F$6=F2))>1,MATCH(COUNTIF($A$2:$A$6,"<"&A2),SMALL(IF(($I$2:$I$6=I2)*($H$2:$H$6=H2)*($F$2:$F$6=F2),COUNTIF($A$2:$A$6,"<"&$A$2:$A$6)),ROW(INDIRECT("1:"&SUM(($I$2:$I$6=I2)*($H$2:$H$6=H2)*($F$2:$F$6=F2))))),0)-1,0)

...confirmed with CONTROL+SHIFT+ENTER

O2, copied down:

=SUM(K2:N2)


Note that Column O gives you the final ranking. Adjust the ranges accordingly.


Hope this helps!

 

[jimrward]

assuming you have a table setup as previously suggested in an earlier post
with headers like

Team | Played | Won | Drawn | Lost | For | Against | Points

In the ranking column put the following, substitute names for actual cell locations and copy down.

=points+((For-Against)/100)+(For/10000)+((100-CODE(Team))/1000000)

and rank these values