How to Find and Calculate Range in Excel.
Mathematically, you calculate a range by subtracting the minimum value from the maximum value of a particular data set. It represents the spread of values in a data set and is useful for measuring variability – the larger the range, the more scattered and variable your data is.
Luckily, finding the range of a dataset is easy when using Excel functions. Here’s how to do it.
How to Find and Calculate Range in 3 Steps
The easiest way to find a range in Microsoft Excel is to use the MIN and MAX functions. The MIN function calculates the largest value in a data set, and the MIN function calculates the smallest value.
First, make sure your data is neatly organized on the sheet for ease of analysis. You can then use the MIN and MAX functions like this:
Select a cell outside of your dataset (D1 in our example). In this cell, enter =MAX and select =MAX from the drop-down menu.
Select a range of cells by clicking and dragging. Alternatively, you can enter a range of cells manually (e.g. =MAX(B2:B15). Press Enter to confirm.
Select another cell (in our example D2) and enter =MIN. Repeat step 2 for this cell.
Now we need to find the difference between these values. To do this, select a cell (in our example D3) and use the subtraction function by typing =(cell containing the value MAX)-(cell containing the value MIN ). For example, =D1-D2.
You can calculate a range of values in one quick step by combining these functions in one cell. To do this, we will use our example where the dataset is contained in cells B2 through B15.
Using these cells, the range function would look like this: =MAX(B2:B15)-MIN(B2-B15)
All you have to do is change the cell values to match your data.
How to Find a Conditional Range in Excel
If your dataset has multiple outliers, you can calculate a conditional range that ignores the outliers. For example, take a look at this screenshot example:
Almost all values are between 40 and 80, but there are two values near 1000 and the two lowest values are close to 1. If you want to calculate the range but ignore these values, you will need to ignore values less than 5 and greater than 900. . This is where the MAXIFS and MINIFS functions come to the rescue.
MAXIFS adds a condition that ignores values above a certain number, and MINIFS ignores values less than a certain number (with each condition separated by a comma).
So for our example, the MAX function would look like this:
=MAXIFS(B2:B15,B2:B15,”<900”)
And the MIN function will look like this:
=MINIFS(B2:B15,B2:B15,”>5”)
In this case, the final Excel formula for calculating the conditional range will look like this:
=MAXLIMS(B2:B15,B2:B15”,<900″)-MINIMS(B2:B15,B2:B15”,>5″)
Note. The MAXIFS and MINIFS functions are only available in Excel 2019 and Microsoft Office 365, so if you’re using an older version, you’ll need to enable the IF function separately. For example: =MAX(B2:B15)-MIN(IF(B2:B15>5;B2:B15)
Data Analysis Has Never Been Easier
Microsoft Excel is a powerful data analysis tool. With this guide, you can easily calculate the range of any dataset, even if you need to remove outliers.
How to Find and Calculate Range in Excel
How to Find and Calculate Range in Excel