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

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.

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


"BAM718" wrote in message
...
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.

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

-----Original Message-----
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.
.