Using RANK.EQ, PERCENTILE, and QUARTILE Functions in Excel

Course: Advanced Excel Training

Topic: Using RANK.EQ, PERCENTILE, and QUARTILE Functions

Course Link: Advanced Excel Course – JustAcademy

Demo Link: Register for a Course Demo

JustAcademy Homepage: Visit JustAcademy

1. Introduction

Excel provides several statistical functions for analyzing numerical data, comparing performance, and understanding how values are distributed. Three useful functions are RANK.EQ, PERCENTILE.INC, and QUARTILE.INC.

RANK.EQ identifies the position of a value relative to other values in a dataset. Percentile functions identify the value below which a specified proportion of observations falls. Quartile functions divide a dataset into four parts to help analyze its distribution.

These functions are useful in sales performance reports, examination results, salary analysis, employee rankings, financial reporting, customer analytics, and quality control.

2. Learning Objectives

• Understand the purpose and syntax of RANK.EQ, PERCENTILE.INC, and QUARTILE.INC.
• Rank values in ascending or descending order.
• Understand ties and equal ranks in datasets.
• Calculate percentiles and quartiles.
• Use statistical functions to analyze employee, sales, and student performance.
• Interpret results and troubleshoot common formula errors.

3. Sample Dataset for Practice

Use the following sample sales data to understand the formulas. Enter the values in cells B2:B11.

This dataset contains ten sales values, including two employees with equal sales. It can be used to demonstrate ranking, percentiles, and quartiles.

4. Understanding the RANK.EQ Function

The RANK.EQ function returns the rank of a number within a list of numbers. When two or more values are equal, they receive the same rank.

Syntax

=RANK.EQ(number, ref, [order])

4.1 Ranking from Highest to Lowest

To rank the sales in cell B2 against all sales in B2:B11, use:

=RANK.EQ(B2,$B$2:$B$11,0)

The highest sales value receives rank 1. Copy the formula down to rank every employee. The dollar signs keep the comparison range fixed as the formula is copied.

4.2 Ranking from Lowest to Highest

To give the lowest sales value rank 1, use:

=RANK.EQ(B2,$B$2:$B$11,1)

This ordering can be useful for ranking expenses from lowest to highest, identifying the smallest scores, or prioritizing the lowest measurements.

4.3 Understanding Tied Ranks

In the sample data, Rahul and Neha both have sales of ₹18,000. With descending ranking, both receive rank 5. The next value receives rank 7, because RANK.EQ skips rank 6 after the tie.

This behavior is important when preparing leaderboards or performance reports. If you require a unique sequential position for every record, you need an additional tie-breaking rule.

4.4 Sample Ranking Results

Notice that equal values share the same rank and that ranks are skipped after a tie. In this example, the ranking is calculated against all ten values.

5. Understanding the PERCENTILE Function

A percentile indicates the value below which a specified percentage of observations falls. For example, the 90th percentile is a threshold at or below which approximately 90% of observations lie under the percentile method used.

Excel's modern function is PERCENTILE.INC. The older PERCENTILE function remains available for compatibility and corresponds to the inclusive method.

Syntax

=PERCENTILE.INC(array, k)

5.1 Common Percentile Values

5.2 Calculating the 25th Percentile

Using the sales data in cells B2:B11, enter:

=PERCENTILE.INC(B2:B11,0.25)

The result is ₹14,250. This is the first quartile for the sample data using Excel's inclusive percentile method.

5.3 Calculating the 50th Percentile

=PERCENTILE.INC(B2:B11,0.5)

The result is ₹17,000, the median of the ten sales values. It is the midpoint between the fifth and sixth values after sorting the data from lowest to highest.

5.4 Calculating the 90th Percentile

=PERCENTILE.INC(B2:B11,0.9)

The result is ₹27,500. This provides an upper-end sales threshold for the sample dataset. It does not mean that every value above this threshold is automatically a top performer in a business context; the interpretation depends on the data and business rules.

5.5 Using a Cell Reference for the Percentile

If cell D2 contains 0.75, use:

=PERCENTILE.INC($B$2:$B$11,D2)

You can change D2 to 0.25, 0.50, or 0.90 to calculate different percentile thresholds without rewriting the formula.

6. Understanding the QUARTILE Function

Quartiles divide a sorted dataset into four parts. They help analysts understand the spread and distribution of values, identify the middle of a dataset, and compare lower and upper ranges.

Excel's modern function is QUARTILE.INC. The older QUARTILE function is retained for compatibility and corresponds to the inclusive method.

Syntax

=QUARTILE.INC(array, quart)

6.1 Quartile Argument Reference

6.2 Calculating the First Quartile (Q1)

=QUARTILE.INC(B2:B11,1)

Result: ₹14,250.

Q1 represents the 25th percentile. It helps identify the lower quarter of the distribution.

6.3 Calculating the Second Quartile (Q2)

=QUARTILE.INC(B2:B11,2)

Result: ₹17,000.

Q2 is the median, which divides the sorted observations into lower and upper halves.

6.4 Calculating the Third Quartile (Q3)

=QUARTILE.INC(B2:B11,3)

Result: ₹21,000.

Q3 represents the 75th percentile. Approximately three-quarters of the observations fall at or below this threshold, subject to the percentile method and ties.

6.5 Finding the Minimum and Maximum

=QUARTILE.INC(B2:B11,0)

Result: ₹10,000.

=QUARTILE.INC(B2:B11,4)

Result: ₹30,000.

You can also use MIN and MAX for these calculations.

7. Corrected Ranking Results for the Sample Dataset

When ranking sales from highest to lowest with RANK.EQ, equal values receive the same rank and subsequent ranks are skipped. For the sample dataset, the correct results are:

For example, if the sales amount is in B2:B11, enter this formula in C2 and copy it down:

=RANK.EQ(B2,$B$2:$B$11,0)

8. Difference Between Percentiles and Quartiles

Quartiles are special percentile points: Q1 is the 25th percentile, Q2 is the 50th percentile, and Q3 is the 75th percentile under the inclusive method.

9. Understanding Inclusive and Exclusive Methods

Excel offers both inclusive and exclusive percentile and quartile methods. The choice can affect the result, especially with small datasets.

The inclusive method allows k values from 0 through 1 and includes the minimum and maximum endpoints. The exclusive method uses a different calculation convention and does not accept the endpoints as percentile positions. Choose the method required by your organization's reporting standard, textbook, or analytical specification, and use it consistently.

10. Combining Ranking and Quartile Analysis

RANK.EQ and quartile functions can work together in a sales performance dashboard. RANK.EQ identifies the relative order of each employee, while quartiles summarize the distribution and help establish performance bands.

For example, calculate the third quartile with:

=QUARTILE.INC($B$2:$B$11,3)

Then classify a sales result in B2 as above or below Q3:

=IF(B2>=QUARTILE.INC($B$2:$B$11,3),"At or Above Q3","Below Q3")

This formula labels values at or above the third quartile threshold. It is a simple classification rule, not a guarantee of employee performance quality; interpret the threshold in the context of the business.

11. Practical Business Applications

11.1 Employee Performance Ranking

Use RANK.EQ to rank employees by sales, completed projects, productivity scores, or customer satisfaction results. Ensure that higher values genuinely indicate better performance for descending ranking.

11.2 Student Examination Results

Use RANK.EQ to rank student marks and PERCENTILE.INC to determine score thresholds. Quartiles can help educators understand score distribution across a class.

11.3 Salary and Compensation Analysis

Use quartiles to summarize salary distributions. Q1, the median, and Q3 can help compare salary ranges across departments or job levels. Avoid treating quartiles alone as proof of pay equity or fairness.

11.4 Sales Target Analysis

Use percentile thresholds to identify sales values in the upper part of a distribution. RANK.EQ can list employee positions, while quartiles provide a broader view of how sales are spread across the team.

11.5 Quality Control

Quartiles and percentiles can summarize measurements and help flag values for further review. They should be combined with the relevant process specifications rather than used as a substitute for formal quality limits.

12. Common Errors and Troubleshooting

13. Best Practices

• Use RANK.EQ when tied values should receive the same rank.
• Use RANK.AVG when tied values should receive their average rank.
• Use absolute references for ranges that should remain fixed when formulas are copied.
• Keep numeric datasets clean and check for values accidentally stored as text.
• Choose inclusive or exclusive percentile methods according to the reporting requirement.
• Format sales, salaries, and financial figures with appropriate number or currency formats.
• Explain what each threshold means when presenting results to business users.
• Validate formulas against a sorted sample before applying them to large datasets.

14. Practical Exercises

Using the sample dataset in B2:B11, complete the following exercises:

1. Rank each employee from highest to lowest sales.
2. Rank each employee from lowest to highest sales.
3. Calculate the 25th, 50th, and 75th percentiles.
4. Calculate the 90th percentile for sales.
5. Find the minimum, median, and maximum using QUARTILE.INC.
6. Create a label for sales that are at or above Q3.
7. Compare the results of PERCENTILE.INC and PERCENTILE.EXC using the same dataset.

Suggested Formulas

Descending rank:

=RANK.EQ(B2,$B$2:$B$11,0)

Ascending rank:

=RANK.EQ(B2,$B$2:$B$11,1)

25th percentile:

=PERCENTILE.INC(B2:B11,0.25)

90th percentile:

=PERCENTILE.INC(B2:B11,0.9)

First quartile:

=QUARTILE.INC(B2:B11,1)

Median:

=QUARTILE.INC(B2:B11,2)

Third quartile:

=QUARTILE.INC(B2:B11,3)

15. Interview Questions and Answers

Q1. What does RANK.EQ do in Excel?

Answer: RANK.EQ returns the rank of a number relative to a range of numeric values. Equal values receive the same rank, and subsequent ranks are skipped.

Q2. What is the difference between ascending and descending rank?

Answer: Descending ranking assigns rank 1 to the highest value. Ascending ranking assigns rank 1 to the lowest value.

Q3. What is a percentile?

Answer: A percentile is a value associated with a specified position in a distribution. For example, the 75th percentile is a threshold at or below which approximately 75% of observations fall under the chosen method.

Q4. What does PERCENTILE.INC return?

Answer: It returns the value at a specified inclusive percentile position in a numeric dataset, where k ranges from 0 to 1.

Q5. What are quartiles?

Answer: Quartiles are values that divide a dataset into four parts. Q1 is the 25th percentile, Q2 is the median, and Q3 is the 75th percentile.

Q6. What does QUARTILE.INC(array,0) return?

Answer: It returns the minimum value in the dataset.

Q7. What does QUARTILE.INC(array,4) return?

Answer: It returns the maximum value in the dataset.

Q8. What is the difference between PERCENTILE.INC and PERCENTILE.EXC?

Answer: They use different conventions for calculating percentile positions. The inclusive version allows endpoints from 0 to 1; the exclusive version uses an exclusive position convention.

Q9. When would you use RANK.EQ in a business report?

Answer: It can rank employees by sales, products by revenue, branches by performance, or students by marks, provided the selected metric is appropriate for ranking.

Q10. Why can two employees have the same RANK.EQ result?

Answer: They have the same value in the metric being ranked. RANK.EQ assigns equal values the same rank.

16. Quick Revision Checklist

• RANK.EQ ranks a number relative to other values.
• Use order 0 for descending ranking and 1 for ascending ranking.
• Equal values receive equal ranks, with later ranks skipped.
• PERCENTILE.INC calculates a specified inclusive percentile.
• QUARTILE.INC uses quart arguments from 0 through 4.
• Q1 equals the 25th percentile, Q2 equals the median, and Q3 equals the 75th percentile under the inclusive method.
• Use fixed ranges when copying formulas down a worksheet.
• Validate data types, arguments, and the chosen statistical method.

17. Conclusion

RANK.EQ, PERCENTILE.INC, and QUARTILE.INC are useful Excel functions for analyzing relative performance and understanding numerical distributions. RANK.EQ helps compare individual values, percentiles establish thresholds at specific distribution positions, and quartiles summarize the lower, middle, and upper portions of a dataset.

With these functions, Excel users can build clearer sales reports, performance dashboards, salary analyses, and statistical summaries. Always verify the dataset and select the appropriate ranking or percentile method for the task.

18. Additional Learning Resources

• Microsoft Support: RANK.EQ Function
• Microsoft Support: PERCENTILE.INC Function
• Microsoft Support: QUARTILE.INC Function
• Explore the Advanced Excel Course at JustAcademy
• Register for a Course Demo
• Visit the JustAcademy Homepage