How to Calculate the Midrange in Excel


The midrange of a dataset is calculated as:

Midrange = (largest value – smallest value) / 2

This value is simply the average of the largest and smallest values in the dataset and it gives us an idea of where the center of a dataset is located.

This tutorial explains how to calculate the midrange of a dataset in Excel.

Example: Calculating the Midrange in Excel

Suppose we have the following dataset in Excel:

Excel raw data values

To calculate the midrange, we can use one simple formula:

=(MAX(range of values) – MIN(range of values)) / 2

Column D shows the midrange of our dataset and column E shows the formula we used to calculate it:

Midrange calculation in Excel

The midrange for this dataset is 24.5.

The Drawback of Using the Midrange

The drawback of using the midrange is the fact that it can be easily influenced by outliers. If the minimum value of a dataset is unusually small or if the maximum value is unusually large, this can have a huge impact on the calculation of the midrange.

For example, consider if the maximum value in our dataset was 120. The midrange would then be equal to 66:

Midrange influenced by outliers in Excel

Recall that the midrange is supposed to give us an idea of where the center of a dataset is located. In this scenario, though, since the maximum value is an outlier it causes the midrange to be 66, which isn’t close to the center of our dataset at all.

Alternatives to the Midrange

In practice, the midrange is rarely used as a way to calculate the center of a dataset simply because there are better measurements available that are more robust to outliers. In particular, the following two metrics tend to be more accurate measures of center:

Mean: The average value in a dataset.

Median: The median value in a dataset. 

The following image shows the formulas we can use to calculate both the mean and the median of the dataset:

Midrange, mean, and median calculation in Excel

Notice that the mean is only slightly affected by the outlier while the median is not affected at all.

Leave a Reply

Your email address will not be published. Required fields are marked *