QUARTILE.INC

Calculates the quartile of the data set, based on percentile values being between 0 and 1, including 0 or 1 itself.

Syntax:

QUARTILE.INC(array, quart)

array is required, and is the array or range of cells that you want to use to find the quartile.

quart is required, and is the quartile you want to find.

0: Returns minimum value (i.e. percentile 0)

1: Returns the first quartile (25th percentile, i.e. 0.25).

2: Returns the second quartile (50th percentile - median, i.e. 0.5).

3: Returns the third quartile (75th percentile, i.e. 0.75).

4: Returns maximum value (i.e. percentile 1)

Example:

If array, found in A1:A10, contains numbers 85, 92, 78, 90, 88, 75, 95, 80, 82, 70 and quart, found in B1, contains 0:

QUARTILE.INC(A1:A10, B1)

returns 70

If array, found in A1:A10, contains numbers 85, 92, 78, 90, 88, 75, 95, 80, 82, 70 and quart, found in B2, contains 1:

QUARTILE.INC(A1:A10, B2)

returns 78.5

If array, found in A1:A10, contains numbers 85, 92, 78, 90, 88, 75, 95, 80, 82, 70 and quart, found in B3, contains 2:

QUARTILE.INC(A1:A10, B3)

returns 83.5

If array, found in A1:A10, contains numbers 85, 92, 78, 90, 88, 75, 95, 80, 82, 70 and quart, found in B4, contains 3:

QUARTILE.INC(A1:A10, B4)

returns 89.5

If array, found in A1:A10, contains numbers 85, 92, 78, 90, 88, 75, 95, 80, 82, 70 and quart, found in B5, contains 4:

QUARTILE.INC(A1:A10, B5)

returns 95

Result for example using 0 for quart is found in C1.

Result for example using 1 for quart is found in C2.

Result for example using 2 for quart is found in C3.

Result for example using 3 for quart is found in C4.

Result for example using 4 for quart is found in C5.

	A	B	C
1	85	0	70
2	92	1	78.5
3	78	2	83.5
4	90	3	89.5
5	88	4	95
6	75
7	95
8	80
9	82
10	70

Application:

Employee Salary Analysis

Imagine you are an HR manager at a tech company and you want to analyze the salary distribution of your software engineers. You have the salaries of 20 engineers and you want to understand the salary ranges at different quartiles to ensure fairness and competitiveness.

The Dataset:

	Engineer ID	Salary
	A	B
1	E01	$85,000.00
2	E02	$92,000.00
3	E03	$95,000.00
4	E04	$100,000.00
5	E05	$102,000.00
6	E06	$105,000.00
7	E07	$108,000.00
8	E08	$110,000.00
9	E09	$112,000.00
10	E10	$115,000.00
11	E11	$118,000.00
12	E12	$120,000.00
13	E13	$125,000.00
14	E14	$130,000.00
15	E15	$135,000.00
16	E16	$140,000.00
17	E17	$145,000.00
18	E18	$150,000.00
19	E19	$160,000.00
20	E20	$175,000.00

Using the QUARTILE.INC Function:

You can use the QUARTILE.INC function to find the salaries at different quartiles. The syntax is QUARTILE.INC(array, quart).

In this case, your array is the range of salaries (e.g., B1:B20 if your data starts in cell B1), and quart is the quartile number you want to find (0, 1, 2, 3, or 4).

Minimum Salary (0th Quartile):
- Formula: QUARTILE.INC(B1:B20, 0)
- Result: $85,000
- Interpretation: This confirms the lowest salary in the dataset.
25th Percentile (1st Quartile):
- Formula: QUARTILE.INC(B1:B20, 1)
- Result: $104,250
- Interpretation: This is the salary at the 25th percentile. 25% of the software engineers earn a salary of $104,250 or less.
Median Salary (2nd Quartile):
- Formula: QUARTILE.INC(B1:B20, 2)
- Result: $116,500
- Interpretation: This is the median salary. Half of the software engineers earn less than $116,500 and half earn more.
75th Percentile (3rd Quartile):
- Formula: QUARTILE.INC(B1:B20, 3)
- Result: $136,250
- Interpretation: This is the salary at the 75th percentile. 75% of the software engineers earn a salary of $136,250 or less.
Maximum Salary (4th Quartile):
- Formula: QUARTILE.INC(B1:B20, 4)
- Result: $175,000
- Interpretation: This confirms the highest salary in the dataset.

Summary of Results:

	Quartile	Formula	Result	Interpretation
	A	B	C	D
1	0 (Min)	QUARTILE.INC(B1:B20, 0)	$85,000.00	The lowest salary is $85,000.
2	1 (25%)	QUARTILE.INC(B1:B20, 1)	$104,250.00	25% of engineers earn less than or equal to $104,250.
3	2 (Median)	QUARTILE.INC(B1:B20, 2)	$116,500.00	The median salary is $116,500.
4	3 (75%)	QUARTILE.INC(B1:B20, 3)	$136,250.00	75% of engineers earn less than or equal to $136,250.
5	4 (Max)	QUARTILE.INC(B1:B20, 4)	$175,000.00	The highest salary is $175,000.

Why This Is Useful:

This analysis helps the HR manager to:

Identify the salary range: The interquartile range ($136,250 - $104,250 = 32,000) shows the spread of the middle 50% of salaries.
Spot potential outliers: If a salary is far below the 1st quartile or far above the 3rd quartile, it might indicate an underpaid or exceptionally high-paid employee.
Benchmark against industry standards: The company can compare its 1st, 2nd, and 3rd quartile salaries to industry benchmarks to ensure it is competitive in the market.

Result for interquartile range:

$32,000.00