QUARTILE.EXC

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

Syntax:

QUARTILE.EXC(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.

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).

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 1:

QUARTILE.EXC(A1:A10, B1)

returns 77.25

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

QUARTILE.EXC(A1:A10, B2)

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 B3, contains 3:

QUARTILE.EXC(A1:A10, B3)

returns 90.5

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

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

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

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

Application:

To understand the QUARTILE.EXC function, let's consider using the QUARTILE.EXC function to help with analyzing the sales performance of a retail store. The store manager wants to understand the distribution of daily sales revenue over a month. They have collected the daily sales data for 30 days.

Here is the data:

	Day	Daily Sales
	A	B
1	1	$2,500.00
2	2	$3,100.00
3	3	$2,800.00
4	4	$3,500.00
5	5	$2,900.00
6	6	$4,200.00
7	7	$3,800.00
8	8	$2,700.00
9	9	$3,300.00
10	10	$4,500.00
11	11	$3,600.00
12	12	$2,950.00
13	13	$3,250.00
14	14	$4,000.00
15	15	$3,150.00
16	16	$2,850.00
17	17	$3,900.00
18	18	$3,400.00
19	19	$4,100.00
20	20	$3,700.00
21	21	$2,600.00
22	22	$3,000.00
23	23	$3,550.00
24	24	$4,300.00
25	25	$2,750.00
26	26	$3,100.00
27	27	$3,850.00
28	28	$3,200.00
29	29	$4,400.00
30	30	$3,350.00

The manager wants to determine the first, second (median), and third quartiles of the sales data to identify the performance levels.

They choose to use the QUARTILE.EXC function because they want to exclude the minimum and maximum values from the quartile calculations, which is standard practice in many statistical analyses.

The QUARTILE.EXC function has the following syntax:

QUARTILE.EXC(array, quart)

array: The range of data (the daily sales in this case).
quart: The quartile to return (1 for the first quartile, 2 for the median, 3 for the third quartile).

Calculations:

First Quartile (Q1): This represents the value below which 25% of the data falls.
- Formula: QUARTILE.EXC(B1:B30, 1)
- Result: $2,937.50
- Interpretation: 25% of the days had sales revenue below $2,937.50.
Second Quartile (Q2) or Median: This represents the value below which 50% of the data falls.
- Formula: QUARTILE.EXC(B1:B30, 2)
- Result: $3,325
- Interpretation: 50% of the days had sales revenue below $3,325. This is the median sales figure.
Third Quartile (Q3): This represents the value below which 75% of the data falls.
- Formula: QUARTILE.EXC(B1:B30, 3)
- Result: $3,862.50
- Interpretation: 75% of the days had sales revenue below $3,862.50.

Summary of Results:

	Quartile	Value	Interpretation
	A	B	C
1	Q1 (25th Percentile)	$2,937.50	25% of the daily sales were below this value.
2	Q2 (50th Percentile / Median)	$3,325.00	The midpoint of the daily sales data.
3	Q3 (75th Percentile)	$3,862.50	75% of the daily sales were below this value.

Conclusion:

Using these quartile values, the store manager can gain valuable insights:

Performance Benchmarking: The manager can identify that a daily sales figure of $2,937.50 or less is in the bottom 25% of their performance. This could signal a need to investigate factors on those days, such as weather, competitor promotions, or staffing levels.
Target Setting: The manager can set a realistic target for a "good day" based on the third quartile. A target of $3,862.50 or more would mean the store is performing in its top 25%.
Data Analysis: The difference between the quartiles (the Interquartile Range or IQR) can show the spread of the data. In this case, the IQR is $3,862.50 - $2,937.50 = $925, which indicates a moderate level of variability in daily sales.

Result for IQR:

$925.00