Find and average

[Debi B. in Milwaukee]

Following is the data I have. I would like a formula to find all the items
in Column B that is between 0300 and 0700 then using the data from column D,
average them together - putting in Column E.
I need to do this for 5 other ranges, ex. 1500 - 1900; 1901 - 2300, etc.

This is what I did, but is not working:
=AVERAGE(IF(B2:B14>1100,IFB2:B14<1500,(D214))) -tried the array also: CSE.

To make it more difficult, I need to do this for a different range of
numbers each day and the quantity in the range varies.

Please help, this is so frustrating.

Col B Col C Col D Col E
0200 0202 2
0215 0220 5
0853 0900 7
1146 1212 26
1147 1212 25
1814 2025 131
1815 1842 27
1925 1932 7
2025 2120 55
2100 2125 25

 

[Shane Devenshire]

Hi,

=AVERAGE(IF((B2:B14>1100)*(B2:B14<1500),D214,""))

Array entered Shift+Ctrl+Enter




If this helps, please click the Yes button.

Cheers,
Shane Devenshire

 

[Shane Devenshire]

Sorry, forgot

In 2007

=AVERAGEIFS(D214,B2:B14,">1100",B2:B14,"<1500")