Feb 22 2019 07:10 PM - edited Feb 22 2019 07:11 PM
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.
Feb 22 2019 10:06 PM
SolutionHi,
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
Feb 22 2019 10:13 PM
Feb 22 2019 11:55 PM
For newer versions of Excel try:
=AVERAGEIF(A1:A9,"<>",B1:B9)
or
=AVERAGEIFS(B1:B9,A1:A9,"<>")
Feb 22 2019 10:06 PM
SolutionHi,
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