I need to find the Average from Column A - but Reference Column B

[BAM718]

Column A has a Range of Dollar Values ($0 - $100,000). Column B has a Range
of numbers from 0 to 100.

I want to return an average of the Dollar Values in column A but only using
the Dollar Values that have a corresponding number in column B between the
range of 0 to 10.

 

[JulieD]

Hi

one method
=SUMIF(B2:B27,"<="&10,A2:A27)/COUNTIF(B2:B27,"<="&10)

as long as you don't have any negative numbers in B

Cheers
JulieD

 

[Jason Morin]

You could use the array formula:

=AVERAGE(IF((B1:B10>=0)*(B1:B10<=10),A1:A10))

In order to work, you must press ctrl + shift + enter
after copying in the formula. An alternative would be:

=SUM(A:A,-SUMIF(B:B,{"<0",">10"},A:A))/SUM(COUNT(B:B),-
COUNTIF(B:B,{"<0",">10"}))

The advantage of the 2nd formula is that it is *not* an
array formula and you can reference entire columns.

HTH
Jason
Atlanta, GA