"Jennifer" wrote:
.. I need a different data point for each hour of each day,
not one average for all 6am times,
but one 6am average for 2/1 and for 2/2, 2/3, etc......
My data has over 2000 points ..

Ok, now that you have clarified it as above, here's my thoughts on your
issue ...

First, let's set the calc mode to manual mode since it's going to get quite
calc intensive. Click ToolsOptionsCalculation tabCheck "Manual"OK.

Assume data in cols A and B, from row1 down (as before)
In C1:
=DATE(YEAR(A$1),MONTH(A$1),DAY(A$1)+INT((ROWS($1:1 )-1)/24))
In D1:
=MOD(ROWS($1:1)-1,24)
Select C1:D1, copy down by 336 rows (14 days x 24 hourly band per day = 336
rows) to D336. Press F9 to recalc. Cols C and D will create all the labels to
denote the date/hour for each day based on your initial date data in A1. Each
date in col C will have all the 24 hourly bands, cycling 0-23 in col D.

Now place in E1, array-enter by pressing CTRL+SHIFT+ENTER:
=AVERAGE(IF((TEXT(A$1:A$3000,"dd-mmm-yy")=TEXT(C1,"dd-mmm-yy"))*(HOUR(A$1:A$3000)=D1)*(A$1:A$3000<""),B$1:B $3000))
Copy E1 down to E336. Again press F9 to recalc. When recalc is complete, you
should have the exact results that you seek in col E for each of the 14 days'
hourly bands.

Col E will return #DIV/0! where data in col A is missing/incomplete for the
particular date/hour. To suppress #DIV/0! errors, you could use an
IF(ISERROR(...) trap for the above (as before), viz, use this instead in E1,
array-entered then copied down:
=IF(ISERROR(AVERAGE(IF((TEXT(A$1:A$3000,"dd-mmm-yy")=TEXT(C1,"dd-mmm-yy"))*(HOUR(A$1:A$3000)=D1)*(A$1:A$3000<""),B$1:B $3000))),"",AVERAGE(IF((TEXT(A$1:A$3000,"dd-mmm-yy")=TEXT(C1,"dd-mmm-yy"))*(HOUR(A$1:A$3000)=D1)*(A$1:A$3000<""),B$1:B $3000)))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---