Re: [libreoffice-users] Re: Generate a column of times

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