AVERAGEIF criterion is itself a conditional

[sroper2001]

I suspect this has been answered before, though  I searched and couldn't find it.   I want to find the average value of a column of numbers, but ONLY of the number exceeds the value of the cell in an adjacent column.  For example, from the following table...

	A	B
1	1.25	0.38
2	2.24	0.43
3	1.68	0.57
4	1.75	0.48
5	1.88	0.62
6	1.42	0.32
7	0.38	0.51
8	0.49	0.61
9	0.12	0.18
10	0.79	0.30

...I want to find the average of the values in column A if and only if that value exceeds its adjacent value in column B.    These A values are in bold font above.  The excel function =AVERAGEIF(A1:A10,">"&B1) isn't correct, of course.  But what is the correct criterion for such a case?

[#2 · Accepted solution]

@sroper2001 

=AVERAGE(IF(A1:A10>B1:B10,A1:A10))

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or Excel 2021.

 

[#3]

Yes! Perfect!! A million thanks! I assume will also work with SUMIF when I want to sum those numbers instead of averaging them. You've made my day!

[#4]

@sroper2001 

Currently, the best formula would be

= AVERAGE(
    FILTER(A, A>B)
  )

where A and B are names given to your variables.

= AVERAGE(
    IF(A>B,A)
  )

would also work.

[#5]

Yup, also quite good. Introduced me to the FILTER function! Thanks, Peter!!