Re: [libreoffice-users] 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: users+unsubscribe@global.libreoffice.org
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