How to calculate positions (1st,2nd,3rd...) in Excel
Solved
hardy

Updated on Dec 19, 2022 at 11:30 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023  Dec 12, 2022 at 11:09 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023  Dec 12, 2022 at 11:09 AM
Related:
 1st, 2nd, 3rd position formula in excel
 How to find position in excel  Best answers
 How to calculate position in excel  Best answers
 How to calculate position (1st,2nd,3rd) in excel....?  Excel Forum
 Formulas for 1st and 2nd position in class  Office Software Forum
 Calculation of positions in excel ✓  Excel Forum
 Excel date format dd.mm.yyyy  Guide
 Number to words in excel formula  Guide
2 replies
mubashir aziz
Posts
190
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
161
Updated on Dec 4, 2018 at 01:43 AM
Updated on Dec 4, 2018 at 01:43 AM
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)
Mar 31, 2011 at 04:35 AM
like if three students got 2nd position and two students got 3rd than wots the formula...
Mar 31, 2011 at 09:54 AM
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))
Apr 11, 2013 at 08:39 AM
I am appreciating you on this favour