yeah, I was doing !
Okay, so we have two ties, both on 11 points
if there's no other way of seperating them, we can use the COUNTIF function to seperate them.
COUNTIF syntax goes like : =COUNTIF(range, item)
so countif(B1:B10,"hello") : this will count all "hello"s in range B1 to B10
or countif(B1:B10,B1) : this will count the occurances of whatever is in cell B1 in the range B1 to B10
so what we do in this case (in our John, Paul, George and Ringo data) is to make our range only one line deep next to our John entry :
=countif($B$1:B1,B1)
this is asking : how many times does the value in B1 occur, in the range B1 to B1
the answer is obviously, once.
NOw, notice, we have anchored the first part of the range, but not the second part. This means that as we drag the formula down, the second part of the range will increase to the next row.
next row becomes :
=COUNTIF($B$1:B2,B2)
asking, how many times does the value in B2 occur in the range B1 to B2
*more grolsche*
at this point, lets assume that both the values in B1 and B2 were the same....(eleven)
in the first formula, we got an obvious answer of 1....
in our second formula, we got an answer of 2 as it occured twice....
if we copied it down again and there was a third eleven, we would get 3 returned as it's counting the values of "11" on an ever-increasing range
this will carry on ad finitum for any amount of duplicates..... if there's 29 of them, they will return values from 1 all the way through to 29 as each range it is looking in will get one row deeper....
so bearing in mind, they all already have a rank of 2 (being 2nd highest points), they now all also have a count that goes up by 1 each time......
so with 5 second place guys, they now each also have a count : 1 to 5, which we can ADD to that 2nd place ranking....
going back to our Beatles...
we have 2 second place guys, which will attract a count of 1 and 2 respectively..... if we ADD that count to their rank, you will see it will be more than the next rank (ie Ringo's 2 will have 2 added, which is 4.... this is no good as John is already 4.....) so, we get round this by just deducting 1 from the count....
=countif($B$1:B1,B1)-1 etc etc etc
then we add this result to the rank to get a true ranking, eliminating duplicates
=RANK(B1,$B$1:$B$4)+(COUNTIF($B$1:B1,B1)-1)
in John's case this becomes : 4 + (1-1)..... =..... 4 + 0...... =...... 4
in Paul's case it's : 2 + (1 - 1)....... = .....2 + 0..... = .....2
In George's case it's : 1 + (1-1)....... = ...... 1 + 0..... = .......1
In Ringo's case it's : 2 + (2-1)....... = .......2 + 1....... = ........3
