Forum Discussion
Solved
Averaging a set of values based off another set of values
I have a set of values in one column and another in the next column over (see image). I want to average the values in column B when there is a number in column A.
I have been trying to use wildcards, but they only seem to work for text and not numbers.
Thanks in advance.
Hi,
Do you want to calculate the average for only the number in column B that has a corresponding number in column A?
If this is what you want, please try this:
=AVERAGE(IF(IF(ISNUMBER(A1:A9),B1:B9,0)=0,"",IF(ISNUMBER(A1:A9),B1:B9,0)))
Please note that you need to press Ctrl+Shift+Enter to enter this formula not only Enter because it's an array formula.
Hope that helps
For newer versions of Excel try:
=AVERAGEIF(A1:A9,"<>",B1:B9)
or
=AVERAGEIFS(B1:B9,A1:A9,"<>")
Hi,
Do you want to calculate the average for only the number in column B that has a corresponding number in column A?
If this is what you want, please try this:
=AVERAGE(IF(IF(ISNUMBER(A1:A9),B1:B9,0)=0,"",IF(ISNUMBER(A1:A9),B1:B9,0)))
Please note that you need to press Ctrl+Shift+Enter to enter this formula not only Enter because it's an array formula.
Hope that helps
- Exactly what I meant. Thank you :)
