+ Reply to Thread
Results 1 to 4 of 4

Fishing League - Need a formula to list winning team names and highest score

  1. #1
    Registered User
    Join Date
    04-18-2012
    Location
    Michigan
    MS-Off Ver
    Excel 2003
    Posts
    2

    Fishing League - Need a formula to list winning team names and highest score

    I am putting a fishing league together. Each night teams will compete against each other. Scoring will be based on entering 5 fish per team, with 6 points per fish awarded for a salmon, and 3 points awarded for a trout. We will award cash prizes for 1st, 2nd, and 3rd place based on total entry fees per night. In the event of a tie, based on total points per night, the team who has the longest fish, measured to 1/8th inch will win the tie. There isnt much time to calculate winners at the end of the night and I want to easily track each week's stats, so I want to use a spreadsheet to do it. I need help writing a formula that looks through a list of scores, picks the highest three scores, and populates three rows of cells with the team name and their points. ALSO, in the event of a tie, I want the winner to be decided based on the longest fish. I have attached a copy of the spreadsheet that I want to use. Can anyone help me write the formulas to populate the 1st through 3rd places with team name and total points, AND evaluate a tie breaker by the longest fish?
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor AlvaroSiza's Avatar
    Join Date
    09-19-2007
    Location
    Staffordshire
    MS-Off Ver
    2007
    Posts
    591

    Re: Fishing League - Need a formula to list winning team names and highest score

    The crux of the multi-criteria ranking is accomplished in helper column H to the right of your score sheet via:

    Please Login or Register  to view this content.
    VLOOKUP's are then used to return the 1st, 2nd, and 3rd place finishers using Columns H:K. These can remain hidden from view if you prefer. Hope this helps. Please report back the results.

    -as-

    Points Summary.xls
    Perhaps it was the Noid who should have avoided me...
    If you are satisfied with my solution click the small star icon on the left. Thanks
    1. Make a copy of your workbook and run the following code on your copy (just in case)
    2. With excel open, press ALT+F11 to open the Visual Basic Editor (VBE). From the "Insert" menu, select "Module".
    3. Paste the code from above into the empty white space. Close the VBE.
    4. From the developer tab, choose "Macros", select the Sub Name, and click "Run".

  3. #3
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Fishing League - Need a formula to list winning team names and highest score

    If you add a new column G, called say Rank Score with a formula in G2 of

    =(C2*6+D2*3)*1000+E2*8

    Then you can use simple formulae of

    =INDEX($A$2:$A$12,MATCH(LARGE($G$2:$G$12,ROW(A1)),$G$2:$G$12,0))

    in B16, and

    =VLOOKUP(B16,$A$1:$G$12,6,FALSE)

    in C16, which you copy down to B18:C18.

  4. #4
    Registered User
    Join Date
    04-18-2012
    Location
    Michigan
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Fishing League - Need a formula to list winning team names and highest score

    WOW, thank you both for helping me!!

+ 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