=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