Ranking query

[thedaddy]

I have a spreadsheet which we use to rank scores for our golf society.
At the start of our season it was decided that if anyone of the top 3 scores were tied we would separate them using a second criteria(back nine score). Any scores from position 4 down would still be tied.

From searching this forum i have managed to separate ties using the 'back nine' second criteria(using a hidden helper column) but only for all the ranking positions not just the top 3.

Can it be done??

I have attached the file.

Many thanks

[martindwilson]

this should work but its still possible is it not to have a 4 way split?

[thedaddy]

Thanks for your reply, the formula below is the one you've entered into sheet


IF(OR(AS6=LARGE($AS$6:$AS$19,1),AS6=LARGE($AS$6:$AS$19,1),AS6=LARGE($AS$6:$AS$19,1)),AS6+AT6,AS6)

should it read

IF(OR(AS6=LARGE($AS$6:$AS$19,1),AS6=LARGE($AS$6:$AS$19,2),AS6=LARGE($AS$6:$AS$19,3)),AS6+AT6,AS6)

You could have a 4 way split but it happens so rarely it wouldn't be a problem

[martindwilson]

i wasnt thinking straight but now i am ,you can do away with the or() altogether and just use =IF(AS6=LARGE($AS$6:$AS$19,1),AS6+AT6,AS6)
or even
=IF(AS6=MAX($AS$6:$AS$19),AS6+AT6,AS6)
as you are only testing the fact that it is equal to the biggest number !

[thedaddy]

Not sure how your formula only separates ties in the top 3 and nowhere else. However it has helped find a way to do it:

In cell G4 and copied down

=IF(OR(E4=LARGE($E$4:$E$17,1),E4=LARGE($E$4:$E$17,2),E4=LARGE($E$4:$E$17,3)),RANK(E4,$E$4:$E$17)*10000+RANK(F4,$F$4:$F$17)*20,RANK(E4,$E$4:$E$17)*10000)


There's probably a less long winded way but from the little testing i've done it seems to do the job!!

[martindwilson]

my thinking if its the biggest/largest then it will always match others that have the same value ergo they are also the largest/bigest.

[thedaddy]

on the example attatched, your method doesn't split the ties in 2nd place

[DonkeyOte]

It might be me but isn't it a case of saying... RANK by Helper if <=3 scores equate to current points else rank off points ?

So using the example layout... I would use a helper column of:

AU6: =AR6+(AT6/1000)
copied down

Then for the RANK

AQ6: =IF($AS6=0,"Did not play",IF(COUNTIF($AR$6:$AR$28,">"&$AR6)>=3,RANK($AR6,$AR$6:$AR$28),RANK($AU6,$AU$6:$AU$28)))
copied down

You could probably come up with a clever way of doing all in one cell if you really wanted to but the above may suffice ?

[thedaddy]

cheers does the job as well!!

[martindwilson]

yep i see ,i just read it as top three tied, didnt consider 1 2 2