How to Calculate Range in Excel: 4 Quick Ways Explained

by | Excel

Ready to calculate range in Excel? Great, you’re in the right place. We’ve got 4 quick and efficient ways to run you through, step-by-step.

To calculate the range of a dataset in Excel, start by using the MIN and MAX functions to find the smallest and largest values in your dataset. Then, subtract the minimum value from the maximum value to get the range. For example, if your data is in cells A1 through A10, the formula for the range would be =MAX(A1:A10) – MIN(A1:A10).

You can also use other methods to calculate range such as the SMALL and LARGE functions, and the Data Analysis Toolpak.

Today you will learn how to calculate a range in Excel. Each method will have examples for demonstration to help you better understand the concept too

So, let’s dive in and explore the range function.

How to Calculate Range in Excel

What is the Range?

For those of you new to Excel, let’s start from the beginning, for anyone with experience, feel free to scroll down to get going.

The range function in Excel is used to find the difference between the highest and lowest values in your data set.

It provides you with a comprehensive idea of your data’s spread or variation.

Suppose we have the following set of numbers: 82, 90, 76, 94, 68, 88, 79

The maximum value is 94. The minimum value is 68.

Therefore, the range will be 94-68 = 26.

The range gives you a good measure for the spread of your dataset.

4 Methods To Calculate Range In Excel

In this section, we will go over 4 methods to calculate Range in Excel.

The 4 methods we will run through are:

  1. Using the MIN and MAX functions

  2. Using the MIN and MAX functions with IF statement

  3. Using the SMALL and LARGE functions

  4. Using Data Analysis Toolpak

4 Methods To Calculate Range In Excel

1. How to Calculate Range Using MIN And MAX Functions

Calculating the Range using the MIN and MAX Excel functions is straightforward.

Follow the steps given below to find Range using this method:

Step 1: Identify The Data Range

To calculate the Range using this method, start by selecting the range of values for your formula.

Dataset under analysis

In the given dataset, we will select A2:A6 of the Set 1 column.

Step 2: Find The Maximum Value

In a new cell, enter the below formula to find the maximum value of your data.

=MAX(A2:A6)

The max function calculates the highest value in your data range.

Writing formula for finding maximum value

Press Enter and Excel will calculate the max value of the selected cells.

Maximum value calculated

Step 3: Find The Minimum Value

In another new cell, enter the formula to find the minimum value.

=MIN(A2:A6)

The min function calculates the minimum score or value in your data range.

Writing formula to find minimum value

Press Enter and Excel will calculate the minimum value of the selected cells.

Minimum value calculated

Step 4: Calculate the Range

To find the range, subtract the minimum value from the maximum value.

Calculating range

Press Enter, and Excel will calculate the range for you.

Range calculated

You can also directly combine these functions in one formula like

=MAX(A2:A6) - MIN(A2:A6)

This gives you the range in one step, without having to first calculate minimum and maximum values.

2. How to Calculate Range Using MIN And MAX Functions With IF Statements

Calculating the range in Excel using MIN and MAX functions combined with IF statements is useful when you want to compute the range based on certain conditions.

Step 1: Use MAX and MIN with IF for Conditional Range

Suppose you want to calculate the range of values in Set 1, but only for values greater than 150.

The formula for maximum value will be:

=MAX(IF(A2:A6>150, A2:A6))

In Excel, the formula looks like the following:

Writing conditional formula for finding maximum value

Press Enter, and Excel will calculate the maximum score.

Maximum value calculated

The formula for minimum value will be:

=MIN(IF(A2:A6>150, A2:A6))

In Excel, the formula looks like the following:

Writing conditional formula for finding minimum value

Press Enter, and Excel will calculate the minimum value.

Mainimum value calculated

Step 2: Calculate the Range with Conditions

In a new cell, subtract the conditional minimum from the conditional maximum to get the range.

In our case, the subtraction will look like the following:

=A8-A9

You can combine the above functions into one formula as well:

=MAX(IF(A2:A6>150, A2:A60)) - MIN(IF(A2:A6>150, A2:A60))

After you write the formula, press enter and Excel will calculate the range for you.

Range calculated by Excel

3. How to Use The SMALL And LARGE Functions to Calculate Range

You can also use SMALL and LARGE functions in Excel to calculate range.

This method is useful when you want to consider values other than the absolute minimum and maximum.

Follow the steps given below to calculate the range using this method:

Step 1: Use LARGE Function for Nth Largest Value

You can use the LARGE function to find the nth largest value in your data.

The formula will be:

=LARGE(range,1)

Enter the above formula in Excel.

Wriiting formula for largest value

Press Enter and Excel will find the largest value for you.

Largest value calculated

Step 2: Use SMALL Function for Nth Smallest Value

The SMALL function works similarly but for the smallest values.

To get the smallest value, you can use the following formula:

 =SMALL(range, 1)

Enter the above formula in Excel, and change the range according to your requirements.

Wriiting formula for smallest value

Press Enter and Excel will find the smallest value for you.

Smallest value calculated

Step 3: Calculate The Range

Subtract the result of the SMALL function from the LARGE function.

Subtracting small value from large value

Press Enter, and Excel will calculate the range for you.

4. How to Calculate Range Using Data Analysis Toolpak

With the data analysis toolpak, you can get a comprehensive statistical overview of your data, including the range.

On the top left side of your Excel window, select File, and then click Get Add-ins.

Navigating to the Get Add-in in Excel

Search for Data Analysis Toolpak, select the first add-in, and then click Add.

Installing Data Analysis Toolpack

Select the cells that you want to find the range of, navigate to the Home tab, and then click Show Toolpak. From the list of calculations, select Descriptive Statistics.

Navigating to Descriptive Statistics

Choose the input range and the location where you want the output to display.

Click OK.

Configuring descriptive statistics

Excel will display the descriptive statistics, including the range.

Range caclulated with Analysis toolpack

Learn how to remove duplicate rows in Power Query by watching the following video:

Final Thoughts

Gone are the days when you had to spend hours with complex calculations or manual measurements to assess your data’s scale. With the range function in Excel, getting a quick and informative picture of your data is just a few clicks away!

This fundamental tool not only helps in understanding your data’s spread but also serves as the foundation for advanced analytics. From calculating central tendencies to exploring the depth of data through standard deviations, the range function offers a powerful springboard for deeper insights.

Frequently Asked Questions

In this section, you will find some frequently asked questions you may have when calculating range in Excel.

Close-up image of a dashboard on laptop

How to write a formula to find the range in Excel?

To write a formula to find the range of a dataset in Excel, you would use the combination of the MAX and MIN functions in the following way:

=MAX(range) - MIN(range)

What will be the output if there is a missing or default value?

If there are missing (blank) or default values in your dataset when calculating range in Excel, the MIN and MAX functions used for range calculation will ignore the blank cells.

However, if there are cells with default values (like zeros), they will be included in the calculation, which might significantly affect the resulting range.

Can I find the range without MIN and MAX functions?

Yes, you can find the range without using the MIN and MAX functions in Excel.

One alternative method is to sort your data in ascending order and then subtract the first value (now the minimum) from the last value (now the maximum).

This gives you the range. However, this method changes the original order of your data, so it’s advisable to make a copy before sorting if the order is important.

What is the general range formula in Excel?

To calculate the range, you can use the formula =MAX(range) – MIN(range), where “range” refers to the range of cells containing the dataset.

This formula returns a single value, which is the difference between the highest and the lowest value in the specified range, representing the overall spread of the data.

Related Posts