Excel 2007 : Solving Ties with LARGE/SMALL function

[mkenaw]

I coach a little league baseball team and over the years have added on to my stats file. I do a sports article after each game listing leaders in the various stat categories and with the most recent season over, I'd like to make my life easier going foward and just have that info listed out for me.

At issue is the LARGE / SMALL function ... there are various instances where more than one player has '8 hits' for example.

Is there an easy way to list each player's name instead of showing the first instance player's name multiple times?

Thanks
Matt

[MarvinP]

Hi Matt,

To eliminate ties how about adding a small fraction to things that make sense.
Example two players have 8 hits but one has 10 at bats and the other has 15 at bats.

I'd add hits plus 1/(at bats * 10) to his hits. This could easily eliminate ties so your formulas would work and still make sense.

[daddylonglegs]

Hello Matt,

You can use this formula in C3

=INDEX(Stats!B$5:B$16,SMALL(IF(Stats!FJ$5:FJ$16=B3,ROW(Stats!FJ$5:FJ$16)-ROW(Stats!FJ$5)+1),COUNTIF(B$3:B3,B3)))

It's an "array formula" that needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces appear around the formula in the formula bar.

Copy down to C14 and use the same method for other columns. Note: I took the name from column B, you don't need to have IL if you don't want.....

[mkenaw]

I copied the suggested formula into C3, but it results in a #VALUE! with note that says "A Value used in the formula is of the wrong data type"

I'll work through the formula wizard though with that thought process - thanks

Updated ... Got It! ... Beauty ... Thanks