Calculate the Midrange in ExcelLearn how to calculate the midrange in Exel

Performing mathematical and statistical calculations in Excel is probably the most well-known use of the app.

It’s a spreadsheet app after all. And most data that you work on it contains numbers.

It works out too because Excel has many functions that cater to it such as SUM, AVG, STDEV.P, etc.

One of the measures in statistics is the midrange. To put it simply, the midrange is the average of the smallest and largest values in a dataset.

By calculating the midrange of a dataset, we can get an idea of where its (the dataset) center is located.

Hence, why it’s also known as the measure of center in statistics.

To calculate the midrange, you may use this formula:

Midrange = (Smallest Value + Largest Value)/2

It’s a pretty simple formula that makes it easy to translate in Excel terms. Suppose we have a range of cells that contain numerical values in an Excel sheet.

We can calculate the midrange of that dataset simply by using the following formula:

=(MIN(range)+MAX(range))/2

Where

range – refers to the range of cells that contain the numerical values

Let’s see this formula in action.

How to Calculate the Midrange in Excel

Let’s break down the formula we have above.

• MIN(range) – this function returns the smallest values within the specified range
• MAX(range) – this function returns the largest values within the specified range
• Then we add the two values together and divide the result by 2 to get the average, which is the midrange

Suppose we have this dataset:

The numerical values are located in cells A2:A25. This will be our range. Now let’s try using the formula:

As per our calculation, the midrange of the dataset is 26.

Wasn’t that easy? That’s just the simplicity of the midrange at work.

The Issue with the Midrange

While the midrange’s simplicity works to its advantage, it also results in some issues.

A major issue with the midrange is that it may fail to represent the center of the dataset if there’s an outlier.

For example, let’s add an outlier to our dataset above and then calculate midrange:

By adding an outlier value of 1071 to the dataset, the midrange significantly increased to 537 (from 26).

This is why the midrange isn’t widely used in statistical calculations. The probability of it failing to represent the center of the dataset is very high.

Alternatives to the Midrange

Rather than relying on the midrange, you may want to take a look at other statistical measures such as the mean and median.

The mean represents the average value of the dataset. You can calculate it by adding all the values in the dataset and then dividing the result by the number of observations (or values).

In Excel, the formula for calculating the mean is:

=AVG(range)

On the other hand, the median represents the middle value of the dataset. You can calculate it by arranging the dataset first in ascending order.

The middlemost value of that order is the median. In case there are two middlemost values, calculate the average of the two values.

In Excel, the formula for calculating the median is:

=MEDIAN(range)

Here is a comparison of the three measures (with and without the outlier).

We already know how having an outlier affects the midrange.

Compared to that, the effect on the mean and the median isn’t as much.

There is still a considerable increase in the mean but it isn’t as drastic as the midrange. The median is barely affected.

Conclusion

Calculating the midrange is simple.

You only need to calculate the average of the smallest and largest values in a dataset.

However, the midrange is easily influenced by the presence of outliers.

Thus, it isn’t the most reliable measure of the center of a dataset.