|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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
|
|
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
|
|
|