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

Thanks for the tip, but back in 1989 when this was all happening, I'm not sure that my DOS spreadsheet even had the ability to display numbers as "currency". I certainly made no attempt to do so, even if it were possible. They were just numbers and, yes, integers, and no, the spreadsheet didn't get 28 divided by 7 correct.

I have learned, however, as you point out, that how numbers are displayed is not the same as what they are. For example, you can't "round" a number in a spreadsheet by simply reducing the number of decimal places displayed in a cell. The underlying number remains unrounded and further calculations with it might produce undesired results. When working with currency, I've developed the habit of probably overusing the the "@round" function at each step of a complex calculation just to ensure desired results.


On 02/11/2016 04:16 AM, Brian Barker wrote:
At 15:18 10/02/2016 -0500, Virgil Arrington wrote:
About 25 years ago, I was the treasurer of my children's preschool. I created a spreadsheet to calculate paychecks, and I found that the paycheck was consistently off by .01 (a penny). It drove me nuts. As it turned out, one part of the calculation required the division of 28 by 7, which every third grader knows is 4. Well, my spreadsheet gave an answer of 3.9999999999_. By itself, it wasn't a big problem, but later in the chain of operations, the 3.99999_ produced a result that rounded *down* to the nearest penny instead of *up*, which it would have done if the 28/7 had resulted in 4 instead of 3.9999. I complained to a computer friend of mine who tried to explain that the computer's answer was more "precise" than my mental math of 28/7=4. I didn't buy it. I learned a valuable lesson in blindly accepting a computer's calculation simply because it was made by a computer.

I strongly suspect that you didn't, in fact, try to divide 28 by 4 - which your spreadsheet would have got right!

Currency amounts are often expressed in whole pounds or dollars or whatever, with pence or cents as a fraction of the basic currency unit. But these fractions are deceptive: you simply don't want them to be handled the way they are stated. If, for example, you have the value $1.23 and halve it, you don't want the result to be $0.615. The amounts may look like what programmers call "real" or "floating-point" values, but they are actually *integer* values: that $1.23 is better thought of as 123 cents - with no fractional part present.

Spreadsheet values are always real rather than integer, so you have to take care with how you calculate with them. If you halve $1.23 (properly formatted as Currency) in a spreadsheet, the result will be helpfully rounded in the display to $0.62. But the underlying value will still be the correct result of 0.615. You need to be alert to this situation in order to use spreadsheets successfully. You have a number of options, and your choice may depend on circumstances: o You may decide to retain the underlying value (although the displayed value will still be rounded).
o You may round or truncate the value explicitly in your formula.
o You may choose to tick the option at Tools | Options... | LibreOffice Calc | Calculate | Precision as shown.

Depending on your choice, doubling the displayed value of $0.62 may produce $1.23 or $1.24. Both of these results may be helpful in different circumstances. It is perfectly possible to carry out accounting calculations in a spreadsheet providing they are done sensitively.

So what was happening in your case? Well, my guess is that the value you were dividing by 4 was not the integer 28 but instead some currency amount ending in 28 cents. If you express this conventionally as $X.28, you are calculating with a floating-point value having a fractional part of 0.28. Now although this fraction terminates after two decimal digits, it is a recurring binary fraction: 0.01000111101011100001... - with those twenty fractional binary digits recurring indefinitely. It can only be that this value is either truncated or rounded in a cell value. So if you choose to use a spreadsheet conventionally - and here's the important point - an amount of $X.28 is *already* stored only approximately. This is the genuine source of your problem - not any subsequent division by 4.

At 18:14 10/02/2016 -0500, Virgil Arrington wrote:
What I found interesting was that, in the spreadsheet, I got the "wrong" 3.9999, but I wrote a simple BASIC program on my beloved Tandy Model 100 and got the "correct" answer of 4. I was excited to see that my little first generation laptop gave better results than my state of the art (for the time) PC.

Well, we cannot know how you programmed this, but in Basic you may have been forced either to use integer values - in which case you would have represented $1.28 as 128 in cents, avoiding the problem - or else to attend to the rounding explicitly. However you did this, you were probably alert to the very problem of which you needed to be aware in the spreadsheet version. The difference will not have been because your simpler hardware was somehow better at doing elementary arithmetic than your flashy new PC.

At 19:55 10/02/2016 -0700, Ken Springer wrote:
I wouldn't buy it either. I don't care how someone tries to explain it, the result is wrong. Pure and simple. Just try pulling that response on a grade school teacher, and you're going home with an "F". How something can be more "precise" than the correct answer is beyond me. Wrong is wrong, I don't care how somebody wishes to justify it.

Sadly, all this attitude achieves is that you are never going to be able to understand what is happening or to use spreadsheets reliably. That is unfortunate, of course.

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.