Thread: 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:

      --------A-------- ---B--- ---C---- ---D---- ---E---- ---F----
  1   Player               A       B        C        D        E    
  2   No. of Wins         10      12       11       12       12    
  3   Tiebreaker Points   50      43       24       47       38    
  4   Score               45                                       
  5   Adjusted Score    8.9995  11.9798  10.7879  10.9998  11.9293 
  6   Winner               B

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.