The QUARTILE.EXC function is a statistical function which calculates the first, second, and third quartile values of a range of data. Quartiles can be understood as the value which represents a marker separating data into equal buckets. Each bucket contains exactly 25% of the total results from the dataset. The EXC refers to the fact that if there is an odd number of values in the total dataset, the median value is removed when calculating the first and third quartile.

This is the format of the QUARTILE.EXC function:

=QUARTILE.EXC(DATA RANGE, QUARTILE)

Here is an example of the QUARTILE.EXC function:

=QUARTILE.EXC({1,2,3,4,5},1) = 1.5

QUARTILE.EXC returning the first quartile of the selected range of values

This function is valuable when taking on any statistical analysis within any dataset. The quartiles of a sample of data should be able to signal something about the data being looked at. While it may not provide a definitive answer, it can lead to more critical analysis. It can also signal any skew there may be in the data.

An example of a functional implementation of this would be mapping out the distribution of total transaction amounts. By determining your Q1, Q2, and Q3 values, you will have a visual representation of how much your customers are consistently willing to spend on a single transaction. This can help with promotional planning and pricing decisions.

=QUARTILE.EXC(RANGE WITH TRANSACTION AMOUNTS, 1)

=QUARTILE.EXC(RANGE WITH TRANSACTION AMOUNTS, 2)

=QUARTILE.EXC(RANGE WITH TRANSACTION AMOUNTS, 3)