RANK duplicate numbers without missing in sequence

Hi all,

First thread, so go easy!

Attempting to do a spreadsheet for my golf club, to record scores, etc...

Managed to get it to pretty much what I want despite some very extreme looking formulae!
So for some scores such as 56,56,57,57,54,54, Rank would just give me 3,3,5,5,1,1, so I have put a second column next to 56,56,57,57,54,54 where I input scores to seperate ties (better back nine scores), so in the above I would have for example 30,29 next to the two 56s, 27,29 next to the two 57s, and 27,28 next to the two 54s, so the rank would return 4,3,5,6,1,2. I have gone three stages further, so if, the back nine score was also tied, I have a column for the last 6 hole score, the last 3 hole score and even the last hole score. This is some serious formulae at this point. However, I now want the OPTION to rank the original data as 2,2,3,3,1,1 if I don't put any data in the back nine column.

How can I do this anyone?

This is so that I can input doubles scores on the same spreadsheet as singles scores, so if two people shoot the same score in singles I can input the back 9 scores etc to decide who comes out ahead, but in doubles I would just leave the back nine score blank, and then two people would share 1st in the rank, but then I need two 2nd ranks NOT 3rd ranks as it currently is.

Just so you can see how complex it is the current forumlae in the rank is here is an example:

=IF(O7="","",IF(K$2="MEDAL",RANK(O7,O$7:O$255,1)+SUMPRODUCT(--(O7=O$7:O$255),--(P7>P$7:P$255))+SUMPRODUCT(--(O7=O$7:O$255),--(P7=P$7:P$255),--(Q7>Q$7:Q$255))+SUMPRODUCT(--(O7=O$7:O$255),--(P7=P$7:P$255),--(Q7=Q$7:Q$255),--(R7>R$7:R$255))+SUMPRODUCT(--(O7=O$7:O$255),--(P7=P$7:P$255),--(Q7=Q$7:Q$255),--(R7=R$7:R$255),--(S7>S$7:S$255)),RANK(O7,O$7:O$255,0)+SUMPRODUCT(--(O7=O$7:O$255),--(P7<P$7:P$255))+SUMPRODUCT(--(O7=O$7:O$255),--(P7=P$7:P$255),--(Q7<Q$7:Q$255))+SUMPRODUCT(--(O7=O$7:O$255),--(P7=P$7:P$255),--(Q7=Q$7:Q$255),--(R7<R$7:R$255))+SUMPRODUCT(--(O7=O$7:O$255),--(P7=P$7:P$255),--(Q7=Q$7:Q$255),--(R7=R$7:R$255),--(S7<S$7:S$255))))

There are other things in there to sort out a few other issues as well, but I cant see the wood for the trees so to speak, to get it todo what I want!

I have also attached a sample from my spreadsheet to better demonstrate what I want!

many thanks if anyone out there can understand what I want and help me out!

The problem with both those suggestions appear to be the fact that some of the cells may be blank, so it returns a divide by zero error. So not sure where to go from here?

Cheers I will have a look tomorrow, way way way past my bedtime! Although if I add that into my formula it's gonna cause me a headache! Much appreciated, hope I get to the solution in the end!

I keep wanting this spreadsheet of mine to do more and more, there must be some limitations, although I am quite proud of my efforts so far, even if I do say so myself!

Thanks, it does kind of work for me just not 100%, as the following occurs:

rank	score	back 9 score
1	56
1	56
2	57	29
2	57	29
3	57	30
3	57	30
3	58
3	58

As you can see, I want the rank to return it as 4, not 3, for the two players that scored 58, but because it only looks up the score, it works it out as rank 3. I managed to get the 57/30 players to rank as three by putting in extra formulae.

Any ideas.

Im beginning to think it isnt possible because of the way I want it to manipulate the data, if its a singles compeition I need it to rank them one at a time, if there is a draw it needs to look up the next column (back 9 score), then the next if there is still a tie (back 6 holes score), etc... If its a singles stableford I need it to do the same but descending, and then if its a doubles I need it to do it as described above. Nearly got there, but not quite.

Current formula looks like this:

=IF(EM10="","",IF(EI$4="Doubles",SUMPRODUCT(--(EM$7:EM$309<>""),--(EM10>EM$7:EM$309),1/COUNTIF(EM$7:EM$309,EM$7:EM$309&""))+1+SUMPRODUCT(--(EM$7:EM$309<>""),--(EM10=EM$7:EM$309),--(EN10>EN$7:EN$309),1/COUNTIF(EN$7:EN$309,EN$7:EN$309&""))+SUMPRODUCT(--(EM$7:EM$309<>""),--(EM10=EM$7:EM$309),--(EN10=EN$7:EN$309),--(EO10>EO$7:EO$309),1/COUNTIF(EO$7:EO$309,EO$7:EO$309&""))+SUMPRODUCT(--(EM$7:EM$309<>""),--(EM10=EM$7:EM$309),--(EN10=EN$7:EN$309),--(EO10=EO$7:EO$309),--(EP10>EP$7:EP$309),1/COUNTIF(EP$7:EP$309,EP$7:EP$309&""))+SUMPRODUCT(--(EM$7:EM$309<>""),--(EM10=EM$7:EM$309),--(EN10=EN$7:EN$309),--(EO10=EO$7:EO$309),--(EP10=EP$7:EP$309),--(EQ10>EQ$7:EQ$309),1/COUNTIF(EQ$7:EQ$309,EQ$7:EQ$309&"")),IF(EI$4="Medal",RANK(EM10,EM$7:EM$309,1)+SUMPRODUCT(--(EM10=EM$7:EM$309),--(EN10>EN$7:EN$309))+SUMPRODUCT(--(EM10=EM$7:EM$309),--(EN10=EN$7:EN$309),--(EO10>EO$7:EO$309))+SUMPRODUCT(--(EM10=EM$7:EM$309),--(EN10=EN$7:EN$309),--(EO10=EO$7:EO$309),--(EP10>EP$7:EP$309))+SUMPRODUCT(--(EM10=EM$7:EM$309),--(EN10=EN$7:EN$309),--(EO10=EO$7:EO$309),--(EP10=EP$7:EP$309),--(EQ10>EQ$7:EQ$309)),IF(EI$4="Stableford",RANK(EM10,EM$7:EM$309,0)+SUMPRODUCT(--(EM10=EM$7:EM$309),--(EN10>EN$7:EN$309))+SUMPRODUCT(--(EM10=EM$7:EM$309),--(EN10=EN$7:EN$309),--(EO10>EO$7:EO$309))+SUMPRODUCT(--(EM10=EM$7:EM$309),--(EN10=EN$7:EN$309),--(EO10=EO$7:EO$309),--(EP10>EP$7:EP$309))+SUMPRODUCT(--(EM10=EM$7:EM$309),--(EN10=EN$7:EN$309),--(EO10=EO$7:EO$309),--(EP10=EP$7:EP$309),--(EQ10>EQ$7:EQ$309)),"ERR"))))

Getting a bit too complicated!!!

Ok! thanks, we don't have many doubles competitions, so the workaround will be work out the placings when there is a tie and to put 55.1,55.2,55.3,55.4 if everyone is tied for 55 for example. Nevermind, didn't really want any limitations, but if thata the only one it'll have to do!

Many thanks to Tony Valko and nflsales, both helped me understand my problem a bit more, and used nflsales formula (but took off the +1 at the end to get it to work as there are blanks in my data too). Also because it was over 300 cells, the rank came out 0.99999999998999999 not 1, and took away the conditional formatting, so just used the ROUND function to overcome this.