# need help in finding positions

#### abdul43

##### Board Regular
Hi,
I am using the following formula for finding position by the obtained marks of a student:

=RANK(X8,\$X\$8:\$X\$77)&LOOKUP(MOD(RANK(X8,\$X\$8:\$X\$77),50),{1,2,3,4,5,6,7,8,9,10,21,22,23,24,31,32,33,34,41,42,43,44,51,52,53,54;"st","nd","rd","th","th","th","th","th","th","th","st","nd","rd","th","st","nd","rd","th","st","nd","rd","th","st","nd","rd","th"})

in this formula:
X8= the obtained marks of the student
X8 to X77 = is a column where obtained marks of all students are present.

now a single problem is, that this formula skips a positions after two same positions
for example if obtained marks are 350 of two students, this formula gives them first position and skip 2nd position and gives 3rd to the next position holder, and i need to give him 2nd position.

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### pgc01

##### MrExcel MVP
Hi

You just have to add the number of students with the same mark up to the current one.

You forgot to post where this formula is. Assuming the formula for the first student is in Y8, try for ex.:

=(RANK(X8,\$X\$8:\$X\$77)+COUNTIF(\$X\$8:X8,X8)-1)&MID("ththth"&REPT("thththththththstndrd",10),MOD(RANK(X8,\$X\$8:\$X\$77)+COUNTIF(\$X\$8:X8,X8)-12,100)*2+1,2)

Copy down.

Remark:
Kind of off-topic: it would seem to me that 2 students with the same mark should have the same rank but, of course, I don't know the context.

#### abdul43

##### Board Regular
This formula worked well but a single problem which i couldn't find is that it was giving the positions as
Student 1: obtained marks 340 positions is 3rd
Student 2: obtained marks 340 position is 4th
can it show these position as same 3rd?

and 4th for next maximum marks.?

#### pgc01

##### MrExcel MVP
Hi

If I understood correctly now, this will do what you want.

In Y8:

=(1+SUM(IF(\$X\$8:\$X\$77>X8,0+(MATCH(\$X\$8:\$X\$77,\$X\$8:\$X\$77,0)=ROW(\$X\$8:\$X\$77)-MIN(ROW(\$X\$8:\$X\$77))+1))))&MID("ththth"&REPT("thththththththstndrd",10),MOD((SUM(IF(\$X\$8:\$X\$77>X8,0+(MATCH(\$X\$8:\$X\$77,\$X\$8:\$X\$77,0)=ROW(\$X\$8:\$X\$77)-MIN(ROW(\$X\$8:\$X\$77))+1))))-10,100)*2+1,2)

This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER and not just ENTER.

Remark: In the example I post there are 10 marks better than 91, but since the 10 bigger marks are all 100 and 95, the mark 91 gets the rank 3.
Does this make sense to you?

#### abdul43

##### Board Regular
Salute with respect.
this was genuine solution to my problem.
Thank u Very much.

#### pgc01

##### MrExcel MVP
I'm glad it helped. Thanks for the feedback.

Replies
0
Views
388
Replies
27
Views
988
Replies
3
Views
921
Replies
2
Views
301
Replies
32
Views
3K

1,191,187
Messages
5,985,192
Members
439,947
Latest member
fabiannic

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back