simple rank formula

Question

I'm looking for a mathmatical ranking formula.

Sample is

    2008    2009    2010
A   5       6       4
B   6       7       5
C   7       8       2

I want to add a rank column for each period code field

                            rank
    2008    2009    2010    2008    2009    2010
B   6       7       5       2       1       1
A   5       6       4       3       2       2
C   7       2       2       1       3       3

please do not reply with methods that loop thru the rows and columns, incrementing the rank value as it goes, that's easy. I'm looking for a formula much like finding the percent total (item / total). I know i've seen this before but an havning a tough time locating it.

Thanks in advance!

Answer 1

If you want the rank of a single element, you can do it in O(n) by looping through the elements, counting how many have value above the given element, and adding 1.

If you want the rank of all the elements, the best (and really only) way is to sort the elements. Anything else you do will be equivalent to sorting (there is no "formula")

Answer 2

What are you using? If you're using Excel, you're looking for RANK(num, ref).

=RANK(B2,B$2:B$9)

I don't know of any programming language that has that built in, it would always require a loop of some form.

Answer 3

Are you using T-SQL? T-SQL RANK() may pull what you want.