![[TEST] Document Foundation Community](https://communitytest.documentfoundation.org/images/discourse-logo-sketch.png){kind=logo}
Hi,
Imagine you've collected samples from a data acquisition device, and
know the time you started taking samples (12:04 pm), and when you stopped
(12:36 pm). You'd end up with two columns of data like this:
12.7 12:04 pm
8.9
3.5
2.1
7.2
6.1 12:36 pm
My question is, is there a way to generate the intermediate time values in
the second column?
No - simply because the samples may well have been taken at irregular intervals!
If you mean that we are to suppose that the intervals were regular, let's suppose that these values are in cells A1:B6. In B2, enter:
=B1+(B$6-B$1)/5
and fill this down to B5. Note that your times may need to be proper times (numbers formatted as times) and not text values for this to work. If the number of samples is itself to be variable, you could develop a more complicated formula that would count the items and not have the "5" hard-coded in it.
I trust this helps.
Brian Barker
If on windows, suggest looking at http://realterm.sourceforge.net/
Linux: Realterm has been tested with Wine 1.0, Opensuse 11.0, Realterm 2.0.0.62.
A cursory test with Com1, running as root, was successful.
Realterm is a terminal program specially designed for capturing, controlling and debugging binary and other difficult data streams.
See command :
TIMESTAMP
0-4
During capture, Prepends a timestamp to each line. Only works in text files with EOL
Good luck
Thanks Brian for the reply,
The data acquisition
device samples at "approximately 1 sample per second" not a nice fixed
sample rate. And unfortunately time stamps the first and last sample only.
If the sample values are in column A1, then I'd like an "approximate"
time-line in the cells between the start logging and stop logging times in
column B2.
What I'm after is to look at my notes taken at different times (the time
noted) during logging, and identify the approximate sample that corresponds
to them.
I'm afraid I don't understand your example as it looks like you have the
samples in a row not a column.
The data acquisition device samples at "approximately 1 sample per second" not a nice fixed sample rate.
That's odd, as your example had them over six minutes apart.
If the sample values are in column A1, ...
Hold on: A1 is a cell, not a column. Columns are lettered A, B, and so on. You are using a spreadsheet document, not a table in a text document, aren't you? Spreadsheets give you more flexible calculation facilities.
then I'd like an "approximate" time-line in the cells between the start logging and stop logging times ...
That was clear in your earlier message.
... in column B2.
B2 is a cell; B is a column. Your time values are in column B - with the six values being in cells B1 to B6 (and the data in A1 to A6).
What I'm after is to look at my notes taken at different times (the time noted) during logging, and identify the approximate sample that corresponds to them.
Good.
I'm afraid I don't understand your example as it looks like you have the samples in a row not a column.
That's not so, and I don't see how it looks so (providing you are using a spreadsheet). I put the values you offered into the top left corner of a spreadsheet, so your data was in A1 to A6 and the two time values in B1 and B6. I gave you a formula for B2 that would fill down to B5 to give the results you need.
Brian Barker
This is what Brian is trying to explain...
You've got 6 samples. The first one taken at 12:04PM, the last one at 12:36PM.
The total elapsed time is 12:36PM - 12:04PM = 22 minutes.
22 divided by 5 (always the total number of readings minus 1) = 6.4 minutes or 6m and 24s per interval.
Adding 6:24 to 12:04PM gives you the time of the second reading, 12:10:24PM. Enter that value into cell B2.
Now we make the computer do the work for all remaining readings. Follow these instructions carefully:
Highlight cells B1 and B2. Hover the mouse over the little black square in the lower right hand corner of the the highlighted cells until it turns into a cross (+). Now hold the left mouse button down and drag the mouse downward. As you drag the mouse each cell will fill with the correct time stamp.
You will end up with this:
12.7 12:04:00 PM
8.9 12:10:24 PM
3.5 12:16:48 PM
2.1 12:23:12 PM
7.2 12:29:36 PM
6.1 12:36:00 PM
If you want to use 1S as your interval the just enter 12:04:01PM in B2 and drag the mouse as explained above.
Hope that helps...
-Bill
This is what Brian is trying to explain...
I was doing more than try! And no: what you suggest here is not what I was saying.
You've got 6 samples. The first one taken at 12:04PM, the last one at 12:36PM. The total elapsed time is 12:36PM - 12:04PM = 22 minutes.
Er that's *thirty*-two minutes , not twenty-two, by my arithmetic.
22 divided by 5 (always the total number of readings minus 1) = 6.4 minutes or 6m and 24s per interval.
Good: but only as long as it's 32, not 22, we are dividing.
Adding 6:24 to 12:04PM gives you the time of the second reading, 12:10:24PM. Enter that value into cell B2. Now we make the computer do the work for all remaining readings.
Why do a manual calculation for the first value (where you could easily make mistakes - oh, you did!) and only then "make the computer do the work"? The computer can do all the work.
Follow these instructions carefully:
Why not follow my instructions instead (which are quite different), where a formula does all the calculation for you, not just the last bit?
But chacun à son goût!
Brian Barker
This is what Brian is trying to explain...
I was doing more than try! And no: what you suggest here is not what I was saying.
You've got 6 samples. The first one taken at 12:04PM, the last one at 12:36PM. The total elapsed time is 12:36PM - 12:04PM = 22 minutes.
Er that's *thirty*-two minutes , not twenty-two, by my arithmetic.
32 minutes, yes! That's what happens when I do math so early in the morning.
Follow these instructions carefully:
Why not follow my instructions instead (which are quite different), where a formula does all the calculation for you, not just the last bit?
Since the OP was confused by your instructions (indicating his level of inexperience with spreadsheets) and you did not explain how to fill the column or the purpose of $, I offered what I thought would be easier for someone at his level to understand. It is not necessarily the best approach, but something I thought would solve his problem and he would understand. It also demonstrates the ability of a spreadsheet to generate data based on a pattern in selected cells. This can be a time saver and is not something everyone is aware of.
My solution is not intended as a criticism of your solution, which made perfect sense to me and is frequently what I do in my own spreadsheets. I merely offered an alternative to someone who was struggling.
Regards,
-Bill
Since the OP was confused by your instructions (indicating his level of inexperience with spreadsheets) and you did not explain how to fill the column or the purpose of $, I offered what I thought would be easier for someone at his level to understand.
And I had - *of course* - no problem with that.
My solution is not intended as a criticism of your solution, which made perfect sense to me and is frequently what I do in my own spreadsheets.
My only quibble was that you presented it as what I was "trying to say" - which wasn't so. Sorry if I gave any impression of being dismissive. This is a discussion list, after all.
I merely offered an alternative to someone who was struggling.
Splendid! And exactly why I offered my approval by finishing "chacun à son goût".
Brian Barker
Hi 
I thought it was really kind to de-geekify Brain's answer. The tpyo was
unfortunate but these things happen [shrugs]. The main thing, for me, was
that i could then go back to Brian's answer and understand it more easily.
Thanks and regards from
Tom
Thanks for the replies.
My earlier example was a
simplified version of the data I'm working with. When Brian & Wdragos
technique is applied to the 2026 samples I really have, it sort of works so
I can see what your talking about. I keep altering the incrementation that
takes place in this technique to try to get the last cell to say the stop
time. It comes pretty close but is always off by a few minutes. The reason
for this is theres a limit to the precision you can do with Times in
OpenOffice Calc. Using the hr/mn/sec format you can't generate small enough
increments to get the generated times to match the stop time. If there was a
hr/mn/sec/fraction of a second format you could do it. In Calc theres a
time format that looks like this, but in practice it doesn't 'roll over'
like say minutes or seconds.
It looks like Calc's stock functions won't do the job. I'm thinking of
getting around this by finding some source for a stopwatch program, and
maybe modifiying it to do something similar, but with a greater precision of
incrementation.
My earlier example was a simplified version of the data I'm working with. When Brian & Wdragos technique ...
They were not one technique but two different ones, in fact.
... is applied to the 2026 samples I really have, it sort of works so I can see what your talking about. I keep altering the incrementation that takes place in this technique to try to get the last cell to say the stop time. It comes pretty close but is always off by a few minutes.
My formula technique will get it right for you. Mr Drago's will also, provided you do his initial manual calculation correctly. If your calculation only comes "pretty close", you must be getting something wrong.
The reason for this is there's a limit to the precision you can do with Times in OpenOffice Calc.
There is always a limit to precision in anything, but this will not create the problems you describe. (My formula technique is probably less prone to rounding errors than Mr Drago's repeated addition method.) There is no reason for any reasonable length of data sequence that you should notice any rounding errors.
Using the hr/mn/sec format you can't generate small enough increments to get the generated times to match the stop time. If there was a hr/mn/sec/fraction of a second format you could do it.
You can format times to show fractions of a second; the normal precision of numbers in a Calc spreadsheet means that you can represent times down to around ten fractional places of a second! But in any case, you are here confusing formatting with the precision of a number: values are stored to full precision in a cell even if your cell formatting restricts the display - as it usually will. If you have a long list of samples, you will need to do Mr Drago's initial manual calculation to a greater significance than your cell formatting will perhaps show, or any errors will add up and eventually show in your list. My formula technique will not suffer the same problem.
By the way, there is an option at Tools | Options... | LibreOffice Calc | Calculate | Precision as shown. That causes any calculation to be performed on the rounded value displayed in any cell instead of the actual (potentially more accurate) value actually stored in the cell. Having that ticked would certainly cause rounding errors in Mr Drago's technique, so you want to have that *not* ticked for normal spreadsheet use.
In Calc there's a time format that looks like this, but in practice it doesn't 'roll over' like say minutes or seconds.
Three points here:
o The formats listed are just samples; you can have more fractional places displayed simply by adding more zeroes to the format code.
o The formats don't affect the calculation, only the display in each cell (providing you don't have that option above ticked).
o I'm not sure how you think things don't "roll over", but accumulated fractions of seconds will certainly become seconds, minutes and even hours when they need to.
It looks like Calc's stock functions won't do the job.
Believe me: my formula works. Mr Drago's does, providing you don't round the initial result too much. You are welcome to give up trying, but please don't blame Calc: of course it will "do the job".
I'm thinking of getting around this by finding some source for a stopwatch program, and maybe modifying it to do something similar, but with a greater precision of incrementation.
You have about fifteen significant decimal digits in spreadsheet calculations: that is enough for almost anything, providing you don't introduce errors yourself.
Why don't you get someone to look at your spreadsheet (or a sample copy, showing the problem) to see where you are going wrong?
I trust this helps.
Brian Barker
Hi.
I think the trick is to not calculate the difference/no. samples and keep adding but to calculate the difference and multiply by position over number of samples added to start time. For a quick test this seems to give times to 0.00 of a second and the finish time is always the finish time exactly.
i.e. for the 29th reading of 54 readings taken between 12:04 aqnd 12:36 the time is 12:04+28/53*(12:36-12:04)
Got to dash to beat the traffic, but can post the formula when I'm home.
Steve
.
I think the trick is to not calculate the difference/no. samples and keep adding but to calculate the difference and multiply by position over number of samples added to start time.
For the avoidance of doubt, you will see that this is what my original suggestion (two days ago) does.
For a quick test this seems to give times to 0.00 of a second ...
No, it gives times to about *ten* fractional places of a second (0.0000000000), but it may well *display* with less precision - depending on your cell formatting. This is one of the original questioner's misunderstandings.
Got to dash to beat the traffic, but can post the formula when I'm home.
Or see my original reply: " In B2, enter: =B1+(B$6-B$1)/5 and fill this down to B5"!
Brian Barker
It might be helpful if you post the actual start/stop times for the 2026 samples. There's more than one way to skin this cat and I'd only look for solutions outside of Calc as a very last resort.
-Bill
I think we understand each other, Brian. It's all good...
Regards,
-Bill
Hi
If you are not happy with Calc and looking for a reason to use something
else then maybe try Gnumeric.
It is a specialist tool that focusses more on spreadsheeting functionality
without having to worry about any other apps as it's a stand-alone tool.
It co-operates well with LibreOffice/OpenOffice and others in the same
eco-system.
Brian's answer probably works in that too or (unlikely but theoretically
possible) might need a little tweak for Gnumeric.
I still think Mr Drago's post was to help explain Brian's answer rather
than being intended as an answer in it's own right. It alerts us to the
potential for a "fence post" error and that clarifies why Brian set his
figures as he did.
Now that the question has been extended to a greater sample it might be
better to upload the problem file (or just the sheet) to Nabble or
somewhere so that people can help identify the problem created by
up-scaling the formula.
Regards from
Tom
It might also be helpful if you mentioned your data acquisition hardware.
It might be that using a standard interval reading is possible and you don't have to guess about the readings at all.
Start 00:00:00, first interval 00:01:00, second interval 00:02:00 etc...
Also the data acquisition software. I have written a few data
acquisition programs and in some cases the sample rate was extremely
high over an extended period of time. To keep the file sizes down to
something reasonable I stored the captured data with a compression
algorithm which, amongst other things, eliminated most of the time
stamps. I then provided an user option in the UI to either
display/export the raw data or a representation of the full details.
What the OP describes is simple raw data and maybe there is a similar
display/export option in the capture software being used here.
Dave
Paul D. Mirowsky wrote:
I think the trick is to not calculate the difference/no. samples and keep adding but to calculate the difference and multiply by position over number of samples added to start time.
For the avoidance of doubt, you will see that this is what my original suggestion (two days ago) does.
For a quick test this seems to give times to 0.00 of a second ...
No, it gives times to about *ten* fractional places of a second (0.0000000000), but it may well *display* with less precision - depending on your cell formatting. This is one of the original questioner's misunderstandings.
Just meaning there is a pre-defined format that showed in my test to 0.00 seconds.
Got to dash to beat the traffic, but can post the formula when I'm home.
Or see my original reply: " In B2, enter: =B1+(B$6-B$1)/5 and fill this down to B5"!
Hi Brian.
What I am meaning would be
B2=B1+(B$6-B$1)*1//5
B3=B1+(B$6-B$1)*2/5
.
B5=B1+(B$6-B$1)*4/5
Using =$B$1+($B$6-$B$1)*(ROW(A3)-ROW($A$1))/(ROW($A$6)-ROW($A$1)) makes the formula a bit more transferable.
As you say you can use time format HH:MM:SS.00 or HH:MM:SS.000 as desired.
Steve