Problems with working out averages with large amounts of data

[Mindblank]

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

 

[Teethless mama]

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

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

 

[Sandy Mann]

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$40000)))

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Mindblank]

thanks for the suggestions, a slight point does it make a difference what
format the date/time is shown in column A? just now the format is dd/mm/yyyy
hh/mm.

also the range of cells i have is A2:A39106 AND B2:B39106 so would i
presumably the function would look like:

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

thanks again for all of your help, usually ok with basic functions but this
has thrown me

cheers

mindblank

 

[Sandy Mann]

The DATE() function calculates a number, the number of days since 1/1/1900,
(with an error because it assumes wrongly that 1900 was a leap year). It
follows therefore that the format makes no difference. I use dd/mm/yy and
Teethless mama's formula works for me with the INT() added.

I omitted to say in my post that D2 in my formula should contain the date
1/1/2007 and being as it uses SPMPRODUCT() does not have to be array
entered.

--
HTH

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Mindblank]

got it all working now, saved me and my boss a lot of time and energy.

thanks so much for your help

Mindblank

 

[Sandy Mann]

If I can talk for Teethless mama as well, you're very welcome. Thanks for
the feedback

--

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk