Calculate the Midrange in Google Sheets

Understanding the Midrange: Definition and Purpose

The midrange is one of the simplest measures used in descriptive statistics. Unlike the more common mean or median, the midrange focuses exclusively on the extreme values within a dataset to quickly estimate its center.

Statistically, the midrange serves as an initial indicator of where the middle point of a distribution lies. While it is rarely used in complex statistical analysis due to its sensitivity, it provides a fast and intuitive way to understand the spread and location of data, particularly when data collection is time-sensitive or highly preliminary.

For practical applications, especially within spreadsheet software like Google Sheets, calculating the midrange is straightforward, requiring only two fundamental functions: finding the maximum and minimum values.

The Midrange Formula: A Statistical Overview

The definition of the midrange is derived directly from its calculation. It is defined as the arithmetic average of the highest and lowest points observed in any given collection of data. This simplicity is both its greatest strength and its most significant weakness.

The formal equation for calculating this value is expressed as follows:

Midrange = (Largest Value + Smallest Value) / 2

This calculation yields a single value that attempts to represent the central location of the data range. It essentially splits the observed range of values precisely in half, giving us an idea of the dataset’s overall span and providing a basic measure of central tendency.

Step-by-Step Calculation in Google Sheets

To efficiently calculate the midrange within a dataset hosted in Google Sheets, we utilize built-in functions designed to identify extreme values. The key functions required are MAX() and MIN().

The process involves three simple steps that can be combined into a single, powerful formula:

1. Identify the range of cells containing your numerical data.
2. Use the MAX() function on that range to find the largest value, and the MIN() function to find the smallest value.
3. Sum these two extreme values and divide the result by two.

The combined formula structure that executes the midrange calculation in Google Sheets is shown below:

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

Practical Example: Finding the Center of a Dataset

Suppose we have the following sample dataset containing ten numerical observations in Google Sheets:

To calculate the midrange for the values located in cells A2 through A11, we input the formula directly into an empty cell. The required range reference is A2:A11.

The specific formula used for this dataset is =(MAX(A2:A11) + MIN(A2:A11)) / 2. This calculation identifies the maximum value (39) and the minimum value (10), sums them (49), and divides by two.

Applying this formula yields a midrange value of 24.5 for this specific dataset.

Limitations of the Midrange: The Impact of Outliers

The most significant drawback when utilizing the midrange is its extreme vulnerability to outliers. Since the calculation relies solely on the two most extreme points—the minimum and the maximum—any unusual fluctuation in these boundary values will disproportionately shift the resulting midrange, potentially misrepresenting the data’s true center.

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. The presence of just one extreme outlier can pull the midrange far away from where the majority of the data points are clustered.

For example, consider if the maximum value in our dataset was erroneously recorded as 120 instead of 39. The midrange calculation would then change drastically, resulting in a value of 65: (120 + 10) / 2 = 65.

Comparing Midrange to Other Measures of Central Tendency

Because of the sensitivity demonstrated above, statisticians typically prefer more robust measures of central tendency, such as the mean or the median, for comprehensive data analysis. These methods incorporate more data points and are thus less affected by single extreme values.

A better way to find where the “center” of a dataset is truly located is to use the mean (average) or the median of the dataset. The median, in particular, is highly resistant to outliers because it only considers the positional center of the data.

When comparing the three measures using the dataset containing the outlier (Max = 120), the differences are evident: the mean and median remain relatively stable, providing a more trustworthy center point.

Notice that the mean and the median are significantly less prone to being influenced by extreme values compared to the midrange and they give us a more accurate idea of where the center of the dataset is actually located.

Summary and Best Practices

While the midrange is exceptionally simple to calculate in Google Sheets using the (MAX() + MIN()) / 2 structure, its utility is limited primarily to datasets that are known to be symmetrical or those where the presence of outliers has already been addressed.

We recommend using the midrange only for preliminary data exploration or when working with data where the range itself is the primary focus. For robust statistical reporting, always defer to the median or trimmed mean to ensure reliability and minimize skewing effects.

Related: How to Calculate the Midrange in Excel