+ Reply to Thread
Results 1 to 8 of 8

Display top 3 scores in speadsheet while resolving "ties" by 2nd field column

  1. #1
    Registered User
    Join Date
    07-01-2012
    Location
    LEBANON, PA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Display top 3 scores in speadsheet while resolving "ties" by 2nd field column

    I have created an Excel workbook for a motorcycle judging contest. Each bike has a row with field info, including scores. I am using the LARGE function to find the top 3 places, where column V lists sum of the scores:
    Please Login or Register  to view this content.
    What I need help with:
    1. Resolve ties by referencing the MILEAGE field; the higher mileage takes precedence.
    2. Pull NAME & LOT# field contents from the cells referenced by the LARGE function.

    Any help is appreciated.
    Last edited by STEELCASTOR; 07-12-2012 at 09:29 AM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Display top 3 scores in speadsheet while resolving "ties" by 2nd field column

    you could create a tie-breaker in a helper column by adding (or multiplying) the score with the mileage, and you can use either vlookup, or index/match to return the other data you waht.

    However, if you stilll have trouble sorting this out, it will be much easier to help you if you upload a sample workbook for us to work with
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Display top 3 scores in speadsheet while resolving "ties" by 2nd field column

    If you're familiar with pivot table (in combination with max) you also get te desired result.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  4. #4
    Registered User
    Join Date
    07-01-2012
    Location
    LEBANON, PA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Display top 3 scores in speadsheet while resolving "ties" by 2nd field column

    Thanks for you input! I've considered your suggestions, and with further research have come up with the following solution. Attached is my excel file.

    I added a helper table (outlined in green) to address "ties" by using the RANK function on the total score. Then discerning occurrences of any rank more than once and adding a decimal (calculated by the mileage column). The result is a column of non-repeating ranks by two criteria.

    RANK:
    Please Login or Register  to view this content.
    TIE BREAKER:
    Please Login or Register  to view this content.
    FINAL RANK:
    Please Login or Register  to view this content.
    I then created another helper table (outlined in red) that returns info on the top three scores. The CELL function is used to find the reference, column and row. INDIRECT is used to find the content. My intent was to use these cell packets of info to return the LOT# of the appropriate place winner, in the table at the bottom of the page. All my efforts with vlookup, hlookup, index, and indirect get value or n/a errors. Further guidance would be appreciated.
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Display top 3 scores in speadsheet while resolving "ties" by 2nd field column

    You get better help if you addes your desired answer in the worksheet.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Display top 3 scores in speadsheet while resolving "ties" by 2nd field column

    i presume a high score is better than a low score?

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Display top 3 scores in speadsheet while resolving "ties" by 2nd field column

    see if the attached gives you what you want? i modified some of the formulas in your green table to get rid of the #NA's.

    also, not sure where to reference the data for the "Bike", but maybe you can use what i gave you in B52 as a starting point for that?
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    07-01-2012
    Location
    LEBANON, PA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Display top 3 scores in speadsheet while resolving "ties" by 2nd field column

    Thanks for the input. Your change to green table did remove N/A's. But your formula to return the LOT# made me realize that the root of that problem lay in the MILEAGE field. By replacing the "empty" cells with a "zero", in that field, the subsequent formulas in the green table would return a valid result without modification. Also, by substituting the SMALL for the LARGE in the LOT# function,I got the results I'm looking for. The references for the NAME and BIKE fields will come from an Access database that I'm creating. See image and attached file. Thanks for all your helpillustration_1.jpg
    Attached Files Attached Files

+ 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