+ Reply to Thread
Results 1 to 6 of 6

Assigning points to rankings (averaging ties)

  1. #1
    Registered User
    Join Date
    11-21-2011
    Location
    MI, USA
    MS-Off Ver
    Excel 2003
    Posts
    2

    Assigning points to rankings (averaging ties)

    I'm creating a spreadsheet for a golf league that my buddies and I are trying to put together. Players will be awarded points based upon their finish each week (similar to the FedEd Cup). Attached is what I have done so far. I have entered mock scores for round one for each player.

    The "Results" worksheet is where I'm in need of help. At the bottom of the worksheet I have indicated the number of points that will be awarded to the given rank per week (1st=500, 2nd=300, ect.). In the event of a tie, I would like the point totals to be added together and divided among all golfers tied for a particular position at the end of a round. For example, there are two golfers tied for second. Their score would be an average of the 2nd and 3rd place scores (300+190=490/2=245).

    Thank you for your help in advance! Let me know if you need more info.
    Attached Files Attached Files

  2. #2
    Forum Expert dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Massachusetts
    MS-Off Ver
    365(PC) V:2202
    Posts
    1,468

    Re: Assigning points to rankings (averaging ties)

    i believe this IF statement is what you are looking for.

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

  3. #3
    Forum Expert dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Massachusetts
    MS-Off Ver
    365(PC) V:2202
    Posts
    1,468

    Re: Assigning points to rankings (averaging ties)

    actually that is not yet correct, there is an issue when you have 3 or more tied for the same position

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Assigning points to rankings (averaging ties)

    Welcome to the board.

    Row\Col
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    2
    Round 1
    3
    Net
    Rank
    Pts
    4
    Player 1
    41
    3
    145.0
    5
    Player 2
    47
    7
    87.5
    6
    Player 3
    40
    1
    400.0
    7
    Player 4
    41
    3
    145.0
    8
    Player 5
    50
    9
    72.5
    9
    Player 6
    40
    1
    400.0
    10
    Player 7
    50
    9
    72.5
    11
    Player 8
    41
    3
    145.0
    12
    Player 9
    50
    9
    72.5
    13
    Player 10
    50
    9
    72.5
    14
    Player 11
    47
    7
    87.5
    15
    Player 12
    44
    6
    100.0
    16
    17
    18
    19
    Rank
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    20
    Points
    500
    300
    190
    135
    110
    100
    90
    85
    80
    75
    70
    65


    The formula in E4 and copied down is

    =SUM(INDEX($C$20:$N$20, D4):INDEX($C$20:$N$20, D4 + COUNTIF(D$4:D$15, D4) - 1)) / COUNTIF(D$4:D$15, D4)
    Last edited by shg; 01-19-2016 at 01:28 PM.
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Registered User
    Join Date
    11-21-2011
    Location
    MI, USA
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Assigning points to rankings (averaging ties)

    That'll do it! Thank you both for your help!

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Assigning points to rankings (averaging ties)

    You're welcome.

+ 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. Football league rankings (Points > H2H > GD > GS)
    By cenkbut in forum Excel General
    Replies: 0
    Last Post: 05-23-2014, 04:48 AM
  2. Rankings formulas and points systems
    By SJB777 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-01-2014, 11:34 AM
  3. Merging/Averaging a List of Rankings
    By HowardRoark in forum Excel General
    Replies: 15
    Last Post: 10-31-2012, 11:17 PM
  4. Assigning points values to data to set performance rankings...
    By stateofstatic in forum Excel General
    Replies: 3
    Last Post: 01-28-2012, 09:23 PM
  5. Automatic averaging for ties
    By WDufault in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-10-2011, 11:57 PM
  6. Assigning Multiple Rankings to Data based on different columns
    By Mike610 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-23-2008, 07:43 PM
  7. [SOLVED] assigning points to rankings
    By Nan-C in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-15-2006, 03:10 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