2014 Archives by date, by thread · List index

# 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
```