Minor point, I thnk that the OP has a Date and Time in the same cell so your
formula will need INT()'s:

=IF(ISERR(AVERAGE(IF(INT($A$2:$A$40000)=DATE(2007, 1,ROWS($1:1)),$B$2:$B$40000))),"",AVERAGE(IF(INT($ A$2:$A$40000)=DATE(2007,1,ROWS($1:1)),$B$2:$B$4000 0)))

Just another alternative:

=SUMPRODUCT(--(INT($A$2:$A$4000)=$D$2+ROW()-ROW($A$2))*$B$2:$B$4000)/MAX(SUMPRODUCT(--(INT($A$2:$A$4000)=$D$2+ROW()-ROW($A$2))),1)

(You beat me to it <g) I don't know how having two SUMPRODUCTS() will
affect the efficiency

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Teethless mama" wrote in message
...
=IF(ISERR(AVERAGE(IF($A$2:$A$40000=DATE(2007,1,ROW S($1:1)),$B$2:$B$40000))),"",AVERAGE(IF($A$2:$A$40 000=DATE(2007,1,ROWS($1:1)),$B$2:$B$40000)))

ctrl+shift+enter, not just enter
copy down as far as needed


indblank" wrote:

I have a problem with working out averages that hopefully someone one
here
can help with.

Please bear with me as i try to explain. Here goes: I currently have a
worksheet that contains two columns, column A has the date and time for
data
entries over a year. In each day there are a varying amount of data
entries.
Column B is the data entry for each given time.

for example

A1 = Date/Time B1 = data
A2 = 24/01/2007 17:15 B2 = 1.5
A3 = 24/01/2007 18:00 B3 = 1.2
A26 = 28/01/2007 19:00 B26 =1.8
A245=04/06/2007 09:15 B245 = 0.6
and so on....

Now the problem is that i have over a full years worth of data in this
format and each day has varying amounts of data entries ranging from 10
entries to over 30. The way i started to address this problem was to set
up
an average function of all the results for each day but as i have found
out
the function cannot be copied as each day has a different amount of
readings
and therefore the data series for each function changes per day. Doing it
this way means creating 365+ functions and then going through each
forumla to
check that the data series covers the correct data entries. Due to the
fact i
have over 39000 data entries this takes hours and i hope there is someone
out
there who can suggest a quicker way of doing this?

is there any way i can set up a function to isolate entries per day and
then
average them?

many thanks in advance to anyone who can help

Mindblank