Function/Macro To Determine High Values From Unsorted Array

I administer a football pool and need some function/macro help. My worksheet is like the following:

Player A B C D E
No. of Wins 10 12 11 12 12
Tiebreaker Points 50 43 24 47 38

Final Score 45

I need the function(s) or macro to do the following things:

1. Compare "No. of wins" to determine the highest value. If there is only one player that has the most wins (no ties), then that player is automatically the winner.
2. Compare the "Tiebreaker" points of those who have the most wins to determine the closest value that is less than or equal to "Final Score" (the values will be unsorted, just like they are shown).
3. If all of the "Tiebreaker" points are greater than "Final Score", then determine the value closest to "Final Score."
4. Identify the player who is the winner based on who had the most wins and who was closest to the final score without going over or, if everyone went over, who was closest to the final score.
5. If I use a macro, I need to be able to run the macro to produce the result and the result needs to save with the worksheet so I can e-mail it to other people. I don't want them to have to run the macro again (once they receive the worksheet) to view the results.

I would prefer to use functions and if I have to use multiple functions in multiple cells that would be fine.

Thanks for any and all help.

Here's one way:

Please Login or Register  to view this content.

In B5 and across: =B2 - IF(B3>$B$4, 100, $B$4-B3)/100 - ABS(B3-$B$4)/10000

In B6: =INDEX(B1:F1, MATCH(MAX(B5:F5), B5:F5, 0))

That worked great! Thank you so much for your help.