# Thread: Formula to Rank data

1. ## Formula to Rank data

I have a spreadsheet where I tried to use formulas to rank the students and marks -see section of my spreadsheet A25 to G 29.

However, where the marks are the same, the formula returns the student name and student number twice

It would be appreciated if someone could assist me

Howard

2. In addition to your question, you need to use MATCH with the ",0" option no the ",1" to get an exact match, you approximate match with unsorted data is not accurate, the wrong people are being pulled from the list.

You need a column to rank with no ties. If you use the row as a tie-breaker, In O5 enter: Rank
In O6:
=RANK(G7,\$G\$7:\$G\$17)+COUNTIF(\$G\$6:\$G6,G7)
Copy O6 down the columns.

In O25:O29 enter 1 through 5

In C25:
=INDEX(\$A\$7:\$A\$17,MATCH(D25,\$D\$7:\$D\$17,0))

In D25:
=INDEX(\$D\$7:\$D\$17,MATCH(O25,\$O\$7:\$O\$17,0))

In G25:
=LARGE(\$G\$7:\$G\$19,O25)

Copy c25:G25 down the columns.

Steve

3. Hi Steve

Thanks for the help, much appreciated

Howard

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•