Assign number based on sort order (Step Ladder points)

[benjya]

Hi everyone,

I'm usually able to work out formulas myself but this has baffled me.

I would like to Award the most points (based on the amount of scores entered into the Match Total Column, in this example, there are 4 scores, so the highest amount of points to be award will be is 4 points) to the highest total in the Match Total Column, the next highest score gets 3 points, the next highest score gets 2 points, and the lowest gets 1 point.

.	A	B
1	Match Total	Step Ladder Points
2	463	2
3	523	3
4	426	1

I'm sure the above is the easy bit. But I'm adding a bit of complication to it. If there two or more rows have the same score, then they get the highest amount of points based on where they are placed. Hopefully the table below can explain it better.

.	A	B
1	Match Total	Step Ladder Points
2	465	3
3	516	4
4	465	3
5	434	1

I would like to award points based on the number of scores that have been entered into the Match Total Column, for example, if there are 6 scores entered, then points are awarded 6,5,4,..,1, if there are 10 scores entered, then points are awarded 10,9,8,..,1 , my basic assumption is that the COUNT formula would be used someone.

Thank heaps for any help in advance.

[MarvinP]

Hi benjya and welcome to the forum,

See if the RANK function doesn't do what you want. See an example at:
http://www.addictivetips.com/microso...rank-function/

[benjya]

Thanks heaps MarvinP

The RANK.EQ function works nearly perfectly, however when there is a tie, it's giving the lowest value of points instead of the highest.
Using the table above for an example, instead of awarding 3 points it is awarding it 2 points, any idea on how to overcome that?

Thanks once again

[MarvinP]

Hi,

I don't have a quick answer for you. I know the rank function skips a number if there are ties. I was thinking you could make the Rank Function work for you.

[benjya]

That's ok, the rank.eq function is working good, I'll award points based on that function, not award the higher amount of points as I wanted.

Thans heaps for your help.