How to Calculate the Average of Values Between Two Numbers in Excel

AVERAGEIFS(average_range, criterion_range1, criterion1, criterion_range2, criterion2, ...)

---

You can use the following formula to calculate the average of values in a range (linking 3/5) in Excel (linking 2/5) only for the values that fall between two specific values:

=AVERAGEIFS(B:B,B:B,">=90",B:B,"<=95")

This particular formula dictates that the calculation should only include values in column B that satisfy two conditions: the value must be greater than or equal to 90, and simultaneously, the value must be less than or equal to 95. This powerful use of dual criteria (linking 2/5) ensures that only the data points falling within the defined closed interval [90, 95] contribute to the final averaged result. The following sections will provide practical, detailed examples demonstrating this function in action.

Deconstructing the Arguments of the AVERAGEIFS Formula

Achieving conditional averages successfully depends on correctly identifying and structuring the arguments within the AVERAGEIFS function (linking 3/5). The formula must clearly distinguish between the data to be averaged and the data used for filtering. In the formula `=AVERAGEIFS(B:B, B:B, “>=90”, B:B, “<=95")`, there are five essential arguments that define the conditional calculation process, which we must analyze sequentially for clarity and implementation accuracy.

The first argument, `average_range` (specified here as `B:B`), is the range (linking 4/5) containing the actual numerical values that will be averaged. Critically, this range must contain numbers; if it contains text or logical values, those cells are ignored in the calculation. The subsequent arguments are always supplied in pairs: a criteria range followed by its associated criterion (linking 3/5). Since we are applying two conditions to the same set of values, we must provide two complete criteria pairs, one for the lower bound and one for the upper bound.

The first criteria pair establishes the lower bound and determines the minimum acceptable value:

• Criterion Range 1 (B:B): This range tells Excel where to look for the first condition. In this case, we are checking the values within the same column B, meaning the data being filtered is the same as the data being averaged.
• Criterion 1 (">=90"): This is the condition itself, which combines a relational operator and the numerical threshold, and must always be enclosed in quotation marks when involving operators like greater than or equal to (`>=`). This ensures that only values that satisfy the minimum requirement of 90 are considered for the average.

The second criteria pair establishes the upper bound, which is necessary to ensure the calculation does not include values above the desired threshold, thus defining the maximum acceptable value:

• Criterion Range 2 (B:B): Again, this references the data in column B, ensuring continuity in the filtering process applied to the dataset.
• Criterion 2 ("<=95"): This condition filters the data further, requiring that the values must also be less than or equal to 95. If this second criterion were omitted, the function would simply return the average of all scores 90 and above, failing to define the necessary upper limit for the calculation.

Example 1: Calculating Conditional Average Within a Single Data Column

This first example illustrates the most common application of the `AVERAGEIFS` function for range-based calculations: filtering data within the same column that holds the values to be averaged. This scenario often arises when performing quality control analysis or evaluating performance metrics where thresholds define acceptable limits. Suppose we are tracking the performance of students and wish to assess the central tendency of those who achieved scores within a specific high-performance band, excluding perfect scores and lower results.

We are provided with a dataset detailing the exam scores for 15 students. Our objective is strictly defined: calculate the average exam score for students who received a score that is between 90 and 95, inclusive of those boundary values. This requires setting up the AVERAGEIFS function (linking 4/5) so that both the values being averaged and the conditions being checked refer to the same column, in this instance, column A.

Below is the visual representation of the initial dataset we are working with, where Column A contains the exam scores. We can visually identify the scores that should be included in the calculation: 90, 92, 92, 93, and 95. All other scores, such as 88 or 98, fall outside our specified range and must be excluded by the formula’s logic.

Applying the Formula and Verifying Results (Example 1)

To isolate the scores that satisfy the condition (Score >= 90 AND Score <= 95), we implement the formula below. It is essential to remember that we are referencing Column A for all three components: the average range, Criterion Range 1, and Criterion Range 2. This identical referencing is the hallmark of conditional averaging applied to a single column.

=AVERAGEIFS(A:A,A:A,">=90",A:A,"<=95")

Upon execution, Excel (linking 3/5) first filters the scores, identifying only 90, 92, 92, 93, and 95 as valid data points. It then calculates the arithmetic mean (linking 3/5) of these five values. The result of this calculation, as shown in the practical application screenshot below, provides the targeted average precisely as required by the business logic defined by the boundary conditions.

The computed average exam score for students falling within the defined 90-95 range (linking 5/5) is confirmed as 92.4. Manual verification reinforces the accuracy of the conditional calculation: the sum of the qualifying scores (90 + 92 + 92 + 93 + 95 = 462) divided by the count of qualifying scores (5) yields exactly 92.4. This successful demonstration proves the capability of `AVERAGEIFS` to accurately isolate and average specific subsets of numerical data based on dual boundary conditions within a single field.

Example 2: Conditional Averaging Across Multiple Dependent Columns

A more sophisticated application of the AVERAGEIFS function (linking 5/5) involves scenarios where the criteria (linking 4/5) are applied to one range (the independent variable), but the average is calculated on another corresponding range (the dependent variable). This is extremely useful in correlational analysis where we seek to understand the performance of a dependent metric contingent upon strict limits placed on an independent metric, such as determining salary based on years of experience constraints.

Consider a dataset tracking basketball players, where Column A represents their height (in inches) and Column B represents the average points they scored per game. Our analytical goal is to determine the average points scored exclusively by players whose height falls within a mid-range threshold, specifically between 70 inches and 75 inches (inclusive). Here, height dictates who is included, but points scored is the value that is averaged, highlighting the distinction between the filtering range and the averaging range.

The dataset below clearly defines our inputs: height in Column A and points scored in Column B. Notice how the criteria—the height constraint—must be applied to Column A, while the calculation—the points average—must be drawn from Column B. This decoupling of the criterion range and the average range is the key difference from Example 1.

Executing the Multi-Range Formula and Review

To achieve this specific conditional average, the arguments in the `AVERAGEIFS` function must be structured to reflect the separation between the averaging range and the criteria ranges. The `average_range` must be Column B, as this contains the numerical values we want to find the mean (linking 4/5) of. The subsequent two criteria pairs must both reference Column A, since that is where the height constraint applies.

The formula used to achieve this multi-range calculation is as follows:

=AVERAGEIFS(B:B,A:A,">=70",A:A,"<=75")

In this structure, Excel (linking 4/5) first checks Column A for heights greater than or equal to 70 and less than or equal to 75. It then maps the corresponding scores in Column B for those qualifying players and calculates their average. The resulting average points scored for players between 70 and 75 inches is calculated precisely, demonstrating the flexibility of the function in handling complex, related datasets where filtering is based on a factor different from the value being summarized.

As observed in the result, the average points scored for players within the specified height parameters is 17.833. We can manually confirm this by identifying the points scored by players whose height is 70, 72, 73, and 75 inches (14, 14, 16, 19, 20, 24). The sum (14 + 14 + 16 + 19 + 20 + 24) is 107. Dividing 107 by the count of qualifying players (6) yields approximately 17.833. This example clearly demonstrates how conditional logic applied to one variable can filter results for averaging another related variable, providing deep analytical capabilities.

Handling Open Intervals and Exclusionary Criteria

While the examples above utilized closed intervals (using `>=` and `<=`), the `AVERAGEIFS` function is equally capable of handling open or semi-open intervals by adjusting the comparison operators. Understanding the distinction between inclusive and exclusive conditions is paramount for accurate data filtering, as slight changes in the operator can significantly alter the resulting mean (linking 5/5). If the requirement was to find the average of scores strictly between 90 and 95 (excluding 90 and 95 themselves), the criteria (linking 5/5) would need to be modified.

For an open interval (90 < Score < 95), we would use the strict greater than (`>`) and strict less than (`<`) operators. The formula would be adjusted as follows, ensuring that the boundary values themselves are excluded from the calculation:

=AVERAGEIFS(A:A,A:A,">90",A:A,"<95")

In this revised scenario using the data from Example 1, scores of 90 and 95 would be excluded, leaving only 92, 92, and 93 to be averaged, resulting in an average of 92.333. This flexibility allows analysts to define boundaries with precise mathematical accuracy, whether working with performance tiers, inventory levels, or financial data. Mastering the correct use of relational operators within the quoted criteria is a fundamental skill for advanced Excel (linking 5/5) usage.

Best Practices for Using Conditional Averaging

When implementing complex conditional formulas like `AVERAGEIFS`, adhering to best practices ensures reliability and maintainability, especially when working with extensive datasets or models that will be updated frequently by different users. One critical best practice is the avoidance of hardcoding numerical criteria directly into the formula whenever possible. Hardcoding makes auditing and updating the spreadsheet difficult and prone to error.

Instead of typing `“>=90”` directly into the formula, it is far more efficient to reference cells that contain the boundary values. This allows the spreadsheet to function dynamically. For instance, if cell E1 contains the lower limit 90 and F1 contains the upper limit 95, the formula structure must be adapted using the concatenation operator (`&`) to link the relational operator string to the cell value, ensuring Excel reads the full condition correctly:

=AVERAGEIFS(A:A,A:A,">="&E1,A:A,"<="&F1)

This dynamic approach significantly improves the user experience, allowing the user to easily change the lower and upper limits simply by updating cells E1 and F1, without having to edit the complex formula itself. Furthermore, always ensure that all ranges specified in the function (the average range and all criterion ranges) have the same number of rows or columns, depending on the orientation of the data. Mismatched range sizes can lead to unexpected errors or inaccurate results, a common pitfall in advanced conditional calculations.

Conclusion: The Power of Targeted Data Analysis

The ability to calculate averages based on restrictive, predefined boundaries is essential for granular data analysis. The `AVERAGEIFS` function in Excel provides the necessary mechanism to perform this highly specific aggregation, filtering a primary dataset using two or more simultaneous criteria. Whether analyzing scores, financial metrics, or physical measurements, this function allows users to isolate target populations and derive meaningful statistical insights that would be obscured by a simple overall average.

By understanding the required syntax—specifying the average range first, followed by sequential criteria range and criterion pairs—users can confidently apply complex filters. This capability moves beyond basic computation and elevates data handling to a professional level, enabling the creation of dynamic, responsive, and analytically robust spreadsheets that drive informed decision-making.

Note: You can find the complete documentation for the AVERAGEIFS function here.