Average in Excel Between Two Values: Formula & Tips

Formulas & functions · Updated November 18, 2026

To average numbers between two values in Excel, use AVERAGEIFS with a lower-bound and an upper-bound condition. For an inclusive range of 10 through 20 in A2:A100, enter =AVERAGEIFS(A2:A100,A2:A100,">=10",A2:A100,"<=20").

The basic formula and how it works

AVERAGEIFS calculates the mean of numbers that meet every condition you specify. Its first argument is the range to average; the remaining arguments come in pairs: a range to check and a criterion for that range. When the numbers being checked are also the numbers being averaged, use the same range for the first argument and both criteria ranges.

For a concrete example, suppose A2:A7 contains 8, 10, 12, 18, 20, and 22. The following formula includes values equal to either limit as well as values inside the limits.

With the six example numbers in A2:A7 and limits of 10 and 20 typed into the formula, use: =AVERAGEIFS(A2:A7,A2:A7,">=10",A2:A7,"<=20"). It returns 15: the matching values are 10, 12, 18, and 20, and their sum of 60 divided by 4 is 15.

The two criteria are joined as an AND condition. A value must be at least 10 and at most 20 to count. A number outside either limit is excluded; numbers elsewhere in the worksheet do not affect the calculation.

Inclusive versus exclusive limits

The symbols in the criteria determine whether the endpoints count. The usual meaning of “between 10 and 20” is inclusive: 10 and 20 qualify, so use >= and <=. If you mean strictly greater than 10 and strictly less than 20, use > and < instead.

With 8, 10, 12, 18, 20, and 22 in A2:A7, this formula excludes both endpoints: =AVERAGEIFS(A2:A7,A2:A7,">10",A2:A7,"<20"). It returns 15 because only 12 and 18 qualify, and (12 + 18) / 2 = 15.

Be precise about this choice in reports. For example, “scores from 60 to 80” often includes both 60 and 80, while “scores above 60 and below 80” excludes them. The worksheet formula cannot infer which interpretation you intend.

Use cells for the lower and upper bounds

Putting the limits in cells makes the calculation easier to update and reuse. For example, place the lower bound in D1 and the upper bound in E1. Excel criteria that combine a comparison operator with a cell reference need concatenation: join the operator text to the cell reference with &.

With numbers in A2:A7, the lower limit in D1, and the upper limit in E1, use: =AVERAGEIFS(A2:A7,A2:A7,">="&D1,A2:A7,"<="&E1). If D1 is 10 and E1 is 20, the result is 15, using the same four qualifying values as the basic example.

Changing D1 or E1 recalculates the average automatically. This is useful for a dashboard where readers choose a minimum and maximum, or when you need to repeat the same calculation for several ranges. Keep the limits numeric if the values being checked are numeric; numbers stored as text can produce confusing comparisons or be omitted from calculations.

Average one column based on limits in another

Sometimes the values that determine eligibility are not the values you want to average. For example, you might average sales amounts only for records whose quantity is between two limits. In that case, the first argument is the amount range, while the criteria ranges point to the quantity column.

Suppose A2:A5 contains quantities 10, 12, 18, and 20, and B2:B5 contains the corresponding amounts 100, 120, 180, and 200. With lower and upper limits in D1 and E1, use: =AVERAGEIFS(B2:B5,A2:A5,">="&D1,A2:A5,"<="&E1). If D1 is 12 and E1 is 18, it returns 150: the qualifying amounts are 120 and 180, whose mean is 150.

You can add further criteria pairs when the average must satisfy other conditions too. For instance, a separate region column could be required to equal “North,” in addition to the quantity being within bounds. Each additional condition narrows the records because all the criteria in AVERAGEIFS must be true. Make sure the average range and each criteria range cover corresponding rows and have the same dimensions; mismatched range sizes can cause an error.

Working with dates and times

Excel stores real dates as numbers, so date limits work with the same comparison operators. Enter actual dates in the bound cells rather than relying on text that merely looks like a date. For a date-only column, an inclusive start and end date can use >= for the start and <= for the end.

When the data includes times as well as dates, an upper limit set to a date usually represents midnight at the start of that day. A condition of “less than or equal to the end date” can therefore leave out records later on that date. A reliable approach is to include the start date and use a strict upper limit of the day after the end date.

Suppose A2:A5 contains timestamps of Jan 1 at 9 a.m., Jan 2 at noon, Jan 2 at 6 p.m., and Jan 3 at 10 a.m.; B2:B5 contains 10, 20, 40, and 100. Put Jan 1 in D1 and Jan 2 in E1. Use: =AVERAGEIFS(B2:B5,A2:A5,">="&D1,A2:A5,"<"&E1+1). The result is about 23.33: the first three amounts qualify, and (10 + 20 + 40) / 3 = 23.333…. The <E1+1 condition includes every time on Jan 2 but excludes Jan 3.

AVERAGEIFS compared with AVERAGEIF and FILTER

AVERAGEIF handles one condition, which is why it is not the most direct choice for a lower and upper bound: those are two separate tests. AVERAGEIFS is designed for multiple conditions and is generally the clearest option for this task. It is available in current desktop Excel for Microsoft 365 on Windows, and the formulas work the same way in Excel for Mac.

In Microsoft 365 and Excel 2021 or later, FILTER offers another way to select qualifying values before averaging them. With values in A2:A7 and limits in D1 and E1, use: =AVERAGE(FILTER(A2:A7,(A2:A7>=D1)*(A2:A7<=E1))). With D1 equal to 10 and E1 equal to 20, the filtered values are 10, 12, 18, and 20, so the answer is 15.

The asterisk between the two logical tests combines them as AND for the array calculation. FILTER can be handy when you also want to display the qualifying values, but for a single average, AVERAGEIFS is usually simpler. If no values meet the conditions, FILTER without an “if empty” argument returns a #CALC! error; AVERAGEIFS returns #DIV/0! when there are no matching numeric values to average.

Common errors and checks

If the formula does not return the expected result, check the limits, data types, and ranges before changing the calculation. These are common causes:

  • Wrong endpoint behavior: A formula using > excludes the lower bound. Use >= when the lower endpoint should count, and make the equivalent decision for the upper bound.
  • Limits stored as text: Make sure numeric bounds and values are actual numbers. A cell may look like 10 while containing text, especially after importing data.
  • Incorrect range alignment: In a formula that averages one column based on another, each range must refer to matching rows. Do not include a header in one range but not the others.
  • No matching numbers: A valid formula can still return #DIV/0! if no numeric values meet both conditions. Confirm that the bounds are in the intended order and that the data falls inside them.
  • Errors in the source data: Error values in relevant source ranges can affect a calculation. Locate and correct the underlying errors rather than assuming the result is an ordinary average.
  • Unexpected zeroes or blanks: Blank cells are not numeric values to average, but a genuine zero is included and can lower the result. Check whether zero means “no result” in your data or is a valid measurement.

A quick manual check is to list the qualifying values, add them, and divide by their count. This catches off-by-one boundaries and reveals whether a zero or an unexpected record is being included. For repeated work, test the formula on a small sample with known results before using it across a large table.

Frequently Asked Questions

How do I average numbers between two cells in Excel?
Use AVERAGEIFS and join each comparison operator to its bound cell with &. For values in A2:A100 and lower and upper limits in D1 and E1, use =AVERAGEIFS(A2:A100,A2:A100,">="&D1,A2:A100,"<="&E1).

Does Excel include the two boundary values?
Yes, if the criteria use >= and <=. Replace those with > and < if values equal to either boundary should be excluded.

Why does AVERAGEIFS return #DIV/0?
Usually no numeric values matched all the criteria. Check that the limits are correct, the ranges align, and the eligible cells contain numbers rather than text. The error can occur even when the formula itself is valid.

Can I average values between two dates?
Yes. Use the date column as both criteria ranges and specify a start and end date. If records contain times, use a strict upper bound equal to the day after the final date so all times on that date are included.

Can I average one column based on another column being in range?
Yes. Put the column to average first in AVERAGEIFS, then use the other column as the criteria range for the lower- and upper-bound tests. Add more criteria pairs if records must also meet conditions such as a particular region or category.

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.