Menu
  1. Accueil
  2. Tech Forum
  3. Office Software

How to calculate positions(1st,2nd,3rd...) in Excel [Solved/Closed]

Report
-
 HASHMAT -
Hello,
hi sir/madam...i am doing some work with the students result in excel...but I faced a problem..it is simple but I havn't got dat in my mind...i have students total marks.now I wanna get the 1st 2nd 3rd...so on position of student leads from highest to lower one...

regards
hardy
Related:

1 reply

Answer 1 / 1
Posts
190
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
 147
Suppose your data is in column b from 2 to 9 then in C2 you can post this formula and drag down .... 


=RANK(B2,$B$2:$B$9)
78
Thank you
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Report

But If there is more than one student in same position

like if three students got 2nd position and two students got 3rd than wots the formula...
Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 767 > MAni
Depends on what you want.

sample data 100,75,50,75,50,46,75,1,45,0
Sample data is in range A1:A10

If desired result should be like 1,2,5,2,5,7,2,9,8,10
So if three students got 2nd postion and two got third, and you want to say that those who are in third postion are actually at 5 position (1 for 1st, 3 for 2nd )
=RANK(A1, A$1:A$10,FALSE)
to reverse order : =RANK(A1, A$1:A$10,TRUE)


If desired result is 1,2,5,3,6,7,4,9,8,10
So it does not skip any number and rank the same number in sequence, So first person at 2nd postion is given rank 2, and 2nd person at postion 2, is given rank 3
=RANK(A1, A$1:A$10,FALSE)+COUNTIF(A$1:A1,A1)-1
to reverse order =RANK(A1, A$1:A$10,TRUE)+COUNTIF(A$1:A1,A1)-1

If desired result is 1,2,3,2,3,4,2,6,5,7
So if values are same, the rank is same and next ranking is to be sequencial, then you have to use an array formula. To enter array formula, type in the formula and press control, shift and enter key at the same time
=SUMPRODUCT(1 * (COUNTIF(OFFSET(A$1,,,ROW(A$1:A$10)-ROW(A$1)+1),A$1:A$10)=1) * (A$1:A$10>=A1))
to reverse order: =SUMPRODUCT(1 * (COUNTIF(OFFSET(A$1,,,ROW(A$1:A$10)-ROW(A$1)+1),A$1:A$10)=1) * (A$1:A$10<=A1))
Report
> MAni
A nice attempt
I am appreciating you on this favour
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!