Averaging Value between Two Serial Dates "ChrisM" <Christopher.Meaney[ at ]ec.gc.ca> 15.11.2005 20:04:22 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

Re: Averaging Value between Two Serial Dates Bill Manville <Bill-Manville[ at ]msn.com> 16.11.2005 00:25:59 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