Data Selection

[S. Casey]

Can anyone help me with this...

I have a yearly set of hourly data. I want to be able to take daily
averages of the data. I don't really want to have to type in the =average()
and select each days data for the entire year. Using the fill command
doesn't work because it just moves the data set down one cell and averages.
Is there an easy way of doing this that doesn't require hours of work?

 

[Jacob Skaria]

With date in colA the below formula will give you the average of the data in
colB based on the date mentioned in E1 . Please note that this is an array
formula; press CTRL+SHIFT+ENTER to enter the formula. If successful in
'Formula Bar' you can notice the curly braces at both ends like "{=<formula>}"

=AVERAGE(IF((A1:A1000=E1),B1:B1000))

If this post helps click Yes

 

[S. Casey]

Thank you Jacob. This worked perfectly. The only down fall was that the
dates that accompanied the data also had the time of day in the cells, so I
had to reinput all the dates again without the times. Thanks again.

 

[David Biddulph]

You didn't need to put the data in again without the times.
What's wrong with the array formula
=AVERAGE(IF(INT(A1:A1000)=E1,B1:B1000)) ?

 

[S. Casey]

The formula worked quite well. The issue was that the first column that
contained the date the data was taken also contained the time of day. Which
is fine, but when your search criteria is for 1/1/2007 it didn't find any
cells for that date because the addition of the time within the cell was
making the search come back "False". Is there a way that you can bypass this
problem? Otherwise, this is why I had to reinput the dates. As well the
times of day that the data were taken kept increasing by 1 millisecond every
few days so even if I created a search criteria to include the time of day it
seemed like this would have not been worth it and just reinputting the dates
was easier.

 

[David Biddulph]

Perhaps you didn't read my message to which you were replying? My formula
was designed to cope with date and time in column A; that's why the INT
function is in the formula (to pull out the date without the time).

Or perhaps you don't actually have date and time which Excel recognised, but
instead you've got text?

What do =ISNUMBER(A2) and =ISTEXT(A2) show, if you have one of your date/
time combinations in A2?

 

[S. Casey]

I'm sorry about that, I read the formula too quickly and missed the INT()
part of it. That would work even better, thank you very much.