Averaging values between two dates/times

[ChrisM]

Hi All,

I have a spreadsheet that contains 4 columns. Column A = start date, Column
B = Start Time, Column C = Stop Date, Column D = Stop Time.

I have another spreadsheet which contains meteorological data for a given
time period. The data in this spreadsheet lists the Date in column A (eg.
Jan 1, 2005 to March 1, 2005). Columns C through AB list the time (0:00 to
23:00) corresponding to the date from column A. So the data looks like a
large matrix with dates listed in the rows of column A and 24 hour times
listed in columns C to AB.

Basically, I am looking for a function in Excel or a program in VBA to read
the start date and time and the stop date and time from the first
spreadsheet and then average the meteorological values that correspond to
that day/time interval on the second spreadsheet. Is this possible or not???

Any help would be greatly appreciated as I am very lost.

Thanks

Chris

 

[Peo Sjoblom]

Sure, a couple of ways

=AVERAGE(IF((Date_Range>=DATE(2005,10,1))*(Date_Range<=DATE(2005,10,31))*(Va
lue_Range<>""),Value_Range))

entered with ctrl + shift & enter

will average for October 2005

This part

Value_Range<>""

is to make sure there are no blanks in the value range, if there are no
blanks you can remove that part but if there are you need it or else blank
will be counted as zero and skew the average