 • 659K Members
• 4,999 Online
• 809K Conversations
SOLVED

Highlighted
New Contributor

# 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.

3 Replies
Highlighted
Solution

# Re: Averaging a set of values based off another set of values

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

Highlighted

# Re: Averaging a set of values based off another set of values

Exactly what I meant. Thank you :)
Highlighted

# Re: Averaging a set of values based off another set of values

For newer versions of Excel try:

`=AVERAGEIF(A1:A9,"<>",B1:B9)`

or

`=AVERAGEIFS(B1:B9,A1:A9,"<>")`