Ranking a list
 

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.

Ranking a list
 

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),--(I2I$2:I$10))+1

Biff

"HK" wrote in message
.. .
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.

Ranking a list
 

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" wrote in message
...
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),--(I2I$2:I$10))+1

Biff

"HK" wrote in message
.. .
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.

Ranking a list
 

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" wrote in message
.. .
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" wrote in message
...
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),--(I2I$2:I$10))+1

Biff

"HK" wrote in message
.. .
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.

Ranking a list
 

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.


"T. Valko" wrote in message
...
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" wrote in message
.. .
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" wrote in message
...
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),--(I2I$2:I$10))+1

Biff

"HK" wrote in message
.. .
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.