Creating daily average from multiple values per day

[ser1609]

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

 

[John C]

=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

 

[ser1609]

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]

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.

 

[ShaneDevenshire]

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.