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

At 06:33 11/09/2014 -0700, Nobody Noname wrote:
> Thanks again for the replies, this is what I've tried so far:

Why not try one of the two techniques already suggested (both of which work)?

> The start time was 10:25pm and the stop time was 10:55pm, with 2026 
> samples. I first highlight column 'A', and format the cells as 
> 'Time', using the 01:37:46pm format. The first cell, A1, gets the 
> start time, 10:25:00pm. The second cell gets 10:25:01pm, which is 
> the start time with an increment of 1 second.

That's your mistake. You said in your original message that the 
samples were taken "at *approximately* 1 sample per second" (my 
emphasis), not exactly so. Your 2026 samples span 2025 intervals 
between 10:25 and 10:35, and if we divide thirty minutes by 2025, we 
find that the interval is actually eight-ninths of a second, not 
exactly one second. If you add eight-ninths of a second 2025 times to 
10:25 you will get to 10:55 with no problem.

> I then highlight these first two cells, then drag their lower right 
> corner down the column to row 2026. The last cell now reads 
> 10:58:45pm, which does not match the stop time of 10:55pm.

That's because 2025 seconds is not thirty minutes, but indeed 
thirty-three and three-quarter minutes. You should note that your 
difficulty here is entirely to do with your appreciation of the 
problem, and nothing to do with whether Calc can or cannot do what you require.

Using Mr Drago's technique, you need to set A2 to 10:25 PM plus 
eight-ninths of a second. You can do this in various ways. One is to 
put 10:25 PM and 10:55 PM in two cells, subtract them, and divide by 
2025. As a number, this will display as 0.000010288... as the 
interval is that fraction of a day. Use this value in the calculation 
in A2, using =A1+Xn, where Xn is the result cell. Note that the value 
in A2 will display as 10:25:01 PM, but it is not exactly one second 
later - it merely displays that way with your chosen formatting. The 
value *stored* in the cell is nevertheless accurate. You will need to 
select the value in A2, copy it, and paste it back using Paste 
Special... with Formulas unticked in order to convert the formula 
into a number. Now select A1 and A2 and fill down the column. Row 
2026 will read 10:55:00 PM. In fact, rounding errors mean that it 
misses the mark by less than a hundred-millionth of a second. Is that 
good enough?!

Using my formula technique, put the start and finish times in A1 and 
A2026. The formula you need in A2 with these new cells is
=A1+(A$2026-A$1)/2025
Select just A2 this time (not A1 and A2) and fill this down the 
column to A2025 (not A2026, or you will get a circular reference 
error, Err:522). The rounding errors will be no more than with the 
previous technique.

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