Forum Discussion

broccoli's avatar
broccoli
Copper Contributor
Feb 23, 2019
Solved

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 wi...
Formulas and Functions
  • Haytham Amairah's avatar
    Haytham Amairah
    Feb 23, 2019

    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

Detlef_Lewin's avatar
Detlef_Lewin
Silver Contributor
Feb 23, 2019

For newer versions of Excel try:

 

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

or

 

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

 

Resources