Problems with working out averages with large amounts of data
 

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

Problems with working out averages with large amounts of data
 

=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

Problems with working out averages with large amounts of data
 

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

Problems with working out averages with large amounts of data
 

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$3910 6)))

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

cheers

mindblank

"Sandy Mann" wrote:

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

Problems with working out averages with large amounts of data
 

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


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


"Mindblank" wrote in message
...
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$3910 6)))

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

cheers

mindblank

"Sandy Mann" wrote:

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

Problems with working out averages with large amounts of data
 

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" wrote:

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


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


"Mindblank" wrote in message
...
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$3910 6)))

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

cheers

mindblank

"Sandy Mann" wrote:

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

Problems with working out averages with large amounts of data
 

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


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


"Mindblank" wrote in message
...
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" wrote:

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


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


"Mindblank" wrote in message
...
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$3910 6)))

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

cheers

mindblank

"Sandy Mann" wrote:

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