The range of a data set is the difference between its largest and smallest values. In Excel, calculate it by subtracting the result of MIN from the result of MAX.
Calculate the range with one formula
If your observations are in cells A2:A101, enter:
=MAX(A2:A101)-MIN(A2:A101)
Excel returns the numerical distance between the highest and lowest values.
Find the endpoints separately when useful
- Enter =MIN(A2:A101) to find the smallest observation.
- Enter =MAX(A2:A101) to find the largest observation.
- Subtract the minimum from the maximum.
Showing the endpoints can make the calculation easier to audit.
Do not confuse range with a cell range
Excel also uses the word “range” to mean a group of cells, such as A2:A101. In statistics, the range is a measure of spread. The two meanings are related only by terminology.
Check the data before calculating
Accidental totals, mixed units, imported errors, or a value entered in the wrong scale can radically change the result because only the minimum and maximum determine the range.
Range is sensitive to outliers
One unusually high or low observation can make the range much larger. For a less extreme-sensitive description of spread, consider the interquartile range or another statistic appropriate to the analysis.
Blank and text cells need interpretation
MAX and MIN generally ignore text in references, but that does not mean a dirty data set is valid. Verify that numbers stored as text or missing observations are handled intentionally before reporting the result.
Matt Jacobs has been working as an IT consultant for small businesses since receiving his Master’s degree in 2003. While he still does some consulting work, his primary focus now is on creating technology support content for SupportYourTech.com.
His work can be found on many websites and focuses on topics such as Microsoft Office, Apple devices, Android devices, Photoshop, and more.