Averaging 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

Averaging Value between Two Serial Dates
 

Ah, so after I spent 30 minutes developing a formula for your previous
data structure you have decided to change the structure. This is much
easier to work with...

Assuming the column of meteo date/time is named MeteoDates and the
readings are named MeteoVal we can use SUMIF to get the sum of the
readings at times after the StartDateTime and subtract those which are
after the StopDateTime:
=(SUMIF(MeteoDates,StartDateTime,MeteoVal)-SUMIF(MeteoDates,StopDateT
ime,MeteoVal))/(COUNTIF(MeteoDates,StartDateTime)-COUNTIF(MeteoDates,
StopDateTime))

By the way, this question would be better suited to the
WorksheetFunctions newsgroup

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup