Excel 2003 - Find records

K

Klaus

I do have a Golf Table. Listed in columns are Members (Col A); Sex (Col B);
Results in Day1....Day 5 in Col C...G. I'd like to find the best and second
best man and woman for each day. How can I accomplish this???
Thanks for the reply
Klaus
 
B

Biff

Ok.

Now, there are a couple of ways to do this:

1. use conditional formatting to highlight the applicable scores
2. make a list of the best and second best players

#1 is the easiest. #2 will take some time (depending on how many players
typically "tie" and trying to setup a nice method for display)

Biff
 
K

Klaus

I'm looking for a formula to retrieve the names....
Can anybody help?? e.g. If(Col B="M',Large(Day1,1),"") or similar...
Thanks
Klaus
 
B

Biff

Can anybody help?? e.g. If(Col B="M',Large(Day1,1),"") or similar...

Oh, if only it was that easy! <g>

Having to account for possible ties really makes things much more
complicated.

Hmmm .... Large(Day1,1)

The best golf scores are the lowest scores so wouldn't you want
Small(Day1,1) ?

That's what I based my solution on, the lowest score.

Anyhow.....

Assume your table is in the range A1:G10. A1:G1 are column headers.

First, you have to extract the LOWEST score than the next LOWEST score for
the men than the women. I did it like this:

*ALL* of the formulas needed to do this are array formulas. They need to be
entered with the key combo of CTRL,SHIFT,ENTER.

B12 = lowest Male
C12 =MIN(IF(B2:B10="m",IF(C2:C10>0,C2:C10)))

B13 = 2nd lowest Male
C13 =MIN(IF(B2:B10="m",IF(C2:C10>C12,C2:C10)))

B15 = lowest Female
C15 =MIN(IF(B2:B10="f",IF(C2:C10>0,C2:C10)))

B16 = 2nd lowest Female
C16 =MIN(IF(B2:B10="f",IF(C2:C10>C15,C2:C10)))

Now, to extract the players names:

lowest Male (and ties)

=IF(ISERROR(SMALL(IF(B$2:B$10="m",IF(ISNUMBER(C$2:C$10),IF(C$2:C$10=C$12,ROW($1:$9)))),ROW(1:1))),"",INDEX(A$2:A$10,SMALL(IF(B$2:B$10="m",IF(ISNUMBER(C$2:C$10),IF(C$2:C$10=C$12,ROW($1:$9)))),ROW(1:1))))

Copy down as needed to cover the number of possible ties. Could that be 5,
10 ?

2nd lowest Male (and ties)

=IF(ISERROR(SMALL(IF(B$2:B$10="m",IF(ISNUMBER(C$2:C$10),IF(C$2:C$10=C$13,ROW($1:$9)))),ROW(1:1))),"",INDEX(A$2:A$10,SMALL(IF(B$2:B$10="m",IF(ISNUMBER(C$2:C$10),IF(C$2:C$10=C$13,ROW($1:$9)))),ROW(1:1))))

Again, copy down to cover any possible ties.

lowest Female (and ties)

=IF(ISERROR(SMALL(IF(B$2:B$10="f",IF(ISNUMBER(C$2:C$10),IF(C$2:C$10=C$15,ROW($1:$9)))),ROW(1:1))),"",INDEX(A$2:A$10,SMALL(IF(B$2:B$10="f",IF(ISNUMBER(C$2:C$10),IF(C$2:C$10=C$15,ROW($1:$9)))),ROW(1:1))))

Copy down to cover any possible ties.

2nd lowest Female (and ties)

=IF(ISERROR(SMALL(IF(B$2:B$10="f",IF(ISNUMBER(C$2:C$10),IF(C$2:C$10=C$16,ROW($1:$9)))),ROW(1:1))),"",INDEX(A$2:A$10,SMALL(IF(B$2:B$10="f",IF(ISNUMBER(C$2:C$10),IF(C$2:C$10=C$16,ROW($1:$9)))),ROW(1:1))))

Copy down to cover any possible ties.

See, I told you CF was much easier!

Or, use a pivot table!

Biff
 
Top