Ranking a list

[HK]

I'm trying to build a spreadsheet to manage results from a race. The racers
can be in one of 7 different categories.

In column A, I want to rank the racer's finish, 1-x, x being the number of
racers for that category. In column H, I have the category for each racer.
In column I, I have the race times which I will use to sort my list.

I'm having trouble with the formula in column A, the rank. I have no way of
knowing how many racers are in each category so I can't just pre-enter a
series of numbers. I have to enter a series, starting from 1 and then reset
that series to 1 each time the value in column H changes.

Can anyone help with a worksheet function or formula?

Thanks.

 

[T. Valko]

So, you want to rank by time for each category?

Sounds like you should sort your data first by category then by time.

Then, a formula like this will do what you want: (assuming the lowest time
is best: rank = 1)

=SUMPRODUCT(--(H$2:H$10=H2),--(I2>I$2:I$10))+1

Biff

 

[HK]

I figured it out. So simple:

=IF(H24=H23,B23+1,1)

Column B is the Ranking Column. If it's the first occurrence of that
category, then it's 1, if not, then add 1 to B. And yes, I'm starting with
a sorted list.

Thanks.

 

[T. Valko]

I figured it out. So simple:

=IF(H24=H23,B23+1,1)

While that may seem to work, you're not actually ranking the times. You're
ranking by virtue of sorting and just counting the instances of a catergory.
That will probably be good enough but if 2 times in the same category are
the same, if that's even possible, then a true "rank" will not be achieved.

Biff

 

[HK]

You're right. If get two identical times, it will not show a tie. I'll try
your formula again but I had trouble with it and went with my simple
version.