Averaging a Value between Two Serial Dates

Hi all,

I have two spreadsheets. The first spreadsheet contains two columns, one for
start date/time (in serial format) and the second for stop date/time (in
serial format).

The second spreadsheet contains two columns. One containing a serial
date/time and the other containing a meteorological value (eg. temperature).

I want Excel to read the start and stop date/time from the first spreadhseet
and then search the second spreadsheet for all temperature values which were
recorded during this time frame.

Lastly, I want Excel to average these values.

Is this possible using a single Excel function?? Or will I have to use many
nested functions?? Would it be easier to use VBA and if so where would I
start?? Any help would be greatly appreciated. Thanks

Chris

Assuming start time is in cell A1, end time is in cell B1, and your
meteorological data is on Sheet2 cells A1:B10, try entering this in cell C1

=AVERAGE(IF((Sheet2!$A$1:$A$10>A1)*(Sheet2!$A$1:$A$10<B1)=1,Sheet2!$B$1:$B$10,""))

Entered as an array formula (ie confirmed with Control+Shift+Enter). Then
copy it down.

"ChrisM" wrote:

> Hi all,
>
> I have two spreadsheets. The first spreadsheet contains two columns, one for
> start date/time (in serial format) and the second for stop date/time (in
> serial format).
>
> The second spreadsheet contains two columns. One containing a serial
> date/time and the other containing a meteorological value (eg. temperature).
>
> I want Excel to read the start and stop date/time from the first spreadhseet
> and then search the second spreadsheet for all temperature values which were
> recorded during this time frame.
>
> Lastly, I want Excel to average these values.
>
> Is this possible using a single Excel function?? Or will I have to use many
> nested functions?? Would it be easier to use VBA and if so where would I
> start?? Any help would be greatly appreciated. Thanks
>
> Chris
>
>
>