Excel tie-break; formula in VBA

[sfchas]

Hello,

I am basically trying to create a column of tie-break points. Below is an image of my spreadsheet. Candidates have scores and a random number assigned to them.
I'm trying to write a procedure that will loop through the scores and for tied candidates, award them "tie-break" points based on the highest random number. (The "summed" column that I have in the spreadsheet isn't necessary.)

(I apologize, but based on flickr.com community rules, I have to link the image or make the link available: www.flickr.com/photos/sfchas/1500138874

I have a formula that works for a group of tied candidates, but I'm not sure how to replicate it for the next tied group (without typing in the cell references).

=RANK(D2,D$2:D$4,1)*0.01+COUNTIF(D$2:D2,D2)-1

So, the above would be okay for a range of tied candidates, but cannot be copied to the next range. For each group of ties, points awarded should start at ".01".

I'm not sure how to replicate this formula in VBA, or if I even need VBA for this, but I definitely could use a nudge in the right direction. Any help would be appreciated - please let me know if additional information is needed. Thanks!

 

[GlennUK]

Points to apply:
=IF(COUNTIF($B$2:$B$11,B2)>1,RANK(C2,OFFSET($C$2,MATCH(B2,$B$2:$B$11,0)-1,0,COUNTIF($B$2:$B$11,B2)),1)*0.01,"")
( assuming the scores are sorted )

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.