+ Reply to Thread
Results 1 to 2 of 2

Rank with Substitution

  1. #1
    Registered User
    Join Date
    02-06-2004
    Posts
    1

    Rank with Substitution

    I have setup a game score/points tracking spreadsheet using the RANK function and it works well for the base points mechanism, i.e.

    1st place = 10 pts
    2nd place = 9 pts
    3rd place = 8 pts etc...

    However, the players involved have an opportunity to score bonus "quick start" points if they submit a score for the game within the first three days of starting the game in question's round. I have added a separate "Quick Start" score column to record any scores submitted within the three days.

    Now for the tricky bit. The rules require that the quick start score still be within the top three places at the end of the round (14 days) to be eligible for the quick start bonus points. So, each players quick start score (if they submitted one) has to be compared against the final "Base" scores to see if it is within the top three (so a simple IF statement with > should do this). The problem I have is how to individually do this comparison for each player and then decide what rank/position their quick start score would be IGNORING that players final score (this last part is important because the quick start score may be lower than the players final score).

    I would like to achieve this without macros.

    I have attached my template file and very much hope someone can help.

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Rank with Substitution

    I guess I'm missing what you want to "add" to this sheet. The formulas you have seem to do all that, though the long way around. The formulas I would use are:

    E6: =IF($C6>0, RANK(C6, Round_1_Scores), 0)
    F6: =IF(COUNTA($B6:$C6)=2, LOOKUP($E6, Scoring!$B$4:$B$10, Scoring!$C$4:$C$10), 0)
    G6: =IF(AND(COUNTA($B6:$D6)=3,E6<=3), LOOKUP($E6, Scoring!$E$4:$E$6, Scoring!$F$4:$F$6), 0)
    H6: =SUM(F6:G6)

    Then copy those four cells all the way down the green section.

    Then go to TOOLS > OPTIONS > VIEW > [x] Zero Values and uncheck that.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1