Excel rank function data does not sort correctly
I have used a formula to rank data. When I try to sort from low to high based on rank, it does not sort correctly. Any solutions?
=RANK(I53,$I$53:$I$69)
Data type is set to Number
Total Rank
75.00 1
66.25 3
43.75 14
53.75 12
65.00 5
57.50 8
57.50 8
30.00 17
60.00 7
65.00 5
32.50 16
37.50 15
47.50 13
56.25 10
56.25 10
75.00 1
66.25 3
=RANK(I53,$I$53:$I$69)
Data type is set to Number
Total Rank
75.00 1
66.25 3
43.75 14
53.75 12
65.00 5
57.50 8
57.50 8
30.00 17
60.00 7
65.00 5
32.50 16
37.50 15
47.50 13
56.25 10
56.25 10
75.00 1
66.25 3
if you mean to generate unique rank list, you could try formula:
How to rank range numbers uniquely without duplicates in Excel?
https://www.extendoffice.com/documents/excel/2512-excel-unique-rank.html
29157648.xlsx
=RANK(I53,$I$53:$I$69,0)+COUNTIF($I$53:I53,I53)-1How to rank range numbers uniquely without duplicates in Excel?
https://www.extendoffice.com/documents/excel/2512-excel-unique-rank.html
29157648.xlsx
ASKER
Here is a version of the file
Capital-project-EE-Sample-10SEP19--.xlsx
Capital-project-EE-Sample-10SEP19--.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
»bp