Jim said:
Isn't this the job of computers and Excel anyway?
Generally not a good idea to "bite the hand that feeds you" -- get
snippity with people who are struggling to help you. Yes, computers can
solve almost any problem. But the solution is only as good as the problem
description. Billy was saying that your original problem description was
not sufficiently clear. I agree. Concrete examples always help clarify
the problem, as you finally did.
You are talking about adjusting for clock skew -- the fact that time on
the Hobo logger might not stay in sync with system time over a long period
of time. That is what I thought you might be talking about; but I was not
sure.
Label the first log entry as "firstLog", the last log entry as "lastLog",
and the corresponding system time as "lastActual".
Then the average skew per day (and any fraction thereof) is:
=(lastActual - lastLog) / (lastLog - firstLog)
Caveat: Format that as Number with 6 decimal places. See below if you
want to format as "[h]:mm:ss.000".
Label that cell as "skew".
Then if the "firstLog" is in A1, put the following formula into B1 and
copy down:
=A1 + skew * (A1 - firstLog)
Caveat: If you actually name the cells as I have suggested, Excel might
"autocorrect" A1 to firstLog. If that happens, manually edit the formula
in the Formula Bar to use A1.
PS: If you want to format skew as "[h]:mm:ss.000", there is an issue with
negative skew. Instead of changing the date system to 1904, as some
people suggest, I suggest that you put the following formula into another
cell:
=IF(skew < 0, "-", "") & TEXT(ABS(skew), "[h]:mm:ss.000")
and format with Right horizontal alignment. Do something similar if you
want format the total skew for the last log entry (i.e =lastActual -
lastLog). Note that these cells are not numeric; so they cannot be used
in arithmetic formulas such as the "skew" formula above.
PPS: You could compute the skew per log entry instead. IMHO, the
advantage of computing the skew per (fractional) day as I did is: it
compensates for if the logger entry times are not exactly X
hours/minutes/seconds apart. But if that's not an issue, then:
skew: =(lastActual - lastLog) / (COUNT(firstLog:lastLog) - 1)
B1: =A1 + skew * (ROW(A1) - ROW(firstLog))
----- original message -----
Jim said:
Sure, here is info on one real life example:
From 11/14/08 to 4/5/09 there were 1704 samples recorded (every 2 hours
in
this case). That's 3408 hours of recording time. The logger records the
date and time of each record. The time of the first sample on offload of
the data, was the same as actual time, because I set the time accurately
on
launch.
The time of sample #1704 was off by 22 minutes 10 seconds (logger time
compared to actual time).
Obviously, the beginning sample times were close to the actual time, but
as
time went on, the sample time was further from the actual time.
So my problem is to use a formula that will compute and distribute the
time
difference error across the entire 1704 sample, 4 1/2 month logging
period......and fill the cells in a column with the actual time of each
sample. I could probably do this by hand, but for multiple spreadsheets
of
varying numbers of samples, using excel, rather than my pencil would be a
better solution. Isn't this the job of computers and Excel anyway?
Thanks
Jim
Billy Liddel said:
Jim
Pete gave a general answer because the question was rather vague.
Perhaps
you can post concrete examples of the data?
Peter
:
Well, o.k., but can you provide any help with my time adjustment per/sample
problem?
Jim
Times are stored in Excel as fractions of a 24-hour day. Thus, 12
hours is actually 0.5, 6 hours is 0.25 etc. If you want to add x hours
onto a time in A1, then you can do this:
=A1 + x/24
You need to format the cell as a time (maybe [h]:mm:ss), but be aware
that if the addition takes the time beyond midnight then the time will
not wrap at 24 hours with this setting.
Hope this helps.
Pete
I am trying to figure out how to develop a cell formula that will adjust
the
time on entered data.
I use Hobo data loggers to collect temperatures every 4 hours from October
to May. I set the time on the logger at launch according to a WWVB radio
clock, hopefully to the nearest second or so.
On recovery, I record the actual WWVB time as compared to the logger time,
and not unexpectidly, there is always some descepency. The data,
which
includes the date and time of each record is loaded into an Excel
spreadsheet.
I would like to be able to create a column of cells beside the loggers'
time, that I will call actual time, based on the time difference at
recovery, and the logging period since launch. I can't seem to get
my head
to work this out.
Any ideas on the cell formula that will do this?
Thanks
Jim
