Excel Number Range Formula: Calculate, Count, and List

Formulas & functions · Updated November 19, 2026

To calculate the numerical range—the difference between the largest and smallest values—in cells A2:A10, use =MAX(A2:A10)-MIN(A2:A10). If by “number range” you mean counting values between two limits or creating a sequence of numbers, Excel uses different formulas for those tasks.

What “Number Range” Means in Excel

The phrase “number range formula” can describe several related but distinct jobs. In statistics, the range of a set of numbers is its spread: the maximum value minus the minimum value. In a worksheet, a range also means a group of cells, such as A2:A10. And people may use “number range” to mean the values between two endpoints, such as 10 through 20.

Those meanings call for different approaches. Use MAX minus MIN to measure the spread of existing data. Use COUNTIFS to count entries that fall between limits, or an AND test to label an individual value as inside or outside those limits. To create a list of consecutive values, use SEQUENCE in supported Excel versions. Choosing the task first avoids the common mistake of expecting one formula to do all four things.

Calculate the Range of a Set of Numbers

For a basic numerical range, identify the cells containing the data and subtract the smallest value from the largest. Assuming the values are in A2:A10, enter:

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

MAX returns the highest numeric value in the referenced cells; MIN returns the lowest. Subtracting the latter from the former gives the width of the data’s spread, not the number of entries. For example, if the values are 4, 9, 11, and 16, the result is 16 − 4, or 12. The formula does not require the data to be sorted.

To display the endpoints as well as the difference, calculate the minimum and maximum in separate cells. Assuming the original values are in A2:A10, enter =MIN(A2:A10) in one cell and =MAX(A2:A10) in another. This can make a summary easier to read, especially when the range is being reported alongside other statistics.

Worked Example: Understand the Result

Suppose a teacher records five quiz scores in B2:B6: 72, 85, 91, 85, and 68. With those scores in B2:B6, enter =MAX(B2:B6)-MIN(B2:B6). MAX returns 91 and MIN returns 68, so the formula returns 23 points. The repeated score of 85 does not change either endpoint and therefore does not affect the result.

A range of 23 does not mean there are 23 scores, nor does it mean that every score lies between 0 and 23. It says only that the gap from the lowest observed score to the highest is 23. If you want the number of recorded scores instead, use COUNT on the same data. If you want the average, use AVERAGE. These measurements answer different questions, even though they can be calculated from the same cells.

The range is sensitive to extreme values. If a data-entry error changes 68 to 6, the range becomes 85 because the minimum is now 6. That makes the range useful for a quick view of spread, but it also means that an unusual or incorrect endpoint can dominate the result. Check the underlying values when a range looks unexpectedly large.

Count Values Between Two Limits

If the values are in A2:A20 and the lower and upper limits are in D2 and E2, respectively, COUNTIFS can count entries within the limits, including both endpoints:

=COUNTIFS(A2:A20,">="&D2,A2:A20,"<="&E2)

In the formula, the comparison operators are joined to the limit-cell references with the ampersand. The first condition accepts values greater than or equal to D2; the second accepts values less than or equal to E2. For example, if D2 is 10, E2 is 20, and A2:A20 contains 10, 12, 20, and 21 among its entries, the formula counts 10, 12, and 20, but not 21. It returns 3 if those are the only qualifying entries.

Be clear about whether endpoints should count. For an exclusive interval—greater than the lower limit and less than the upper limit—change the criteria to ">"&D2 and "<"&E2. If the limit cells may be entered in either order, first decide whether reversed limits should be corrected or treated as an error; the COUNTIFS example assumes D2 is the lower limit.

Check Whether One Value Is Within a Range

To return TRUE when a value is between two limits and FALSE otherwise, assuming the value is in A2 and the limits are in D2 and E2, use:

=AND(A2>=MIN($D$2,$E$2),A2<=MAX($D$2,$E$2))

MIN and MAX make this test work even if someone types the two limits in reverse order. AND returns TRUE only when both comparisons are true, so the endpoints are included. The dollar signs keep the limit references fixed if you copy the formula down a column, while the reference to A2 can adjust to A3, A4, and so on.

This formula returns a logical result, not a custom message. To show a label instead, assuming the same cell layout, use =IF(AND(A2>=MIN($D$2,$E$2),A2<=MAX($D$2,$E$2)),"Within range","Outside range"). The words in quotation marks are the text Excel displays. This kind of check is useful for reviewing whether measurements meet an allowed interval, but it does not change or validate the original entry by itself.

Create a List of Consecutive Numbers

If you want Excel to generate numbers rather than analyze existing ones, SEQUENCE can spill a list into neighboring empty cells. Assuming A1 contains the start value 1 and B1 contains the end value 10, enter:

=SEQUENCE(B1-A1+1,1,A1,1)

The arguments specify the number of rows, number of columns, starting value, and step. With the stated inputs, the formula creates ten rows containing 1 through 10. The “+1” includes both endpoints. This formula assumes the end value is at least the start value and that the desired sequence uses whole-number steps of one.

SEQUENCE is available in Microsoft 365 and Excel 2021 or later. It returns a dynamic array, meaning results spill into cells below the formula; those cells need to be empty or Excel may show a #SPILL! error. In Excel versions without SEQUENCE, enter the first number in a cell, the next number beneath it, select both, and drag the fill handle down to extend the pattern. The fill handle is a practical alternative for a short fixed list.

Common Problems and Checks

  • Confusing spread with count: MAX minus MIN returns a difference. It does not count cells or tell you how many integers are between the endpoints.
  • Including or excluding endpoints unintentionally: Decide whether the boundary values count. COUNTIFS with greater-than-or-equal and less-than-or-equal includes them; strict comparison operators exclude them.
  • Numbers stored as text: A value that looks like a number but is stored as text may not behave as expected in numerical calculations or comparisons. Check the cell’s contents and number format, and convert text entries to numbers when appropriate.
  • Unexpected blanks or text: MAX and MIN ignore text and empty cells in a referenced range. If there are no numeric values, the result may not answer the question you intended. Check that the selected range contains the relevant data.
  • Hidden rows: MAX and MIN consider values in hidden rows too. Hiding a row does not remove its value from these calculations. If the analysis should use only visible rows, a different approach is needed.
  • Sequence spill blocked: A dynamic array cannot spill over cells that already contain data. Clear the destination cells or move the formula; do not assume the formula generated only part of the list.

For a basic range calculation, errors in referenced cells can also affect the result. If MAX or MIN returns an error, inspect the source cells for error values such as #N/A or #VALUE! rather than hiding the problem with an error-handling formula. A visible error can point to data that needs correction.

Choose the Formula That Matches Your Goal

Use the question you are trying to answer as the guide:

  • “How far apart are the largest and smallest observations?” Use =MAX(A2:A10)-MIN(A2:A10).
  • “How many observations fall between these two limits?” Use COUNTIFS with the appropriate inclusive or exclusive criteria.
  • “Does this particular value meet the interval?” Use AND, optionally nested inside IF to return a readable label.
  • “Can Excel fill a list from one endpoint to another?” Use SEQUENCE in a supported version, or the fill handle for a manual series.

These formulas are not interchangeable. For instance, if the endpoints are 3 and 8, their difference is 5, while the count of whole numbers from 3 through 8 inclusive is 6: 3, 4, 5, 6, 7, and 8. The count is one greater because both endpoints are included. For decimal endpoints or a list of measured observations, do not apply that integer-count shortcut; count the actual values using the relevant cells.

Frequently Asked Questions

What is the Excel formula for the range of numbers?
Use MAX minus MIN on the cells containing the numbers, such as =MAX(A2:A10)-MIN(A2:A10) when the values are in A2:A10. The result is the difference between the highest and lowest numeric values.

How do I count numbers between two values in Excel?
Use COUNTIFS with a lower-bound and an upper-bound condition on the data range. Use greater-than-or-equal and less-than-or-equal criteria if the two endpoint values should be included.

How do I make a number sequence in Excel?
In Microsoft 365 or Excel 2021 and later, SEQUENCE can create a spilling list. For older versions, type the start of the series and use the fill handle to extend it.

Does the range formula include the highest and lowest numbers?
Yes. MAX and MIN use the endpoints to calculate their difference. The result is the gap between them, not a count that includes or excludes individual values.

Why is my range formula returning an unexpected result?
Check that the referenced cells are the intended data and that number-like entries are stored as numbers. Also look for extreme values, since a single very high or low observation can substantially change the range.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.