K
Ken
I need help with a golf tournament spreadsheet. Golfers are in two
divisions and I can find the lowest raw score and name in each
division. Not all golfers compete each week, so there are many raw
scores of zero that need to be ignored. I use named ranges.
To determine if there is a two-way tie within the division, I have this
formula in cell B19:
=IF(MIN(IF(Division="Semi",IF(RawScores<>0,RawScores,FALSE),FALSE))=SMALL(IF(Division="Semi",IF(RawScores<>0,RawScores,FALSE),FALSE),2),"tied","no
tie for low")
The problem is finding the golfers name if there is a tie. This is the
formula I tried. It first checks to see if there is a tie by looking
at the results of the above formula, then is supposed to pull out the
name of the second person. It doesn't work, though...it just pulls out
the first name it comes to with the low score.
=IF($B$19="tied",INDEX($A$51:$H$161,MATCH(SMALL(IF(Division="Semi",IF(RawScores<>0,RawScores,FALSE),FALSE),2),RawScores,0),1),"")
I read the arbitrary lookups section on Pearson's site
(http://www.cpearson.com/excel/lookups.htm), but don't know how to
modify the formulas to fit my situation. Can anyone please help?
divisions and I can find the lowest raw score and name in each
division. Not all golfers compete each week, so there are many raw
scores of zero that need to be ignored. I use named ranges.
To determine if there is a two-way tie within the division, I have this
formula in cell B19:
=IF(MIN(IF(Division="Semi",IF(RawScores<>0,RawScores,FALSE),FALSE))=SMALL(IF(Division="Semi",IF(RawScores<>0,RawScores,FALSE),FALSE),2),"tied","no
tie for low")
The problem is finding the golfers name if there is a tie. This is the
formula I tried. It first checks to see if there is a tie by looking
at the results of the above formula, then is supposed to pull out the
name of the second person. It doesn't work, though...it just pulls out
the first name it comes to with the low score.
=IF($B$19="tied",INDEX($A$51:$H$161,MATCH(SMALL(IF(Division="Semi",IF(RawScores<>0,RawScores,FALSE),FALSE),2),RawScores,0),1),"")
I read the arbitrary lookups section on Pearson's site
(http://www.cpearson.com/excel/lookups.htm), but don't know how to
modify the formulas to fit my situation. Can anyone please help?