Ranking Values In Excel 2010 With RANK function

Finding out the rank of the data value from the list is very easy through Excel 2010 inherent RANK function. This function takes three simple arguments and will let you to rank the data in a specified sorting order, i.e, Ascending or Descending.

Launch Excel 2010 spreadsheet in which you want to apply RANK function. For instance we have included spreadsheet containing student record having fields; Name, Course, Marks, Grade, and Status, as shown in the screenshot below.

table

Now we need to to find out the ranking of specified student, for this we will be using RANK function. The rankings would be based upon the marks obtained by the students.

The syntax of the RANK function is;

=RANK(number,ref,[order])

The first argument is number which refers to any entered number or location of cell for which we need to find out the rank, the second argument refers to record list from which rankings will be evaluated, and third argument refers to sorting the ranking in either ascending or descending order.


Create new column by the label of Rankings adjacent to Status field and write down the function in the first row of Rankings field as;

=RANK(D2,$D$2:$D$11,0)

D2 as the first argument refers to the first value in the Marks column, D2:D11 is the reference of all marks contained in Marks field, you need to write it with absolute reference i.e with dollar sign ($D$2:$D$11). 0 indicates that we need to sort the rank data in descending data, for ascending order enter 1 instead of 0.

It will yield 7 after checking position of corresponding marks obtained (60) in Marks field.

ranking

Now just drag down the plus sign at the end of the cell towards the end of column to apply it over the field. It will evaluate and show the standings of every student (see in the screenshot below).

rankings

You can also check out previously reviewed Excel functions; TIME, ADDRESS ,FACT (factorial), MAX,MIN, MAXA, MINA and EXACT, Logical Functions, INFO, SUMSQ, DOLLAR, SUMPRODUCT, SUMIF, COUNTIF, VLOOKUP, HLOOKUP ,PMT, & LEN.

Advertisement