How to Calculate Range in Excel
Excel's MIN and MAX functions locate the lower and upper numbers in a range. Subtracting those figures returns the range of values.
Finding the smallest and largest values in an extensive list of figures — as well as the range between those numbers — can be an exhausting process. However, Excel offers several functions that reduce the tedium by automatically extracting pertinent numbers.
Use the MIN function to locate the lowest value in a range of cells. This function follows the format =MIN(range) with "range" being a range of cells, such as "=MIN(A1:D5)," or a series of numbers separated by commas, such as "=MIN(1,2,3,4,5)."
Another option is the SMALL function that returns the nth smallest value in a series using the format =SMALL(range,n). This is useful when you have to exclude outliers that are obvious errors, such as "-23" when the database should only contain positive numbers.
Use the MAX function to find the highest value using the format =MAX(range). Replace "range" with the same range of cells or series of numbers that you entered in the MIN function. As an example, you might enter "=MAX(A1:D5)" or "=MAX(1,2,3,4,5)."
Similar to the SMALL function, the LARGE function returns the nth largest value using the format =LARGE(range,n).
Subtract the minimum value from the maximum value using the format =F1-E1, where F1 is the cell containing the maximum value and E1 is the cell containing the minimum value. The result is the range of value in which your data lies.
If you only need the range between upper and lower values, simply use the formula =MAX(range)-MIN(range).