+ Reply to Thread
Results 1 to 6 of 6

Breaking a tie- Cross Country scoring excel sheet

  1. #1
    Registered User
    Join Date
    07-11-2017
    Location
    Chicago
    MS-Off Ver
    15.34
    Posts
    3

    Breaking a tie- Cross Country scoring excel sheet

    I am building an excel sheet that will score cross country meets. I know there are apps and software you can buy, but I have learned a ton of excel by making it myself.

    I have an excel sheet that will score 8 teams perfectly. However, I cannot figure out how to break a tie. Without going into too much detail of scoring a meet. here is what I need. See the picture for reference. Teams are ranked in column M, team names in N and scores in O. As you can see two teams have a score of 75 and are both ranked 2nd. In cross county there is no tieing. You break this tie by which teams 6th runner came in first (runners 1-5 make up your score so if you tie in score of runners 1-5 the tieing team with their 6th runner finishing first wins).

    I have each teams 6th runner's place in column P (note some teams may not have a 6th runner and this should be taken into account).

    Basically I need to re-rank any ties in column O based on the lowest number JUST BETWEEN THE TIEING TEAMS in column P (the 6th place runner). My problem is I cannot figure out how to write a function that only breaks the tie and reranks when there is a tie. I do not want all teams reranked based on the 6th runner.

    Any help here would be gladly welcomed.

    Screen Shot 2017-07-11 at 3.18.41 PM.png

  2. #2
    Forum Contributor
    Join Date
    03-22-2017
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    230

    Re: Breaking a tie- Cross Country scoring excel sheet

    try this,

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    it check the rank whether have duplicate,

    if there have, it sum the original rank with rank of 6th that have tie rank in 5th.

    if not duplicate, it show original rank.
    Last edited by BoredWorker; 07-11-2017 at 11:29 PM.
    Hope you can learn every time you visit here.

    If you still confuse on how it work, kindly ask or go to
    i) Formula - Formula (Ribbon) > Formula Auditing (Section) > Evaluate Formula > Evaluate; or
    ii) VBA/Code - Click F8 to see how it work step by step.

    It it take care of your question, Please:
    Mark tread as [Solved] [Thread Tools->Mark thread as Solved]
    ;and
    Click *Add Reputation to thank anyone solved your question.

  3. #3
    Registered User
    Join Date
    07-11-2017
    Location
    Chicago
    MS-Off Ver
    15.34
    Posts
    3

    Re: Breaking a tie- Cross Country scoring excel sheet

    This did not rerank based on column P.

    This is what I need it to do.
    1. Recognize any ties in column M
    2. If any 2+ teams tie in column M rerank among the tying teams only based on lowest number in column P
    3. The rerank should not interfere with any team not originally tied in column M
    4. Therefore if you look in the image for reference schools Ben Franklin & CP Taft tied in column M with 75 points for a tie of 2nd place. Therefore those two should be reranked among themselves based on column P (6th runner).
    5. This formula should then rerank Ben Franklin #2 because their runner (27th place) came in before CP Taft 6th runner (40th place).
    6. CP Taft should then be ranked 3rd. (As you can see the formula I'm currently using skips 3rd place because of a 2 way tie for 2nd place which I am attempting to eliminate.

  4. #4
    Forum Contributor
    Join Date
    03-22-2017
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    230

    Re: Breaking a tie- Cross Country scoring excel sheet

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    mistake made, (previous formula is make for descending order.

    1.PNG

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2404
    Posts
    13,406

    Re: Breaking a tie- Cross Country scoring excel sheet

    This uses a helper and re-ranks that.

    In M2
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The re-rank in L2
    Formula: copy to clipboard
    Please Login or Register  to view this content.




    K
    L
    M
    N
    O
    P
    1
    Original
    ReRanked
    Helper
    Names
    2
    5
    5
    5.00001
    A
    121
    32
    3
    #VALUE!
    #VALUE!
    B
    4
    2
    2
    2.00003
    C
    75
    27
    5
    6
    6
    6.00004
    D
    129
    43
    6
    7
    7
    7.00005
    E
    187
    49
    7
    1
    1
    1.00006
    F
    57
    23
    8
    2
    3
    2.00007
    G
    75
    40
    9
    4
    4
    4.00008
    H
    95
    37
    Dave

  6. #6
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Breaking a tie- Cross Country scoring excel sheet

    Hi all- Best approach is alter team-score calculation to include 6th TEAM-PLACE divided by 10000 (as per @FlameRetired post#5). Then there won't be ANY ties, as the fractional part will differentiate them without otherwise affecting ranks. You could format the cells to hide the decimal part.
    Last edited by leelnich; 07-13-2017 at 08:02 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Need to show particular country name in attached sheet as per ID
    By abhiindia123 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-30-2013, 01:36 PM
  2. [SOLVED] Breaking up a cell containing many countries into individual country cells
    By grumpyguppy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-14-2012, 04:19 AM
  3. Trying to score a cross country race, need to skip runners after 7 of the same school.
    By sciencechickgeek in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-02-2012, 06:38 PM
  4. Excel 2007 : Penalties for cross country
    By Spike0907 in forum Excel General
    Replies: 5
    Last Post: 09-13-2010, 06:15 PM
  5. Cross-country data compare and update
    By drewship in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-16-2009, 08:14 AM
  6. Add ‘country name’ column to worksheet from a list of country codes.
    By Ben Morton in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-02-2009, 09:24 AM
  7. Auto Scoring Sheet
    By Missile in forum Excel General
    Replies: 0
    Last Post: 04-20-2005, 06:06 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1