🔢Ranking Data with Excel

Excel RANK Function Study Guide

What is the main purpose of the RANK function in Excel?

The main purpose of the RANK function in Excel is to determine the position of a number within a list of numbers, based on whether you are ranking from highest to lowest or lowest to highest. It allows you to quickly analyze and identify top performers or positions within a dataset, as demonstrated by ranking employee sales figures.

How do you use the RANK function in Excel?

To use the RANK function, you typically enter =RANK.EQ or =RANK.AVERAGE followed by parentheses. Inside the parentheses, you first provide the number you want to rank, then a comma, and finally the range of numbers against which you are ranking the first number. For example, to rank Bonnie's sales (in cell C3) within the range of all sales data (named "sales"), the formula would be =RANK.EQ(C3, sales).

What is the difference between RANK.EQ and RANK.AVERAGE?

The key difference lies in how ties are handled. RANK.EQ assigns the same rank to tied values and the next value receives the next available rank (e.g., if two values are tied for 3rd, the next value will be 5th). RANK.AVERAGE also assigns the same rank to tied values, but it calculates the average of the ranks that the tied values would have received and assigns that average rank to them (e.g., if two values are tied for 3rd, and the next rank would be 4th, they both receive a rank of 3.5).

Why might someone choose to use RANK.EQ over RANK.AVERAGE, or vice versa?

The choice depends on how you want to represent ties. If you want to clearly show that tied values share the same position and the following values are effectively "skipped" in the ranking order, RANK.EQ is appropriate. If you prefer a ranking that reflects the average position of tied values within the list, RANK.AVERAGE is a better choice.

Is the original RANK function still available in newer versions of Excel?

Yes, the original RANK function is still available in newer versions of Excel. However, as the source mentions, it is primarily included for compatibility with Excel 2007 and earlier versions. It is generally recommended to use RANK.EQ or RANK.AVERAGE in current versions for their updated functionality and handling of ties.

Can the RANK function rank in descending or ascending order?

Yes, the RANK function can rank in either descending (highest to lowest) or ascending (lowest to highest) order. This is controlled by an optional third argument in the function. If this argument is omitted or set to 0, it ranks in descending order. If it is set to any non-zero value (typically 1), it ranks in ascending order.

What happens if the number you are trying to rank is not found in the reference range?

If the number you are trying to rank is not found within the specified reference range, the RANK function will return an error, typically #N/A. This indicates that the value you are trying to rank is not part of the dataset you are comparing it against.

Can the RANK function be used with non-numeric data?

No, the RANK function is designed to work specifically with numeric data. Attempting to use it with non-numeric values in the number argument or the reference range will result in an error. The function requires numbers to perform its ranking calculations.

Quiz

1. What is the primary purpose of the RANK function in Excel?
   

2. Which versions of the RANK function were discussed in the lecture?
   

3. How do you begin using the RANK function in an Excel cell?
   

4. What information does the RANK function require as its first argument?
   

5. What information does the RANK function require as its second argument?
   

6. How does the RANK.EQ function handle ties in rankings?
   

7. How does the RANK.AVERAGE function handle ties in rankings?
   

8. Which RANK function is used for compatibility with Excel 2007 or earlier?
   

9. How did the presenter copy the RANK function down to other cells in the example?
   

10. In the example, how many employees were ranked?

Quiz Answer Key

1. The primary purpose of the RANK function is to determine the position of a number within a list of numbers.
   

2. The versions discussed were RANK.EQ, RANK.AVERAGE, and the original RANK function for compatibility.
   

3. You begin using the RANK function by typing "equals rank" in the desired cell.
   

4. The first argument required is the specific number you want to rank.
   

5. The second argument required is the range of numbers against which you want to rank the first number.
   

6. RANK.EQ assigns the same rank number to all tied values and then skips the subsequent ranks.
   

7. RANK.AVERAGE assigns the average of the tied ranks to each tied value.
   

8. The original RANK function is used for compatibility with Excel 2007 or earlier.
   

9. The presenter copied the RANK function down by dragging the fill handle.
   

10. There were eight employees ranked in the example.

Glossary of Key Terms

RANK Function: An Excel function used to determine the position of a number within a list of numbers.

RANK.EQ: A version of the RANK function that assigns the same rank to tied values and skips subsequent ranks.

RANK.AVERAGE: A version of the RANK function that assigns the average of the tied ranks to tied values.

Sales: In the lecture example, this refers to the numerical data representing the sales figures for each employee.

Rank: The position or order of a number within a list based on its value.

Compatibility: The ability of a function or feature to work with older versions of software, such as Excel 2007.

Arguments: The input values or ranges required by a function to perform its calculation.

Range: A selection of contiguous cells in an Excel spreadsheet.

Tie: When two or more values in a list are equal, resulting in shared rankings.