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

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
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies