Re: [libreoffice-users] Currency calculations [was: Avery 8167 label printing]

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.

Virgil



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: 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