+ Reply to Thread
Results 1 to 11 of 11

Avoiding repetition in ranking

  1. 10-23-2006, 07:14 AM #1
    LarsLanghalm
    LarsLanghalm is offline
    Registered User
    Join Date
    08-12-2006
    Posts
    9

    Avoiding repetition in ranking

    I am working in a danish version of Excell so cannot post the formula in english. But I have ranked some sports clubs and want to have a list that puts the highest ranked club at the top, and naturally want teams with the same amount of points to share a position, or at least to both appear.

    If two clubs have the same amount of points only one of them appears on the list, but then twice. How do I avoid that?

    This is my formula in danish:

    =FORSKYDNING(H$3,SAMMENLIGN(MINDSTE(B$3:B$18,RĘKKE()-RĘKKE(O$3)+1),B$3:B$18,0)-1,0)

    (If someone could attach an excample it would make it a lot easier)
    Reply With QuoteRegister To Reply
  2. 10-23-2006, 07:19 AM #2
    oldchippy
    oldchippy is offline
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097

    Thumbs up

    Quote Originally Posted by LarsLanghalm
    I am working in a danish version of Excell so cannot post the formula in english. But I have ranked some sports clubs and want to have a list that puts the highest ranked club at the top, and naturally want teams with the same amount of points to share a position, or at least to both appear.

    If two clubs have the same amount of points only one of them appears on the list, but then twice. How do I avoid that?

    This is my formula in danish:

    =FORSKYDNING(H$3,SAMMENLIGN(MINDSTE(B$3:B$18,RĘKKE()-RĘKKE(O$3)+1),B$3:B$18,0)-1,0)

    (If someone could attach an excample it would make it a lot easier)
    Hello LarsLanghalm,

    Take a look at this link from a previous question a few days ago, it may help you

    http://www.excelforum.com/showthread...hlight=ranking

    oldchippy
    Reply With QuoteRegister To Reply
  3. 10-23-2006, 08:04 AM #3
    LarsLanghalm
    LarsLanghalm is offline
    Registered User
    Join Date
    08-12-2006
    Posts
    9
    Thanks... missed it. But I'm having a little trouble still... I'm stranded here so far:

    =OFFSET(H$3,MATCH(SMALL(B$3:B$18,RĘKKE()-ROW(O$3)+1),B$3:B$18,0)-1,0)+COUNTIF(B$3:B$18,B3)+1

    (the above formula doesnt work)
    Reply With QuoteRegister To Reply
  4. 10-23-2006, 08:38 AM #4
    Bryan Hessey
    Bryan Hessey is offline
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by LarsLanghalm
    Thanks... missed it. But I'm having a little trouble still... I'm stranded here so far:

    =OFFSET(H$3,MATCH(SMALL(B$3:B$18,RĘKKE()-ROW(O$3)+1),B$3:B$18,0)-1,0)+COUNTIF(B$3:B$18,B3)+1

    (the above formula doesnt work)

    =OFFSET(H$3,MATCH(SMALL(B$3:B$18,RĘKKE()-ROW(O$3)+1),B$3:B$18,0)-1,0)+COUNTIF(B$3:B$18,B3)+1

    what is RĘKKE()

    and why specify O$3 in the row? - the original was to increment the row() for each row, thus giving an increasing number, howerver one of these would seem to be superfluous.

    hth
    ---

    =OFFSET(H$3,MATCH(SMALL(B$3:B$18,RĘKKE()-ROW(O$3)+1),B$3:B$18,0)-1,0)+COUNTIF(B$3:B$18,B3)+1

    =OFFSET(H$3,MATCH(SMALL(B$3:B$18,RĘKKE()-ROW(O$3)+1),B$3:B$18,0)-1,0)+COUNTIF(B$3:B$18,B3)+1

    were you trying for:

    Offset ( H$3 , red-row-offset , blue-column-offset ) + Countif ?

    or for what ?

    ---
    Last edited by Bryan Hessey; 10-23-2006 at 08:56 AM.
    Si fractum non sit, noli id reficere.
    Reply With QuoteRegister To Reply
  5. 10-23-2006, 08:55 AM #5
    LarsLanghalm
    LarsLanghalm is offline
    Registered User
    Join Date
    08-12-2006
    Posts
    9
    Quote Originally Posted by Bryan Hessey
    =OFFSET(H$3,MATCH(SMALL(B$3:B$18,RĘKKE()-ROW(O$3)+1),B$3:B$18,0)-1,0)+COUNTIF(B$3:B$18,B3)+1

    what is RĘKKE()

    and why specify O$3 in the row? - the original was to increment the row() for each row, thus giving an increasing number, howerver one of these would seem to be superfluous.

    hth
    ---
    Ohh... sorry RĘKKE is ROW, I translated it for this forum, missed one of them.

    Im not completely sure what you are suggesting. If it is this, then it doesn't work for me:

    =OFFSET(H$3,MATCH(SMALL(B$3:B$18,ROW()-ROW()+1),B$3:B$18,0)-1,0)

    - comes up with the same input every time (same team on all spots).

    =OFFSET(H$3,MATCH(SMALL(B$3:B$18,ROW()-ROW(O$3)+1),B$3:B$18,0)-1,0)+COUNTIF(B$3:B$18,B3)+1

    - this one seems to have a circular reference... but I cant seem to figure it out.


    Im selfmade in this (or not so made), so every reached out hand is very much appreciated!
    Reply With QuoteRegister To Reply
  6. 10-23-2006, 08:57 AM #6
    Bryan Hessey
    Bryan Hessey is offline
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by LarsLanghalm
    Ohh... sorry RĘKKE is ROW, I translated it for this forum, missed one of them.

    Im not completely sure what you are suggesting. If it is this, then it doesn't work for me:

    =OFFSET(H$3,MATCH(SMALL(B$3:B$18,ROW()-ROW()+1),B$3:B$18,0)-1,0)

    - comes up with the same input every time (same team on all spots).

    =OFFSET(H$3,MATCH(SMALL(B$3:B$18,ROW()-ROW(O$3)+1),B$3:B$18,0)-1,0)+COUNTIF(B$3:B$18,B3)+1

    - this one seems to have a circular reference... but I cant seem to figure it out.


    Im selfmade in this (or not so made), so every reached out hand is very much appreciated!
    Thats ok - checking this one . . .

    =OFFSET(H$3,

    offset row is:
    MATCH(SMALL(B$3:B$18,ROW()-ROW(O$3)+1),B$3:B$18,0)-1,

    offset column is
    0)

    add to value gained from Offset(H$3,row,column) :
    +COUNTIF(B$3:B$18,B3)+1

    looks wrong

    --

    Perhaps

    =OFFSET(H$3,

    MATCH(SMALL(B$3:B$18,ROW()+1),B$3:B$18,0)-1+COUNTIF(B$3:B$18,B3)+1

    ,0)


    As:
    =OFFSET(H$3,MATCH(SMALL(B$3:B$18,ROW()+1),B$3:B$18,0)-1+COUNTIF(B$3:B$18,B3)+1,0)

    ---
    does that help?
    Last edited by Bryan Hessey; 10-23-2006 at 09:02 AM.
    Reply With QuoteRegister To Reply
  7. 10-23-2006, 09:37 AM #7
    LarsLanghalm
    LarsLanghalm is offline
    Registered User
    Join Date
    08-12-2006
    Posts
    9
    Quote Originally Posted by Bryan Hessey
    =OFFSET(H$3,MATCH(SMALL(B$3:B$18,ROW()+1),B$3:B$18,0)-1+COUNTIF(B$3:B$18,B3)+1,0)
    This one is accepted by Excell, but it puts the second team at the top, thus somehow passing number one, and number 12 of thirteen at spot number 2.

    What Im trying to do is this:

    B3:B18 is the teams in H3:H18's rank in the league (calculated from stats in C3:C18 to G3:G18). In P3:P18 I would like the team names (from H3:H18) position according to their position in the league (B3 is the position of the team name in H3, B4 matches H4 and so on):

    ----A----B---||------H-------||-----P
    1
    2
    3--------1----||---Club A----||--Club A
    4--------4----||---Club B----||--Club D
    5--------3----||---Club C----||--Club C
    6--------2----||---Club D----||--Club B
    etc...

    EDIT: So its the formulas for P3, P4 etc. Im looking for!
    Last edited by LarsLanghalm; 10-23-2006 at 09:49 AM.
    Reply With QuoteRegister To Reply
  8. 10-23-2006, 09:51 AM #8
    Bryan Hessey
    Bryan Hessey is offline
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by LarsLanghalm
    This one is accepted by Excell, but it puts the second team at the top, thus somehow passing number one, and number 12 of thirteen at spot number 2.

    What Im trying to do is this:

    B3:B18 is the teams in H3:H18's rank in the league (calculated from stats in C3:C18 to G3:G18). In P3:P18 I would like the team names (from H3:H18) position according to their position in the league (B3 is the position of the team name in H3, B4 matches H4 and so on):

    ----A----B---||------H-------||-----P
    1
    2
    3--------1----||---Club A----||--Club A
    4--------4----||---Club B----||--Club D
    5--------3----||---Club C----||--Club C
    6--------2----||---Club D----||--Club B
    etc...

    The formula is:
    =OFFSET(H$3,

    by row:
    MATCH(SMALL(B$3:B$18,ROW()+1),B$3:B$18 ,0)-1+COUNTIF(B$3:B$18,B3)+1

    by column:
    ,0)

    which looks ok in format, and for content has:

    =OFFSET(H$3,

    MATCH(

    SMALL(B$3:B$18,ROW()+1) ie, as you are on row 3, the 4th smallest

    ,B$3:B$18 ,0) ie select the 4th smallest match


    -1+COUNTIF(B$3:B$18,B3)+1 ie, add rows for Matches on Equal

    on column:
    ,0)
    -------------

    perhaps

    =OFFSET(H$3,MATCH(SMALL(B$3:B$18,ROW()-1),B$3:B$18 ,0)-1+COUNTIF(B$3:B$18,B3)-1,0)

    will be closer ?

    ---
    Reply With QuoteRegister To Reply
  9. 10-23-2006, 10:14 AM #9
    LarsLanghalm
    LarsLanghalm is offline
    Registered User
    Join Date
    08-12-2006
    Posts
    9
    Quote Originally Posted by Bryan Hessey
    The formula is:
    =OFFSET(H$3,

    by row:
    MATCH(SMALL(B$3:B$18,ROW()+1),B$3:B$18 ,0)-1+COUNTIF(B$3:B$18,B3)+1

    by column:
    ,0)

    which looks ok in format, and for content has:

    =OFFSET(H$3,

    MATCH(

    SMALL(B$3:B$18,ROW()+1) ie, as you are on row 3, the 4th smallest

    ,B$3:B$18 ,0) ie select the 4th smallest match


    -1+COUNTIF(B$3:B$18,B3)+1 ie, add rows for Matches on Equal

    on column:
    ,0)
    -------------

    perhaps

    =OFFSET(H$3,MATCH(SMALL(B$3:B$18,ROW()-1),B$3:B$18 ,0)-1+COUNTIF(B$3:B$18,B3)-1,0)

    will be closer ?

    ---
    I get the team that should be ranked 14th at number one... I will keep looking at it till it breaks (or I break)
    Reply With QuoteRegister To Reply
  10. 10-23-2006, 07:18 PM #10
    Bryan Hessey
    Bryan Hessey is offline
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by LarsLanghalm
    I get the team that should be ranked 14th at number one... I will keep looking at it till it breaks (or I break)
    I think for your requirement I would employ a different 'ranking breaker' to avoid the 'Match' problem and to give a more fair split on 'equal' rankings. (chronological is hardly a fair way to split equal rankings)

    Decide on what is important in the league, points for, points against, away wins, other, and finally (when all else fails) chronological.

    Divide each group to produce a diminishing scale,

    Points For / 10000
    Points Against / 100000
    Away Wins / 1000000
    Row() / 10000000

    and add that to your current ranking.

    ----------
    In the attached (in case you can't open the .xls)

    D3 =

    =(F3/10000)+(G3/100000)+(H3/10000000)+(ROW()/10000000)

    J3 =

    =OFFSET(E$3,MATCH(SMALL(C$3:C$28,ROW()-2),C$3:C$28,0)-1,0)

    In columns P and Q the result of a Sort over column C ranking (hence the detected error)

    (to show the Row offset used to find the club name).

    Row 28 - club Z should (of course) be ranked 26.
    All others, except 1, were ranked as (equal) 2nd place.

    Does this help?

    (I will still look at the other method, but it still might not prove useful)
    note, the formula at J3 has one too many zeros, but you should get the idea.

    Should be ok now (the drivvel was removed from the post)

    hth
    ---
    Attached Files Attached Files
    Last edited by Bryan Hessey; 10-23-2006 at 08:51 PM.
    Reply With QuoteRegister To Reply
  11. 10-23-2006, 08:52 PM #11
    Bryan Hessey
    Bryan Hessey is offline
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    the .xls file relating to the previous post

    ---
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1