I've got a dataset with measurements at different time intervals per day.
Most are on the hour, so I could just create daily average for each 24
entries, but many have more (or less) per day. How do I create a daily
average of each group of numbers per day? The data is formatted as column
A=date, column B=amount

=IF(COUNTIF(A1:A8,A11)=0,"No Data",AVERAGE(IF(A1:A8=A11,B1:B8)))
A1:A8 is the dates (note, more work is needed if this is actually date and
time, but if it is just a date, this will work.
A11 is the date you are wanting to average
B1:B8 is the amount
This is an **array** formula, and must be entered with CTRL+Shift+Enter
--
** John C **

"ser1609" wrote:

I've got a dataset with measurements at different time intervals per day.
Most are on the hour, so I could just create daily average for each 24
entries, but many have more (or less) per day. How do I create a daily
average of each group of numbers per day? The data is formatted as column
A=date, column B=amount

Thanks John, it seems close (the first date was correct), but this dataset is
over 50,000 entries with thousands of dates. Can this be modified to
calculate the daily average of every distinct date in the set?

"John C" wrote:

=IF(COUNTIF(A1:A8,A11)=0,"No Data",AVERAGE(IF(A1:A8=A11,B1:B8)))
A1:A8 is the dates (note, more work is needed if this is actually date and
time, but if it is just a date, this will work.
A11 is the date you are wanting to average
B1:B8 is the amount
This is an **array** formula, and must be entered with CTRL+Shift+Enter
--
** John C **

"ser1609" wrote:

I've got a dataset with measurements at different time intervals per day.
Most are on the hour, so I could just create daily average for each 24
entries, but many have more (or less) per day. How do I create a daily
average of each group of numbers per day? The data is formatted as column
A=date, column B=amount

Where do you want the data? This works just fine for me.
=IF(COUNTIF($A$2:$A$50000,A2)=0,"No
Data",AVERAGE(IF($A$2:$A$50000=A2,$B$2:$B$50000)))
Again, entered as an **array** formula. You could even modify it so that the
average ONLY appears once, the first occurance of the date, the last
occurance of the date, etc.
--
** John C **

"ser1609" wrote:

Thanks John, it seems close (the first date was correct), but this dataset is
over 50,000 entries with thousands of dates. Can this be modified to
calculate the daily average of every distinct date in the set?

"John C" wrote:

=IF(COUNTIF(A1:A8,A11)=0,"No Data",AVERAGE(IF(A1:A8=A11,B1:B8)))
A1:A8 is the dates (note, more work is needed if this is actually date and
time, but if it is just a date, this will work.
A11 is the date you are wanting to average
B1:B8 is the amount
This is an **array** formula, and must be entered with CTRL+Shift+Enter
--
** John C **

"ser1609" wrote:

I've got a dataset with measurements at different time intervals per day.
Most are on the hour, so I could just create daily average for each 24
entries, but many have more (or less) per day. How do I create a daily
average of each group of numbers per day? The data is formatted as column
A=date, column B=amount

Hi,

Here are three different ways

1. in 2007 =AVERAGEIF(A1:A50000,C1,B1:B50000)
2. =SUMIF(A1:A50000,C1,B1:B50000)/COUNTIF(A1:A50000,C1)
3. =AVERAGE(IF(A1:A50000=C1,B1:B50000))

The second and third ones work in all versions. The third one requieres
array entry. If you are going to copy you should make the references
absolute as needed.

--
Thanks,
Shane Devenshire


"ser1609" wrote:

I've got a dataset with measurements at different time intervals per day.
Most are on the hour, so I could just create daily average for each 24
entries, but many have more (or less) per day. How do I create a daily
average of each group of numbers per day? The data is formatted as column
A=date, column B=amount