ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Average if....between range (https://www.excelbanter.com/excel-worksheet-functions/153382-average-if-between-range.html)

Saintsman
Average if....between range
 
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)

Mike H
Average if....between range
 
Try this,

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

Mike

"Saintsman" wrote:

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)

Mike H
Average if....between range
 
Given the last post never answered your question, I'll try again!!

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

Mike

"Saintsman" wrote:

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)

Mike H
Average if....between range
 
and neither does that because the ranges are incorrect. lead me to a darkened
room to lie down

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

"Saintsman" wrote:

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)

Toppers
Average if....between range
 
try:

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

Enter with Ctrl+shift+Enter

"Saintsman" wrote:

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)


All times are GMT +1. The time now is 07:18 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com