Average Values in Excel When One Column Exceeds Another
Question details
Calculate the average of values in one column only when they exceed the corresponding values in another column.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet has values in columns A and B, and the user wants to compare them row by row before averaging qualifying values from column A.
- Observed behavior
- The desired result is an average of column A values for rows where the column A value is greater than the column B value.
Make sure the ranges being compared have the same number of rows and contain numeric values. Check your Excel version: FILTER is available in Microsoft 365 and Excel 2021 or later.
Use FILTER and AVERAGE in a Current Version of Excel
Use this formula in Microsoft 365 or Excel 2021 or later to average the qualifying values from column A.
Click an empty cell where you want the average to appear.
Type =AVERAGE(FILTER(A1:A10,A1:A10>B1:B10)) in the cell or formula bar, and press Enter.
Replace A1:A10 and B1:B10 in the formula with your actual matching ranges, keeping their row counts the same.

Use an Array Formula in Older Excel Versions
Use AVERAGE with IF when your Excel version does not support FILTER.
Average Values Across Columns in WPS Spreadsheets
Use an IF array formula with AVERAGE in WPS Spreadsheets to compare matching rows and average the qualifying values.
- 1. Open your worksheet: Open WPS Spreadsheets and use Open to select your workbook.
- 2. Choose a result cell: Click an empty cell outside the ranges you want to compare.
- 3. Enter the formula: Type =AVERAGE(IF(A1:A10>B1:B10,A1:A10)) in the cell or formula bar, replacing the example ranges with your own.
- 4. Confirm the calculation: Press Enter to calculate. If your WPS version requires array-formula confirmation on Windows, use Ctrl+Shift+Enter instead.

Frequently Asked Questions
Why can't AVERAGEIF directly compare two columns row by row?
AVERAGEIF is designed to apply a criterion to a range, rather than compare each cell with a corresponding cell in another range. Use FILTER with AVERAGE or an IF array formula for row-by-row comparisons.
How do I include rows where the two values are equal?
Change > to >= in the formula. For example, use =AVERAGE(FILTER(A1:A10,A1:A10>=B1:B10)) in a version of Excel that supports FILTER.
What happens if no values in column A are greater than column B?
The FILTER formula returns #CALC! because there are no matching rows. The IF array formula typically returns #DIV/0! because there are no numeric values to average.
Can I average column B instead of column A for matching rows?
Yes. In a version of Excel that supports FILTER, use =AVERAGE(FILTER(B1:B10,A1:A10>B1:B10)) to average column B values where the corresponding column A value is greater.




