Forum Discussion
Solved
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?
=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.
4 Replies
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.
- Yup, also quite good. Introduced me to the FILTER function! Thanks, Peter!!
=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.
- 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!
