Using “Rank” Function pto Determine Position in MS Excel

The Rank function determines the relative position or order of one value in a range of data compared to the other values in the same range of data.

This means it is not necessary to sort data in either ascending or descending order to see the relative rankings of values in a data range.  For example: 1^st, 5^th, 3^rd, 6^th, 2^nd, 4^th, etc.

The RANK function has two (2) sections with a comma (,) between each section:

=RANK(number,ref)

In our tutorial, we will create a Quiz Scoreboard template using the Rank function to determine the contestant’s positions.

There are ten (10) schools with each answering four (4) rounds of questions in the quiz competition.

We assume you have a basic understanding of MS Excel cell references, autofill, finding the total, MAX, MIN, AVERAGE etc. and other basic operations.

Step 1             Entering data into the spreadsheet       

·         Enter the following fictitious data shown below exactly as they appeared into the spread sheet:

·         Save the worksheet as Quiz Scoreboard in a private folder or on a flash drive

Step 2             Finding the Total score      

We will configure the Total place holder for each school

·         Click in cell G2 and type:     =SUM(C2:F2)

·         Press the [Enter] key to complete the formula

The formula result is:          0

Since no data has been entered for any of the rounds, the Total score records zero (0)

Instead of calculating the Total for each school, we will let Excel do its magic by using the AutoFill command to copy the formula to other cells

Note:

AutoFill is a command used to extend series of formulas, numbers and/or texts to other cells.  The AutoFill handle is a tiny black-plus (+) sign which is displayed when the cursor is moved to the bottom-right corner of the active cell

To AutoFill, execute the following steps:

·         Click in cell G2 and locate the AutoFill handle at the bottom-right corner of the cell as shown below:

·         Double-click on the AutoFill handle to copy the formula

Result:

Step 2             Finding the Position           

We will configure the Position place holder for each school

In this case, we will use the “Rank” function to configure the Position for each school based on their Total scores

·         Click in cell H2

·            On the Formulas tab, click on More Function, point to Compatibility, scroll down and select RANK

Note: This will display the Rank dialog box.  You can drag the dialog box to any place on your screen to enable you view the table

·         Enter the following information into their respective text boxes:

          ü   In the ‘Number’ text box, type:          G2

ü   In the ‘Ref’ text box, type:                G2:G11

·         Press [F4] function key to insert the dollar ($) sign (i.e. $G$4:$G$11)

Note:  The $ symbol in $G$4:$G$11 means Absolute Cell Reference.  Meaning Excel will treat the cell range G2:G11 as a unique range. (i.e. from OLA SHS to TANYIGBE SHS)

The Rank dialog box should look like this:

·         Click on [OK] button to complete the formula

The syntax for the formula in the Formula bar reads:     =Rank(G2,$G$2:$G$11)

The formula result is:          1

·         AutoFill cell H2 through cell H11

Since zero (0) is conceded as a number, excel assumed it to be a record for Total scores and thus displays one (1) for the Positions

We will now test our formula created for Total and Position respectively by entering marks for Round 1.

·         Enter the following fictitious data for Round 1 into the table:

Result:

From the quiz scoreboard, St. Paul’s SHS is in a comfortable lead, followed by Mawuli SHS.

We will now complete our Quiz scoreboard by entering the following fictitious data for Round 2, Round 3 and Round 4 respectively

Final Result:

In the final round, St. Mary’s Sem. SHS scored 32 points and thus came 1^st position, Bishop Herman College scored 27 points and came second and the list goes on

Source: BB Series