• 412K Members
• 7,484 Online
• 468K Conversations
SOLVED

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

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

Exactly what I meant. Thank you :)

# 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,"<>")`

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