Saturday, June 11, 2005

Follow up to Footy Tipping Competition Ranking problem.

From Jon Peltier

You could cheat, and add a helper column with a formula like this:

=E2+row()/1000

This way, duplicate integer values are different out in the decimal digits, and you eliminate ties.

From Mark
Rank when not in order.xls
This was a good problem.

There is a much simpler solution, not requiring RANK, when the items have been sorted first : IF(G2=G1,F1,F1+1) - refer to column F in the attached workbook.

The formula when the items are not sorted was a bit harder, and required a bit of lateral thinking. I think you will like this solution - refer to column A.

The solution, not surprisingly, requires a CSE formula. We assume that we are ranking from highest number to lowest. (CSE requires CTRL-SHIFT-ENTER when editing or writing the formula.

=SUM(IF(RANGE>B1,COUNTIF(RANGE,RANGE)/COUNTIF(RANGE,RANGE)^2))+1

What the formula does is for each time any other number is larger than the number in the current row, it counts the number of occurrences, but then (and here's the maths coming in), it divides this number by its square. The formula then sums all these numbers and adds one.

Why it works is that if a larger number is found, say, 3 times, on each occurrences of that specific larger number the formula is adding 3/3^2, that is 1/3 - so the 3 occurrences add to 1. Similarly, if there are 4 occurrences of a specific higher number you are adding four 1/4, again equalling one.

The result is that the formula calculates the number of unique numbers that are higher than the number in the current row. The 1 is added to get the actual number ranking you need.

Thanks guys for your input.


Posted by: Jethro | 10:03 AM |