Generate a column of times

classic Classic list List threaded Threaded
25 messages Options
Next » 12
office76#xt office76#xt
Reply | Threaded
Open this post in threaded view
|

Generate a column of times

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?
Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

Re: Generate a column of times

At 08:54 08/09/2014 -0700, Nobody Noname wrote:

>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


--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Paul D. Mirowsky Paul D. Mirowsky
Reply | Threaded
Open this post in threaded view
|

Re: Generate a column of times

In reply to this post by office76#xt
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

On 9/8/2014 11:54 AM, office76#xt wrote:

> 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?
>
>
>
> --
> View this message in context: http://nabble.documentfoundation.org/Generate-a-column-of-times-tp4121568.html
> Sent from the Users mailing list archive at Nabble.com.
>


--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

office76#xt office76#xt
Reply | Threaded
Open this post in threaded view
|

Re: Generate a column of times

In reply to this post by Brian Barker
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.
wdrago wdrago
Reply | Threaded
Open this post in threaded view
|

Re: Generate a column of times

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


On 9/8/2014 8:29 PM, office76#xt wrote:

> 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.
>
>
>
> --
> View this message in context: http://nabble.documentfoundation.org/Generate-a-column-of-times-tp4121568p4121600.html
> Sent from the Users mailing list archive at Nabble.com.
>


--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

Re: Generate a column of times

In reply to this post by office76#xt
At 17:29 08/09/2014 -0700, Nobody Noname wrote:
>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  


--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

Re: Generate a column of times

In reply to this post by wdrago
At 05:45 09/09/2014 -0400, William Drago wrote:
>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  


--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

wdrago wdrago
Reply | Threaded
Open this post in threaded view
|

Re: Generate a column of times

On 9/9/2014 6:18 AM, Brian Barker wrote:

> At 05:45 09/09/2014 -0400, William Drago wrote:
>> 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


--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

TomD TomD
Reply | Threaded
Open this post in threaded view
|

Re: Generate a column of times

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 :)




On 9 September 2014 12:22, William Drago <[hidden email]> wrote:

> On 9/9/2014 6:18 AM, Brian Barker wrote:
>
>> At 05:45 09/09/2014 -0400, William Drago wrote:
>>
>>> 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
>
>
>
> --
> To unsubscribe e-mail to: [hidden email]
> Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-
> unsubscribe/
> Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
> List archive: http://listarchives.libreoffice.org/global/users/
> All messages sent to this list will be publicly archived and cannot be
> deleted
>
>

--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

Re: Generate a column of times

In reply to this post by wdrago
At 07:22 09/09/2014 -0400, William Drago wrote:
>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  


--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

office76#xt office76#xt
Reply | Threaded
Open this post in threaded view
|

Re: Generate a column of times

In reply to this post by office76#xt
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.
Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

Re: Generate a column of times

At 17:22 09/09/2014 -0700, Nobody Noname wrote:
>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


--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

steveedmonds steveedmonds
Reply | Threaded
Open this post in threaded view
|

Re: Generate a column of times

In reply to this post by office76#xt
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

.
On 2014-09-10 16:32, Brian Barker wrote:

> At 17:22 09/09/2014 -0700, Nobody Noname wrote:
>> 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
>
>


--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

Re: Generate a column of times

At 16:58 10/09/2014 +1200, Steve Edmonds 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.

>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


--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

TomD TomD
Reply | Threaded
Open this post in threaded view
|

Re: Generate a column of times

In reply to this post by steveedmonds
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 :)



On 10 September 2014 06:09, Brian Barker <[hidden email]> wrote:

> At 16:58 10/09/2014 +1200, Steve Edmonds 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.
>
>  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
>
>
> --
> To unsubscribe e-mail to: [hidden email]
> Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-
> unsubscribe/
> Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
> List archive: http://listarchives.libreoffice.org/global/users/
> All messages sent to this list will be publicly archived and cannot be
> deleted
>
>

--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
wdrago wdrago
Reply | Threaded
Open this post in threaded view
|

Re: Generate a column of times

In reply to this post by office76#xt
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

On 9/9/2014 8:22 PM, office76#xt wrote:

> 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.
>
>
>
> --
> View this message in context: http://nabble.documentfoundation.org/Generate-a-column-of-times-tp4121568p4121744.html
> Sent from the Users mailing list archive at Nabble.com.
>


--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

wdrago wdrago
Reply | Threaded
Open this post in threaded view
|

Re: Generate a column of times

In reply to this post by Brian Barker
I think we understand each other, Brian. It's all good...

Regards,
-Bill

On 9/9/2014 8:03 AM, Brian Barker wrote:

> At 07:22 09/09/2014 -0400, William Drago wrote:
>> 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
>


--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Paul D. Mirowsky Paul D. Mirowsky
Reply | Threaded
Open this post in threaded view
|

Re: Generate a column of times

In reply to this post by wdrago
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...


On 9/10/2014 7:45 AM, William Drago wrote:

> 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
>
> On 9/9/2014 8:22 PM, office76#xt wrote:
>> 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.
>>
>>
>>
>> --
>> View this message in context:
>> http://nabble.documentfoundation.org/Generate-a-column-of-times-tp4121568p4121744.html
>> Sent from the Users mailing list archive at Nabble.com.
>>
>
>


--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Dave Barton Dave Barton
Reply | Threaded
Open this post in threaded view
|

Re: Generate a column of times

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:

> 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...
>
>
> On 9/10/2014 7:45 AM, William Drago wrote:
>> 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
>>
>> On 9/9/2014 8:22 PM, office76#xt wrote:
>>> 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.


--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

steveedmonds steveedmonds
Reply | Threaded
Open this post in threaded view
|

Re: Generate a column of times

In reply to this post by steveedmonds

On 2014-09-10 17:09, Brian Barker wrote:

> At 16:58 10/09/2014 +1200, Steve Edmonds 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

--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Next » 12