Need help! Average based upon criteria

[spiegea]

I have two different columns as shown below:

Column A Column B
$1,200,000 0.45
$2,500,000 0.35
$1,350,000 0.43
$4,500,000 0.25
$5,250,000 0.20

I am trying to find the average column B figure limited to greater than $1,000,000 and less than $1,499,999. I only need it to calculate those figures. I will then modify the formula to read the average column B figure for $1,500,000 to $1,999,999 etc... Thanks in advance!

 

[Ron Coderre]

With
A1:B5 containing
$1,200,000 0.45
$2,500,000 0.35
$1,350,000 0.43
$4,500,000 0.25
$5,250,000 0.20

These formulas return the average of Col_B items where the
corresponding Col_A value is >=1,000,000 and <1,500,000

XL2007:
=AVERAGEIFS(B1:B5,A1:A5,">="&1000000,A1:A5,"<"&1500000)
 
XL2007 and all prior:
Array formula, committed with CTRL+SHIFT+ENTER, instead of just ENTER:
=AVERAGE(IF(FLOOR(A1:A5,500000)=1000000,B1:B5))
 
Regular formula, committed with just ENTER:
=SUMPRODUCT(--(FLOOR(A1:A5,500000)=1000000),B1:B5)/
SUMPRODUCT(--(FLOOR(A1:A5,500000)=1000000))

With the sample data, each of those formulas returns: 0.44

Does that help?

 

[spiegea]

Ron,

Can't thank you enough! The formula is working great! However, if I wanted to add one more set of criteria to the equation, for example:

A1:C5 containing
$1,200,000 0.45 20
$2,500,000 0.35 55
$1,350,000 0.43 20
$4,500,000 0.25 31
$5,250,000 0.20 30

If now column C contains values as shown above, and I want to do the same thing as before but have the formula only calculate values with a 20 behind it. What would the formula look like then?

Thanks once again in advance!

 

[Ron Coderre]

Ya, know....You *could* have mentioned which of the three formulas I posted was the one you're using.

Anyway...I adjusted all three. Now each formula only averages
Col_B values
where the corresponding
Col_A value is >=1,000,000 and <1,500,000
and
the Col_C value is 20
:

XL2007:
=AVERAGEIFS(B1:B5,A1:A5,">="&1000000,A1:A5,"<"&1500000,C1:C5,"="&20)
 
XL2007 and all prior:
Array formula, committed with CTRL+SHIFT+ENTER, instead of just ENTER:
=AVERAGE(IF((FLOOR(A1:A5,500000)=1000000)*(C1:C5=20),B1:B5))
 
Regular formula, committed with just ENTER:
=SUMPRODUCT(--(FLOOR(A1:A5,500000)=1000000),--(C1:C5=20),B1:B5)/
SUMPRODUCT(--(FLOOR(A1:A5,500000)=1000000),--(C1:C5=20))

Does that help?