Average if....between range

[Guest]

Been looking through threads but can't match this one!

ColA ColB
400 1.266%
132 17.857%
1,348 3.932%
1,177 3.155%
1,131 2.818%
14,900 0.000%
601 0.661%
3,102 26.975%
33,228 2.751%
1,498 23.297%

How do I calculate the average in ColB if ColA >500 and <1000 (between 500
& 1000)

 

[Guest]

Try this,

=SUMPRODUCT((A1:A100>500)*(A1:A100<1000)*(B1:B100))

Mike

 

[Guest]

Given the last post never answered your question, I'll try again!!

=SUMPRODUCT((A1:A100>500)*(A1:A100<1000)*(B1:B100))/(COUNTIF(A1:A11,"<1000")-COUNTIF(A1:A11,"<500"))

Mike

 

[Guest]

and neither does that because the ranges are incorrect. lead me to a darkened
room to lie down

=SUMPRODUCT((A1:A100>500)*(A1:A100<1000)*(B1:B100))/(COUNTIF(A1:A100,"<1000")-COUNTIF(A1:A100,"<500"))

 

[Guest]

try:

=AVERAGE(IF($A$1:$A$10>500,IF($A$1:$A$10<1000,$B$1:$B$10)))

Enter with Ctrl+shift+Enter