Ask Your Question
0

weird Calc results from elapsed-time formula, specific times only

asked 2018-12-28 22:13:11 +0200

dpmaddalena gravatar image

In Calc, I use a formula for getting the elapsed time between entries, and the output of the formula returns a 2 or 3 digit number representing minutes elapsed between clock times. The formula:

=(B1-A1)*24*60

where I manually enter the times (A1 and B1) as HH:MM, formatted as 24-hour for display. For most values, this returns results like this

15:00   15:53   53

However, when the two clock times are roughly between 7:55 and 8:55 AM (the exact limits shift, and I can't keep track of why), and usually ONLY if they are 54 minutes apart OR LESS, they return a number with an inexplicable fraction (13 decimal places!) as here:

08:00   08:53   53.0000000000001

or here:

08:02   08:56   54.0000000000001

or here:

07:57   08:49   52.0000000000001

or here:

07:55   07:56   0.999999999999996

BUT, not here:

08:01   08:56   55

nor here,

08:43   08:57   14

nor here

07:55   08:48   53

I could probably narrow down the specific limiting factors, but I am not that patient. If you ask me why I chose the formula I use for this, my answer is, 'because I did a search and this was the simplest one that worked for me', not because I understand exactly how it works--I'm not that strong a numbers guy. But I think I can see why it should work, and why it should not return fractional results!

Very confused and hoping for help. The weird results are practically NBD, as I get the info I need from the answer, even if it has 13 decimal places. But, while I'm not good at math, I would like for my math to work and make sense, so I'm pretty bothered by this.

thanks for any help!

edit retag flag offensive close merge delete

Comments

I don't know the reason for your problem, although it might be caused by a peculiarity in the floating point arithmetic of converting between hours and minutes and back again in LibreOffice, as evidenced by the difference in the 13th decimal place. Floating point arithmetic sometimes produces these quirky little differences when we think the answer should be exact.

A simple solution might be to just format the cell containing the calculated time difference to display zero decimal places. Menu Format -> Cells and under the Numbers tab make sure the cell is a Number (rather than anything else) with the Option of zero for decimal places. Or, you could try applying the ROUND function to your time difference and specify zero decimal places so that it rounded off as a whole number.

ve3oat gravatar imageve3oat ( 2018-12-29 16:57:08 +0200 )edit

Thanks for this @ve3oat. Setting the number's decimal points to zero is clean enough for me in terms of display. I appreciate the comment. If I was feeding the result into any other formula, I'd try using ROUND to clean it up. Thanks again!

dpmaddalena gravatar imagedpmaddalena ( 2018-12-29 18:15:56 +0200 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2018-12-29 23:22:07 +0200

updated 2018-12-29 23:25:03 +0200

Hello @dpmaddalena

As @ve3oat correctly stated, the reason is Floating-point arithmetic and it's accuracy problems dealing with the real numbers and their non-representability in binary systems in this case. Please also refer to @Lupp example given in this thread and @Mike Kaganski answer here.

In a few words, some numbers can not be represented in the computer arithmetic exactly with the same value as they have in the real world mathematics and these numbers are defined with the finite precision, so performing operations on such a numbers leads to 'weird' results. As the time is represented in calc as a fraction of the day (more information about it you can find here), some values evaluates to such a non-representable numbers and this gives you these 'weird' decimals. What comes to workarounds - @ve3oat already gave them.

edit flag offensive delete link more

Comments

thank you very much, @SM_Riga. You've confirmed for me that the math behind my problem will remain incomprehensible for me! :) Nice to know that the problem is not my fault; thanks for the references and for the encouragement to pick a workaround and be satisfied.

dpmaddalena gravatar imagedpmaddalena ( 2018-12-30 00:13:11 +0200 )edit

In most practical situations it is safe (and satisfying) to ignore these little inconsistencies. After all, your 0.0000000000001 minute difference amounts to only 0.00006 microseconds. It is probably an understatement to say that is small. :-)

ve3oat gravatar imageve3oat ( 2018-12-30 02:28:44 +0200 )edit

But it's billable! lol.

dpmaddalena gravatar imagedpmaddalena ( 2019-01-01 18:20:54 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-12-28 22:13:11 +0200

Seen: 45 times

Last updated: Dec 29 '18