QUARTILE

Returns a specified quartile in a list of numbers.

Syntax:

QUARTILE(numberlist, q)

QUARTILE returns the value below which q quarters of the numbers in numberlist lie. q may be 0 (to return the minimum value), 1 (to return the value below which a quarter of values lie), 2 (to return the median value), 3 (to return the value below which three quarters of values lie) or 4 (to return the maximum value). numberlist is a range or array of numbers, not necessarily in order.

QUARTILE calculates a return value in the same way as PERCENTILE

Example:

QUARTILE(A2:A99, 3)

where cells A2:A99 contain scores from a test, returns the score below which 75% of the scores lie.

The next two examples illustrate the calculation, but are not intended to show practical application:

QUARTILE({3, 4, 9, 11, 12}, 2)

returns 9, the median value of the list.

QUARTILE({9, 3, 7, 8}, 1)

returns 6. The calculation is the same as for PERCENTILE({9, 3, 7, 8}, 0.25).

Application:

Quartile Analysis of Employee Salaries

A company wants to analyze the distribution of salaries for its marketing department. The human resources manager has collected the following salary data for 15 employees. To understand the spread of the data, the manager decides to use the QUARTILE function.

Here's the raw data in a table:

	Employee ID	Salary
	A	B
1	101	$55,000.00
2	102	$62,000.00
3	103	$58,000.00
4	104	$75,000.00
5	105	$68,000.00
6	106	$95,000.00
7	107	$72,000.00
8	108	$65,000.00
9	109	$80,000.00
10	110	$60,000.00
11	111	$78,000.00
12	112	$85,000.00
13	113	$70,000.00
14	114	$92,000.00
15	115	$88,000.00

Step-by-Step Calculation using the QUARTILE Function

We can apply the QUARTILE function to find the key values:

Minimum Value (0th Quartile): This is the lowest salary. QUARTILE(data, 0) = $55,000
First Quartile (Q1): This represents the 25th percentile. It is the salary below which 25% of the employees fall. QUARTILE(data, 1) = $63,500
Median (Second Quartile, Q2): This is the 50th percentile, or the middle value of the dataset. Half the employees earn less than this amount, and half earn more. QUARTILE(data, 2) = $72,000
Third Quartile (Q3): This represents the 75th percentile. It is the salary below which 75% of the employees fall. QUARTILE(data, 3) = $82,500
Maximum Value (4th Quartile): This is the highest salary. QUARTILE(data, 4) = $95,000

Conclusion

By using the QUARTILE function, the HR manager can draw the following conclusions about the salary distribution:

The lowest salary is $55,000 and the highest is $95,000.
25% of the employees earn $63,500 or less. This might be the salary range for entry-level or junior positions.
The median salary is $72,000, which gives a clear picture of the typical or central salary in the department, unaffected by extreme values.
75% of the employees earn $82,500 or less. This helps in understanding the upper end of the salary scale for most of the staff.

This analysis can be used for various purposes, such as:

Compensation Review: Identifying if any salaries are outliers (unusually high or low).
Budget Planning: Understanding the average and range of salaries to forecast future payroll expenses.
Hiring Decisions: Setting a competitive salary range for new job openings based on the quartiles of current employees.
Fairness Analysis: Comparing the salary quartiles across different departments or roles to ensure equitable pay practices.

Result for QUARTILE(data, 0):

$55,000.00

Result for QUARTILE(data, 1):

$63,500.00

Result for QUARTILE(data, 2):

$72,000.00

Result for QUARTILE(data, 3):

$82,500.00

Result for QUARTILE(data, 4):

$95,000.00