Rank formula issues

[damians]

Hello All,

in my excel sheet i need to rank different sellers, according to "results points" given so far (points are given in ascending order : higher points = best valuation).
I tried to use the "RANK" formula, but it's giving me some strange results.

At the moment all the sellers have a score between 0 and 3, but this score can increase up to N.

Here the table (starts on line 68)
Formula i used is
=RANK(B68;B$68:B$89;1)

Seller (col A)	points (col B)	rank -ascending (col C)
A	1	7
b	3	1
c	1	7
d	1	7
e	1	7
f	3	1
g	1	7
h	1	7
i	1	7
j	3	1
k	3	1
l	3	1
m	1	7
n	3	1
o	1	7
p	1	7
q	0	22
r	1	7
s	1	7
t	1	7
u	1	7
v	1	7

<tbody>
</tbody>

Anyone can help me?
Thanks

 

[Comfy]

Rank is working as it should.

You have 6 sellers with points of 3 and as 3 is the highest they are all ranked joint first (1)
As 6 sellers have been ranked the next available rank is 7.

You have 15 sellers with points of 1 so they are all ranked joint seventh (the next available rank)

Finally you have 1 seller with zero and is last the next available rank is 6 (joint first) + 15 (joint seventh) + 1 = 22

If you want the rank to be different you will need to provide more info about how rank will decide the difference between sellers with the same score.

 

[damians]

Thanks Comfy !
Unfortunatly i'm not expert enough of RANK formula to understand that it was working correctly ...

About how i'd like to have the ranking done:
at the moment the "points" earned are only "0" "1" "3"
--> i'd like to have that all the ppl who had a 3 are ranked as 1st, then all the ppl with a 2 are ranke as 2nd, and the only one with 0 is ranked as 3rd.

let's say like a soccer league ranking, just to clarify you what i've in my mind

 

[Comfy]

Thanks Comfy !
Unfortunatly i'm not expert enough of RANK formula to understand that it was working correctly ...

About how i'd like to have the ranking done:
at the moment the "points" earned are only "0" "1" "3"
--> i'd like to have that all the ppl who had a 3 are ranked as 1st, then all the ppl with a 2 are ranke as 2nd, and the only one with 0 is ranked as 3rd.

let's say like a soccer league ranking, just to clarify you what i've in my mind

Where I'm from sports ranking works like the RANK function.

So if you had two team in joint first the next available rank would be third.

But this will do as you asked:

http://www.mrexcel.com/forum/excel-...uplicate-ranks-without-skipping-sequence.html

Excel 2010
	A	B	C
1	Seller (col A)	points (col B)	rank -ascending (col C)
2	A	1	2
3	b	3	1
4	c	1	2
5	d	1	2
6	e	1	2
7	f	3	1
8	g	1	2
9	h	1	2
10	i	1	2
11	j	3	1
12	k	3	1
13	l	3	1
14	m	1	2
15	n	3	1
16	o	1	2
17	p	1	2
18	q	0	3
19	r	1	2
20	s	1	2
21	t	1	2
22	u	1	2
23	v	1	2
Sheet1

Cell Formulas
Range	Formula
C2	=SUMPRODUCT((B2 < B$2:B$23)/COUNTIF(B$2:B$23,B$2:B$23))+1