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:B141100,IFB2:B14<1500,(D2:D14))) -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

Hi,

=AVERAGE(IF((B2:B141100)*(B2:B14<1500),D2:D14,"") )

Array entered Shift+Ctrl+Enter




If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Debi B. in Milwaukee" wrote:

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:B141100,IFB2:B14<1500,(D2:D14))) -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

Sorry, forgot

In 2007

=AVERAGEIFS(D2:D14,B2:B14,"1100",B2:B14,"<1500")

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Debi B. in Milwaukee" wrote:

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:B141100,IFB2:B14<1500,(D2:D14))) -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