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