Re: [libreoffice-users] Re: Generate a column of times
Well, of course not. The difference between 10:25pm and 10:55pm is exactly 30 minutes, or 1800 seconds. But you have 2026 samples, which, at a second each, comes to 33 minutes and 46 seconds, which is equivilent to a stop time of 10:58:46pm. The extra second is (I think) due to the first sample being zero seconds after the start time, instead of 1, so you're actually only adding 2025 seconds, not 2026. Your samples are spaced more frequently than one second apart, which is why using a fixed spacing of a second to start the series doesn't work. Brian's previous answer about using a formula sounds like the answer, although I haven't looked at it myself. But he knows what he is talking about, and the approach is the correct one. It calculates the actual sample frequency and uses that to calculate the timestamps for each sample. Try that and see if it doesn't give you the answer you are looking for. Paul On Thu, 11 Sep 2014 06:33:54 -0700 (PDT) office76#xt l0c35...@verizon.net wrote: Thanks again for the replies, this is what I've tried so far: 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. 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. -- View this message in context: http://nabble.documentfoundation.org/Generate-a-column-of-times-tp4121568p4121956.html Sent from the Users mailing list archive at Nabble.com. -- To unsubscribe e-mail to: users+unsubscr...@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
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.10288... 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+unsubscr...@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
Re: [libreoffice-users] Re: Generate a column of times
At 09:24 11/09/2014 -0700, Nobody Noname wrote: ... that last reply got it working. Good-oh! I can see some time values repeated twice but that's what you'd expect with a non one sample per second sampling rate. Exactly. I meant to mention this but forgot. Remember that the repetition is only in the displayed, rounded values: the actual stored values will be accurate and not repeated. Brian Barker -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] Re: Generate a column of times
It might be helpful if you post the actual start/stop times for the 2026 samples. There's more than one way to skin this cat and I'd only look for solutions outside of Calc as a very last resort. -Bill On 9/9/2014 8:22 PM, office76#xt wrote: Thanks for the replies. My earlier example was a simplified version of the data I'm working with. When Brian Wdragos technique 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. The reason for this is theres a limit to the precision you can do with Times in OpenOffice Calc. 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. In Calc theres a time format that looks like this, but in practice it doesn't 'roll over' like say minutes or seconds. It looks like Calc's stock functions won't do the job. I'm thinking of getting around this by finding some source for a stopwatch program, and maybe modifiying it to do something similar, but with a greater precision of incrementation. -- View this message in context: http://nabble.documentfoundation.org/Generate-a-column-of-times-tp4121568p4121744.html Sent from the Users mailing list archive at Nabble.com. -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] Re: Generate a column of times
Hi :) If you are not happy with Calc and looking for a reason to use something else then maybe try Gnumeric. It is a specialist tool that focusses more on spreadsheeting functionality without having to worry about any other apps as it's a stand-alone tool. It co-operates well with LibreOffice/OpenOffice and others in the same eco-system. Brian's answer probably works in that too or (unlikely but theoretically possible) might need a little tweak for Gnumeric. I still think Mr Drago's post was to help explain Brian's answer rather than being intended as an answer in it's own right. It alerts us to the potential for a fence post error and that clarifies why Brian set his figures as he did. Now that the question has been extended to a greater sample it might be better to upload the problem file (or just the sheet) to Nabble or somewhere so that people can help identify the problem created by up-scaling the formula. Regards from Tom :) On 10 September 2014 06:09, Brian Barker b.m.bar...@btinternet.com wrote: At 16:58 10/09/2014 +1200, Steve Edmonds wrote: 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 the avoidance of doubt, you will see that this is what my original suggestion (two days ago) does. For a quick test this seems to give times to 0.00 of a second ... No, it gives times to about *ten* fractional places of a second (0.00), but it may well *display* with less precision - depending on your cell formatting. This is one of the original questioner's misunderstandings. Got to dash to beat the traffic, but can post the formula when I'm home. Or see my original reply: In B2, enter: =B1+(B$6-B$1)/5 and fill this down to B5! Brian Barker -- To unsubscribe e-mail to: users+unsubscr...@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 -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] Re: Generate a column of times
It might also be helpful if you mentioned your data acquisition hardware. It might be that using a standard interval reading is possible and you don't have to guess about the readings at all. Start 00:00:00, first interval 00:01:00, second interval 00:02:00 etc... On 9/10/2014 7:45 AM, William Drago wrote: It might be helpful if you post the actual start/stop times for the 2026 samples. There's more than one way to skin this cat and I'd only look for solutions outside of Calc as a very last resort. -Bill On 9/9/2014 8:22 PM, office76#xt wrote: Thanks for the replies. My earlier example was a simplified version of the data I'm working with. When Brian Wdragos technique 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. The reason for this is theres a limit to the precision you can do with Times in OpenOffice Calc. 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. In Calc theres a time format that looks like this, but in practice it doesn't 'roll over' like say minutes or seconds. It looks like Calc's stock functions won't do the job. I'm thinking of getting around this by finding some source for a stopwatch program, and maybe modifiying it to do something similar, but with a greater precision of incrementation. -- View this message in context: http://nabble.documentfoundation.org/Generate-a-column-of-times-tp4121568p4121744.html Sent from the Users mailing list archive at Nabble.com. -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] Re: Generate a column of times
Also the data acquisition software. I have written a few data acquisition programs and in some cases the sample rate was extremely high over an extended period of time. To keep the file sizes down to something reasonable I stored the captured data with a compression algorithm which, amongst other things, eliminated most of the time stamps. I then provided an user option in the UI to either display/export the raw data or a representation of the full details. What the OP describes is simple raw data and maybe there is a similar display/export option in the capture software being used here. Dave Paul D. Mirowsky wrote: It might also be helpful if you mentioned your data acquisition hardware. It might be that using a standard interval reading is possible and you don't have to guess about the readings at all. Start 00:00:00, first interval 00:01:00, second interval 00:02:00 etc... On 9/10/2014 7:45 AM, William Drago wrote: It might be helpful if you post the actual start/stop times for the 2026 samples. There's more than one way to skin this cat and I'd only look for solutions outside of Calc as a very last resort. -Bill On 9/9/2014 8:22 PM, office76#xt wrote: Thanks for the replies. My earlier example was a simplified version of the data I'm working with. When Brian Wdragos technique 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. The reason for this is theres a limit to the precision you can do with Times in OpenOffice Calc. 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. In Calc theres a time format that looks like this, but in practice it doesn't 'roll over' like say minutes or seconds. It looks like Calc's stock functions won't do the job. I'm thinking of getting around this by finding some source for a stopwatch program, and maybe modifiying it to do something similar, but with a greater precision of incrementation. -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] Re: Generate a column of times
This is what Brian is trying to explain... You've got 6 samples. The first one taken at 12:04PM, the last one at 12:36PM. The total elapsed time is 12:36PM - 12:04PM = 22 minutes. 22 divided by 5 (always the total number of readings minus 1) = 6.4 minutes or 6m and 24s per interval. Adding 6:24 to 12:04PM gives you the time of the second reading, 12:10:24PM. Enter that value into cell B2. Now we make the computer do the work for all remaining readings. Follow these instructions carefully: Highlight cells B1 and B2. Hover the mouse over the little black square in the lower right hand corner of the the highlighted cells until it turns into a cross (+). Now hold the left mouse button down and drag the mouse downward. As you drag the mouse each cell will fill with the correct time stamp. You will end up with this: 12.7 12:04:00 PM 8.912:10:24 PM 3.512:16:48 PM 2.112:23:12 PM 7.212:29:36 PM 6.112:36:00 PM If you want to use 1S as your interval the just enter 12:04:01PM in B2 and drag the mouse as explained above. Hope that helps... -Bill On 9/8/2014 8:29 PM, office76#xt wrote: Thanks Brian for the reply, The data acquisition device samples at approximately 1 sample per second not a nice fixed sample rate. And unfortunately time stamps the first and last sample only. If the sample values are in column A1, then I'd like an approximate time-line in the cells between the start logging and stop logging times in column B2. What I'm after is to look at my notes taken at different times (the time noted) during logging, and identify the approximate sample that corresponds to them. I'm afraid I don't understand your example as it looks like you have the samples in a row not a column. -- View this message in context: http://nabble.documentfoundation.org/Generate-a-column-of-times-tp4121568p4121600.html Sent from the Users mailing list archive at Nabble.com. -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] Re: Generate a column of times
At 05:45 09/09/2014 -0400, William Drago wrote: This is what Brian is trying to explain... I was doing more than try! And no: what you suggest here is not what I was saying. You've got 6 samples. The first one taken at 12:04PM, the last one at 12:36PM. The total elapsed time is 12:36PM - 12:04PM = 22 minutes. Er that's *thirty*-two minutes , not twenty-two, by my arithmetic. 22 divided by 5 (always the total number of readings minus 1) = 6.4 minutes or 6m and 24s per interval. Good: but only as long as it's 32, not 22, we are dividing. Adding 6:24 to 12:04PM gives you the time of the second reading, 12:10:24PM. Enter that value into cell B2. Now we make the computer do the work for all remaining readings. Why do a manual calculation for the first value (where you could easily make mistakes - oh, you did!) and only then make the computer do the work? The computer can do all the work. Follow these instructions carefully: Why not follow my instructions instead (which are quite different), where a formula does all the calculation for you, not just the last bit? But chacun à son goût! Brian Barker -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] Re: Generate a column of times
On 9/9/2014 6:18 AM, Brian Barker wrote: At 05:45 09/09/2014 -0400, William Drago wrote: This is what Brian is trying to explain... I was doing more than try! And no: what you suggest here is not what I was saying. You've got 6 samples. The first one taken at 12:04PM, the last one at 12:36PM. The total elapsed time is 12:36PM - 12:04PM = 22 minutes. Er that's *thirty*-two minutes , not twenty-two, by my arithmetic. 32 minutes, yes! That's what happens when I do math so early in the morning. Follow these instructions carefully: Why not follow my instructions instead (which are quite different), where a formula does all the calculation for you, not just the last bit? Since the OP was confused by your instructions (indicating his level of inexperience with spreadsheets) and you did not explain how to fill the column or the purpose of $, I offered what I thought would be easier for someone at his level to understand. It is not necessarily the best approach, but something I thought would solve his problem and he would understand. It also demonstrates the ability of a spreadsheet to generate data based on a pattern in selected cells. This can be a time saver and is not something everyone is aware of. My solution is not intended as a criticism of your solution, which made perfect sense to me and is frequently what I do in my own spreadsheets. I merely offered an alternative to someone who was struggling. Regards, -Bill -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] Re: Generate a column of times
At 07:22 09/09/2014 -0400, William Drago wrote: Since the OP was confused by your instructions (indicating his level of inexperience with spreadsheets) and you did not explain how to fill the column or the purpose of $, I offered what I thought would be easier for someone at his level to understand. And I had - *of course* - no problem with that. My solution is not intended as a criticism of your solution, which made perfect sense to me and is frequently what I do in my own spreadsheets. My only quibble was that you presented it as what I was trying to say - which wasn't so. Sorry if I gave any impression of being dismissive. This is a discussion list, after all. I merely offered an alternative to someone who was struggling. Splendid! And exactly why I offered my approval by finishing chacun à son goût. Brian Barker -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] Re: Generate a column of times
Hi :) I thought it was really kind to de-geekify Brain's answer. The tpyo was unfortunate but these things happen [shrugs]. The main thing, for me, was that i could then go back to Brian's answer and understand it more easily. Thanks and regards from Tom :) On 9 September 2014 12:22, William Drago wdr...@suffolk.lib.ny.us wrote: On 9/9/2014 6:18 AM, Brian Barker wrote: At 05:45 09/09/2014 -0400, William Drago wrote: This is what Brian is trying to explain... I was doing more than try! And no: what you suggest here is not what I was saying. You've got 6 samples. The first one taken at 12:04PM, the last one at 12:36PM. The total elapsed time is 12:36PM - 12:04PM = 22 minutes. Er that's *thirty*-two minutes , not twenty-two, by my arithmetic. 32 minutes, yes! That's what happens when I do math so early in the morning. Follow these instructions carefully: Why not follow my instructions instead (which are quite different), where a formula does all the calculation for you, not just the last bit? Since the OP was confused by your instructions (indicating his level of inexperience with spreadsheets) and you did not explain how to fill the column or the purpose of $, I offered what I thought would be easier for someone at his level to understand. It is not necessarily the best approach, but something I thought would solve his problem and he would understand. It also demonstrates the ability of a spreadsheet to generate data based on a pattern in selected cells. This can be a time saver and is not something everyone is aware of. My solution is not intended as a criticism of your solution, which made perfect sense to me and is frequently what I do in my own spreadsheets. I merely offered an alternative to someone who was struggling. Regards, -Bill -- To unsubscribe e-mail to: users+unsubscr...@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 -- To unsubscribe e-mail to: users+unsubscr...@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
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+unsubscr...@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
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+unsubscr...@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
Re: [libreoffice-users] Re: Generate a column of times
At 16:58 10/09/2014 +1200, Steve Edmonds wrote: 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 the avoidance of doubt, you will see that this is what my original suggestion (two days ago) does. For a quick test this seems to give times to 0.00 of a second ... No, it gives times to about *ten* fractional places of a second (0.00), but it may well *display* with less precision - depending on your cell formatting. This is one of the original questioner's misunderstandings. Got to dash to beat the traffic, but can post the formula when I'm home. Or see my original reply: In B2, enter: =B1+(B$6-B$1)/5 and fill this down to B5! Brian Barker -- To unsubscribe e-mail to: users+unsubscr...@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
