Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.links
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.links
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Averaging a Value between Two Serial Dates | New Users to Excel | |||
Averaging Values Embraced In an Interval Between Two Dates | Links and Linking in Excel | |||
Averaging a Value Between Two Serial Dates | Excel Worksheet Functions | |||
Calculating number of days between two dates that fall between two other dates | Excel Discussion (Misc queries) | |||
Where is DateDiff function in Excel 2002 ? | Excel Worksheet Functions |