Function Problems

Average Values in Excel When One Column Exceeds Another

Adam DavisAdam Davis Nov 18, 2026 869 views

Question details

Calculate the average of values in one column only when they exceed the corresponding values in another column.

Average Values in Excel When One Column Exceeds Another
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.
Before you start

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.

Solution 1Recommended

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.

1
Select a result cell

Click an empty cell where you want the average to appear.

2
Enter the formula

Type =AVERAGE(FILTER(A1:A10,A1:A10>B1:B10)) in the cell or formula bar, and press Enter.

3
Adjust the ranges

Replace A1:A10 and B1:B10 in the formula with your actual matching ranges, keeping their row counts the same.

Use FILTER and AVERAGE in a Current Version of Excel
If no rows qualify: FILTER returns a #CALC! error when it finds no matching rows. Check the source values or use IFERROR around the formula if you prefer a custom message.
Calculate in WPS Spreadsheets

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. 1. Open your worksheet: Open WPS Spreadsheets and use Open to select your workbook.
  2. 2. Choose a result cell: Click an empty cell outside the ranges you want to compare.
  3. 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. 4. Confirm the calculation: Press Enter to calculate. If your WPS version requires array-formula confirmation on Windows, use Ctrl+Shift+Enter instead.
Compare values in two columns row by row before averaging.Open and edit Microsoft Excel XLS and XLSX files.Work with familiar spreadsheet formulas and tools.Use WPS Office for free.
microsoft office alternative - wps office

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.