SOLVED
Home

Averaging a set of values based off another set of values

broccoli
New Contributor

 

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.Screen Shot 2019-02-23 at 11.06.48 AM.png

I have been trying to use wildcards, but they only seem to work for text and not numbers.

Thanks in advance.

3 Replies
Solution

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)))

 AVERAGE with IF conditions.png

 

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
Exactly what I meant. Thank you :)

For newer versions of Excel try:

 

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

or

 

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

 

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
7 Replies