Excel: Use rank function - how it works

The rank function in the Excel office program belongs to the group of statistical functions. You can use this function, for example, to quickly find out in a long list of competition run times which time is associated with which rank in the runner field. Read here how to use this function.

Rank function in Excel

• Within a series of numbers, the rank of a number corresponds to its position in the series if the numbers are sorted in ascending or descending order by numerical values. In the number series [4, 5, 3, 1, 2] the number 2 has the rank 2 with increasing sorting [1, 2, 3, 4, 5], with decreasing sorting [5, 4, 3, 2, 1] Rank 4. You can use the Excel function "Rank" to quickly determine the rank of a numerical value, for example a turnover or a competition time, without having to sort the values ​​in advance.
• If you are working with Excel 2010 or a newer version, you can determine the rank of a number in two ways. The function "RANG.GLEICH" corresponds to the function "RANG" of the older versions. The "RANG.MITTELW" function provides a more precise result if several numbers in a row are of equal size.

Use rank function in Excel

• The most convenient way to use complex functions in Excel is through the function wizard of the program. Click on any cell in your Excel worksheet in which the result should be output. Then call the function wizard by clicking on the function notation fx in the formula bar.
• In the "Insert function" window, enter the function name "RANG" in the first input field and click "OK" next to the input field. In newer versions of Excel, the functions "RANG", "RANG.GLEICH" and "RANG.MITTELW" are now displayed in a selection list. Click on "RANK" or "RANG.GLEICH" and confirm your choice with "OK" at the bottom of the window. In the result cell you will see the entry "= RANK.GLEICH ()".
• The "Function arguments" window is now open. Click the cursor in the "Number" input field and then navigate in your worksheet to the number whose rank you want to determine. By clicking on the number, it is transferred to the formula bar as a function argument. The result cell now contains, for example, "= RANK.GLEICH (B5;)".
• In the next step you have to specify the values ​​in relation to which the rank of the adopted number should be determined. To do this, click in the "Reference" input field in the "Functional arguments" window and mark the relevant values ​​on your worksheet. As a rule, this is the entire series of numbers. Click on the first cell of the series of numbers and hold down the left mouse button and drag the cursor to the end of the series. The result cell now contains, for example, "= RANK.GLEICH (A7; A3: A7)".
• You can already see the result in the Function Assistant window. With "OK" the result is output in the result cell. In this form, the result shows the rank of a number on the assumption that the series of numbers is sorted in descending order.
• If you want to determine the rank of a number on the assumption that the number series is sorted in ascending order, you have to enter another argument in the function equation. In the "Order" field in the Function Wizard window, insert any value other than 0. The result cell now contains, for example, "= RANK.GLEICH (A7; A3: A7; 1)" With a click on "ok" the result is output.
• If certain values ​​occur several times in a series of numbers, the "RANG" or "RANG.GLEICH" function is used to output the lowest rank. For the series [4, 5, 3, 1, 2, 2, 2, ], rank 4 (descending sorting) or rank 2 (ascending sorting) for the value 2 is determined unchanged. If you want a more precise result here, you should use the "RANG.MITTELW" function.