16.13: Microsoft Excel: Median, Quartile range, and Box Plots

In Microsoft Excel, calculating the median, interquartile range, and creating box plots can help understand the distribution of your data.

Median and Quartile Range: The median is calculated using the formula `=MEDIAN(range)', which provides the middle value of your data set. Quartiles divide your data into four equal parts. To find the first and third quartiles, use ‘=QUARTILE(range, 1)' and ‘=QUARTILE(range, 3)', respectively. The interquartile range (IQR), which measures data spread, is calculated as `Q3 - Q1`.

Box Plots: To create a box plot, start by calculating the median, quartiles (Q1 and Q3), and minimum and maximum values. This chart visually represents the median, quartiles, and potential outliers, providing a clear view of data spread and central tendency.

Box plots are particularly useful for identifying data skewness, variability, and outliers. The box shows the IQR of the data, and in the given example, the whiskers span the range of the data. However, in some cases when there are extreme values, outliers (shown as dots) may appear beyond the whiskers.This makes box plots a valuable tool for quickly summarizing the key characteristics of your data set in fields like statistics, quality control, or scientific research.

Consider the following three datasets. Now, select them together, select the Insert option, select Insert Statistic Chart, and then Box and Whisker.

In a boxplot, the central line is the data's median or second quartile. The box edges indicate the first and the third quartiles. Lastly, extreme points show the minimum and maximum values in the selected data range.

In Microsoft Excel, the data median is determined using the function MEDIAN.

Similarly, the minimum and maximum values are determined using MIN and MAX functions.

Two different functions — QUARTILE.EXC and QUARTILE.INC — return the quartile values.

Function QUARTILE.EXC excludes the endpoints or the minimum and maximum values of the data to calculate quartiles. Select the data range for the array, and select 1, 2, or 3 for a quart to get the first, second, and third quartile values.

Function QUARTLE.INC considers the entire range of values. Here, select 0, 1, 2, 3, or 4 for the quart values to get the data's minimum, first quartile, median, third quartile, and maximum values.