SOLVED

New Contributor

AVERAGEIF criterion is itself a conditional

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?

4 Replies
best response confirmed by sroper2001 (New Contributor)
Solution

Re: AVERAGEIF criterion is itself a conditional

``=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.

Re: AVERAGEIF criterion is itself a conditional

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!

Re: AVERAGEIF criterion is itself a conditional

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.

Re: AVERAGEIF criterion is itself a conditional

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