Large formula with a condition on another column

[agoch]

Hi all,


I am hoping someone could help, I am creating a spreadsheet for softball stats for a team I coach. I am trying to create a seperate sheet ied to the main stats that list the top 5 in each category. I am using the Large formula to create my top 5, but I wanted to put a condition of another column (at bats). I am wanting to qualify the top 5 by at bats for example minimum requirements to qualify for the top 5 would be 2 at bats for each game played. the better the stats i can provide, the easier it is for the girls to give them to college recruiters. Below is my main data. Thanks in advance


Name G AB H HR BB RBI TB AVG OB% SLG%

Katie 4 10 1 0 1 0 1 .100 .308 .100

Dana 4 11 3 0 1 0 3 .273 .333 .273

Mac 4 10 2 1 1 2 5 .200 .273 .500

Jamie 4 8 2 0 3 1 2 .250 .455 .250

Marissa 3 8 3 0 0 0 4 .375 .375 .500

Alyse 4 7 4 0 2 6 5 .571 .600 .714

Nikki 4 10 7 0 0 0 7 .700 .700 .700

Carli 4 8 0 0 0 0 0 .000 .000 .000

Chelsea 4 7 2 1 0 1 5 .286 .286 .714

Sam 3 1 0 0 0 0 0 .000 .000 .000

Fahlen 4 4 2 0 0 0 3 .500 .500 .750

Nicole 2 3 1 0 0 0 2 .333 .250 .667


Thanks

 

[Hui]

Agoch

Wouldn't a simple sort by G and AB as a second column in the sort do the job?

 

[agoch]

I don't believe so and maybe I am making it more difficult. What I am trying to accomplish is a team leaders page. So I am doing the LARGE formula to get the top 5 in each category in one column and doing an index and match to get the name of the girl associated with that stat. problem is Alyse and Chelsea each have a slugging percentage of .714, but Alyse's name gets put on the sheet twice and no chelsea.


It should look like


Fahlen .750

Alyse .714

Chelsea .714

Nikki .700

Nicole .667


instead it shows

Fahlen .750

Alyse .714

Alyse .714

Nikki .700

Nicole .667


So I thought if I could qualify it, it would select the correct girls name


Thanks

 

[Hui]

What if you made up a helper column which is effectively the SLG field + the AB/1000 + Row Number/1000000

and then add a Rank Field based on the helper column

As Chelsea and Alyse both have the same SLG and AB, the Row() will ensure they are separated in a final ranking, or use another field /1000000


eg:

Helper M2: =RANK(K2,K$2:K$13,1)+C2/1000+ROW()/1000000

Final rank N2: =RANK(M2,$M$2:$M$13,1)

copy both down

 

[keymaster]

As Hui suggested, add a small fraction (row()/1000000 is fine) and then sort. When showing results, refer to original values. You can find similar example here:


http://chandoo.org/wp/2008/08/27/excel-kpi-dashboard-sort-2/

 

[agoch]

ok, so I added the rank which is fine, i just don't grasp the sorting part. Is it possible ot email my spreadsheet for you to look at, so you can see how I am doing my sheet? Thanks

 

[agoch]

I got this to work, thanks for your help. Great Site!