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

1. ## 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?

2. ## 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

3. ## 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. ## Re: Fishing League - Need a formula to list winning team names and highest score

WOW, thank you both for helping me!!

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

#### 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