sodcalculator

10 Tech How-To

How to Calculate Range in Google Sheets

Guide 10-18347 · Updated · Step by step

This guide shows you how to calculate the range of a dataset in Google Sheets. The range is the difference between the largest and smallest values, giving you a quick sense of how spread out your numbers are. You’ll learn the exact formulas to use, how to handle blanks and errors, and common pitfalls to avoid.

What You’ll Need

  • A Google Sheets document with your data in a column or row.
  • Basic familiarity with typing formulas into a cell (starting with an equals sign).

Step-by-Step

  1. Open your Google Sheet and locate the column or row containing your numeric data. For this example, assume your numbers are in cells A1 through A10.
  2. Find the maximum value. Click an empty cell where you want the range to appear, type =MAX(A1:A10), and press Enter. This returns the largest number in the set.
  3. Find the minimum value. In another empty cell, type =MIN(A1:A10) and press Enter. This returns the smallest number.
  4. Subtract the minimum from the maximum. In a third cell, type =MAX(A1:A10)-MIN(A1:A10) and press Enter. The result is your range.
  5. Use a single combined formula (optional). Instead of three separate cells, you can enter =MAX(A1:A10)-MIN(A1:A10) directly in one cell. This is the most efficient approach for a quick answer.
  6. Adjust the range reference to match your actual data. If your data spans A1 to A50, replace A1:A10 with A1:A50. For data in a row, use something like B1:K1.
  7. Handle non-contiguous data. If your numbers are in separate cells (e.g., A1, A3, A5), use =MAX(A1, A3, A5)-MIN(A1, A3, A5) with commas separating the cell references.
  8. Ignore text and blanks. Both MAX and MIN automatically ignore empty cells and text values within the referenced range, so you don’t need to clean your data first—as long as the text isn’t in a cell that also contains a number.
  9. Format the result. If your range shows as a date or a long decimal, go to Format > Number and choose “Number” or “Automatic” to display it correctly.

Common Mistakes

  • Reversing the order: Always subtract MIN from MAX. If you do =MIN(A1:A10)-MAX(A1:A10), you’ll get a negative number that is the absolute value of the range but with the wrong sign.
  • Including the header row: If your first row is a label like “Sales,” and you include it in the range (e.g., A1:A10 where A1 is text), MAX and MIN will ignore it, so no harm—but if the header is a number like a year, it will be counted, skewing your result.
  • Using the wrong range size: A common typo is referencing A1:A9 when your data goes to A10, missing the last value. Double-check the last cell in your selection.
  • Forgetting the equals sign: Typing MAX(A1:A10) without the leading = will be treated as plain text, not a formula.
  • Mixing data types: If some cells contain numbers stored as text (e.g., “5” with an apostrophe), MAX and MIN may ignore them. Convert them to numbers using Data > Split text to columns or by multiplying by 1.

Tips

  • Use named ranges: Select your data, go to Data > Named ranges, and name it something like “SalesData”. Then use =MAX(SalesData)-MIN(SalesData) for cleaner formulas.
  • Combine with other stats: For a fuller picture, also calculate the mean (=AVERAGE(A1:A10)) and standard deviation (=STDEV(A1:A10)) alongside the range.
  • Conditional range: To find the range only for values meeting a condition (e.g., values above 10), use array formulas like =MAX(IF(A1:A10>10, A1:A10))-MIN(IF(A1:A10>10, A1:A10)) and press Ctrl+Shift+Enter (or just Enter in newer Sheets).
  • Check for outliers: A very large range may indicate an outlier. Use the QUARTILE function to see if the middle 50% of data is much tighter.
  • Use the status bar: For a quick look without formulas, select your data cells—Google Sheets shows the range in the bottom-right status bar, but only if you enable it via View > Show status bar and it’s not displaying something else.

Troubleshooting

  • Formula returns #VALUE!: This usually means one of the cells contains an error value (like #DIV/0!) or text that can’t be interpreted. Check your data for error cells and fix them, or use =MAX(IFERROR(A1:A10)) as an array formula to ignore errors.
  • Result is 0 but data isn’t all the same: This happens when MAX and MIN are referencing the wrong range (e.g., a single cell). Click on the cell with the formula and look at the highlighted border to confirm the correct range is selected.
  • Result is a date instead of a number: Google Sheets sometimes auto-formats numbers as dates. Select the cell, go to Format > Number > Number to fix it.
  • Formula not calculating: If you see the formula text instead of a value, check that the cell is formatted as “Plain text” (change via Format > Number &em;gt; Number), and that you haven’t accidentally typed a space before the equals sign.
  • MAX and MIN ignore some cells: If you have cells with formulas that return empty strings (like IF(A1>0, A1, "")), MAX and MIN treat them as text and ignore them. Replace empty strings with 0 if you want them included, or leave as is if you don’t.

Frequently Asked Questions

Q: Does the range formula work with negative numbers?
A: Yes. MAX and MIN handle negatives correctly. For example, data from -5 to 10 gives a range of 15.

Q: Can I calculate range for non-numeric data?
A: No. Range is defined for numbers only. For text, you might count unique values with =COUNTA(UNIQUE(A1:A10)), but that’s not the same as range.

Q: How do I exclude a specific cell from the range?
A: Use separate references with commas, like =MAX(A1:A5, A7:A10)-MIN(A1:A5, A7:A10) to skip A6.

Q: What’s the difference between range and interquartile range (IQR)?
A: Range is max minus min, which is sensitive to outliers. IQR is the difference between the 75th and 25th percentiles, ignoring extreme values. Use =QUARTILE(A1:A10, 3)-QUARTILE(A1:A10, 1) for IQR.

Q: Will the range update automatically when I add new data?
A: Only if you extend the range reference. If you use A1:A10 and add data in A11, it won’t include it. Use a full-column reference like A:A to cover all rows, but be careful not to include headers or stray values.

Q: Can I calculate range for a filtered dataset?
A: Yes, but you need to use the SUBTOTAL function. =SUBTOTAL(4, A1:A10)-SUBTOTAL(5, A1:A10) gives the range of visible cells only (4 is MAX, 5 is MIN).

Related

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →