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.
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.
The learning module – Relative and Absolute References – discusses absolute references and how to create them.
The learning module – AutoFill – shows you how to copy the contents of one cell to others using just your mouse.