**YOUR OBJECTIVE**

You have an *Excel* table that lists the sales volume for groups of products sold by your company within a certain period of time. Now you want to develop a numerical rank for the products sold, the product with the greatest sales receiving a rank of 1. ** Excel’s RANK** .EQ function can quickly and easily calculate this ranking.

This learning module will show you how to use *Excel’s* RANK.EQ function.

**Excel RANK function: HOW DOES IT WORK?**

The excel RANK .EQ determines the ranking of a number within a group of numbers, the numbers being in a particular cell range. In our current example, the dollar amount of sales for products are listed next to a column containing the names of the products sold. You want to list the rankings in a column to the right of the sales numbers.

The excel RANK .EQ function is used in a fashion similar to other functions in *Excel*. In our current example, you will be using the RANK.EQ function to display the rankings in column C. Begin by clicking in cell C4. Then start the *Function Wizard* by clicking the button just to the left of the *formula bar*. In the *Insert Function* dialog box that opens, you will find the excelRANK .EQ function in the *Statistical* category. Select that function and click ** OK**.

**CAUTION**

You may know the RANK function from earlier versions of *Excel*. This function corresponds to RANK.EQ in *Excel 2010*. The excel RANK function is still supported by *Excel*.

The excel RANK .EQ function requires two arguments; the third argument, *Order*, is optional. For clarity’s sake, you should make it a habit to specify all three arguments.

The *Number* argument specifies the cell that contains the value whose rank you want to determine. Click in cell B4 to to enter a cell reference to that cell.

*Ref* is the group of numbers to which the *Number* entry belongs. In the example, use your mouse to select the range of cells B4:B12. The reference to that range of cells appears in the *Ref* input box. Because the cell range for the *Ref* argument applies exactly to all other values, you should change it to an *absolute reference*. (In the example, make sure that the cell range is entered as: $B$4:$B$12 .) Only then can you properly copy your function to other cells in column C.

*Order* specifies how the ranking is calculated: By entering 0 (zero) or by leaving the *Order* field empty, you specify that the highest value is to be given the rank of 1. Any other nonzero entry will result in the lowest value receiving the number 1 rank.

Click the ** OK** button to finish your function.

If you click on cell C4, you will see the complete function in the *formula bar*:

This can be read as follows: Determine the rank of the value in cell B4 within all values in cell range B4:B12. Give the highest value in the range a rank of 1.

To determine the ranking of the other product groups, use the *AutoFill* function to copy the formula to the other cells in column C. Remember, for copying to work properly, you must have initially entered an *absolute reference* to the range of cells B4:B12. That is, in the formula that you entered first, you should have modified the *Ref* argument to $B$4:$B$12. Doing this prevents *AutoFill* from shifting the cell references as you copy the formula.

**HINT**

The learning module – *Relative and Absolute References* – discusses *absolute references* and how to create them.

**HINT**

The learning module – *AutoFill* – shows you how to copy the contents of one cell to others using just your mouse.