Date: prev next · Thread: first prev next last
2014 Archives by date, by thread · List index

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.

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:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.