Averaging Values Embraced In an Interval Between Two Dates
Hi all,
My spreadsheet contains a matrix of data in four columns by approximately 600 rows. The data is structured as follows: column A = start date column B = start time column c = stop date column d = stop time Each one of the 600 hundred rows contains a different set of start/stop information. I have another spreadsheet which lists meteorological information over this same time period. The data in this spreadheet is structured as follows: column A = date columns c through AB = time (0:00 - 24:00) Basically, I am looking for a program to read the start and stop information from columns A, B, C and D of spreadsheet 1 and output an average of the meteorological values (eg. temperature) corresponding to this time interval. Is there a function in Excel or some code in Visual Basic that can handle this task for me?? I am thinking it may have to include the Datediff function but am not sure?? Any suggestions would be greatly appreciated. Chris |
Averaging Values Embraced In an Interval Between Two Dates
Let's try building a formula
If we could identify the relevant cells then the average would be =SUM(TheCells)/COUNT(TheCells) Because the start time and end time can be in the middle of a row it is a bit complicated to identify the cells. I would go for =(SUM(TheDaysCells)-SUM(BeforeStartCells)-SUM(AfterEndCells))/COUNT(The Cells) where TheDayCells are all the readings for the days concerned from StartDate to EndDate inclusive, BeforeStartCells are those cells in the row for StartDate which are before the start time AfterEndCells are those cells in the row for EndDate which are after then end time. I am assuming that the meteo. data has one row per date and that the dates run sequentially. Name the first date in this table "FirstMet" If you also name the columns StartDate, StartTime, etc. it will help make the formula comprehensible. StartDate-FirstMet is the row offset from the FirstMet cell to where the data starts, and StopDate-StartDate+1 would be the number of rows involved. So, TheDayCells would be OFFSET(FirstMet,StartDate-FirstMet,2,StopDate-StartDate+1,24) BeforeStartCells would number INT(StartTime*24)+1 (so that a StartTime of 8:45 would ignore the first 9 times, from 0:00 to 8:00) so BeforeStartCells would be that many cells starting in column 3: OFFSET(FirstMet,StartDate-FirstMet,2,1,INT(StartTime*24)+1) AfterEndCells would start at column INT(StopTime*24)+4 (so StopTime 02:30 would start ignoring the cells from column 6) running to the end of the 24 cells in the row for the StopDate, so AfterEndCells would be OFFSET(FirstMet,StopDate-FirstMet,INT(StopTime*24)+3,1,23-INT(StopTime* 24)) So the total of the meteo readings would be =(SUM(OFFSET(FirstMet,StartDate-FirstMet,2,StopDate-StartDate+1,24))-SU M(OFFSET(FirstMet,StartDate-FirstMet,2,1,INT(StartTime*24)+1))-SUM(OFFS ET(FirstMet,StopDate-FirstMet,INT(StopTime*24)+3,1,23-INT(StopTime*24)) )) and so the average will be =(SUM(OFFSET(FirstMet,StartDate-FirstMet,2,StopDate-StartDate+1,24))-SU M(OFFSET(FirstMet,StartDate-FirstMet,2,1,INT(StartTime*24)+1))-SUM(OFFS ET(FirstMet,StopDate-FirstMet,INT(StopTime*24)+3,1,23-INT(StopTime*24)) ))/(COUNT(OFFSET(FirstMet,StartDate-FirstMet,2,StopDate-StartDate+1,24) )-COUNT(OFFSET(FirstMet,StartDate-FirstMet,2,1,INT(StartTime*24)+1))-CO UNT(OFFSET(FirstMet,StopDate-FirstMet,INT(StopTime*24)+3,1,23-INT(StopT ime*24)))) I bet you are glad you asked! Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
All times are GMT +1. The time now is 04:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com