Datatribe Softwerks, Ltd.

Comments are currently disabled. Feel free to use the contact form.

Reader Contributions:

Nathan T. Freeman (03/06/2009 01:23 PM) website / e-mail

It's not Lotusscript. It's digital computers.

{Link}

{Link}

{Link}

Jerry (03/06/2009 01:41 PM)

@Nathan

All well and good, and thanks for the links, but we're left looking like the chumps when LS manifests the error but Excel doesn't. Fair or unfair or even unrealistic as a comparison, telling an irate customer "that's just the way computers are" doesn't carry much cred. :-)

Jeremy Hodge (03/06/2009 03:17 PM) website / e-mail

Its not just lotus script, formulas are susceptible as well :

{Link}

Nathan T. Freeman (03/07/2009 07:15 AM) website / e-mail

Jerry, you misunderstand my point. Excel is ALSO getting the erroneous value from the processor -- it simply has specific code to address it because Excel is so famously used for financial calculations.

From David Goldberg's "What Every Computer Scientist Should Know About Floating Point Arithmetic"...

"The most common situation is illustrated by the decimal number 0.1. Although it has a finite decimal representation, in binary it has an infinite repeating representation. Thus when = 2, the number 0.1 lies strictly between two floating-point numbers and is exactly representable by neither of them."

Your point about satellite inaccuracies is exactly backwards. That's where the increasing expression of precision and the later normalization become MORE important. From the Wikipedia article...

"Floating-point arithmetic is at its best when it is simply being used to measure real-world quantities over a wide range of scales (such as the orbital period of Io or the mass of the proton), and at its worst when it is expected to model the interactions of quantities expressed as decimal strings that are expected to be exact. An example of the latter case is financial calculations. For this reason, financial software tends not to use a binary floating-point number representation.[6] The "decimal" data type of the C# programming language, and the IEEE 854 standard, are designed to avoid the problems of binary floating-point representation, and make the arithmetic always behave as expected when numbers are printed in decimal."

Want to get the same results in LS? Use the Currency datatype. I made a simple form with a multivalue field called "resultSequence" and button with the following code. The output matched Excel perfectly.

Dim wrkspc As New NotesUIWorkspace
Dim doc As NotesDocument

Set doc = wrkspc.currentDocument.Document

Dim sum As Currency
'Note: I would gladly use chinese dumplings as a medium of exchange!
Dim increment As Currency
Dim sequence(200) As Currency
Dim i As Integer

increment = 1.23
For i = 0 To Ubound(sequence)
sum = sum+increment
sequence(i) = sum
Next

Call doc.replaceItemValue("resultSequence", sequence)
Call wrkSpc.currentDocument.reload

Jerry (03/07/2009 03:00 PM)

mmmm... dim sum.

Thanks for the explanation, Nathan. Using the currency data type is an easy enough conversion to make and saves us having to round all the time.

I still don't think you would want any loss of precision when calculating orbital periods... seems like the sort of thing that makes probes slam into planets, but then I don't work at JPL either.

Nathan T. Freeman (03/07/2009 05:48 PM) website / e-mail

Well, the thing is: you're getting a MORE precise number when you're performing the calc in Double. It's just more precisely an expression of an infinite repeat when it's expressed in base2.

Weird to wrap your mind around, I know. :-) But look, you got more digits from the initial Lotusscript run. More digits = more precise conversion between base10 and base2.

Anyway, glad I could help with the Currency reference.

Jerry (03/07/2009 06:18 PM)

Ah, but see, we're getting academic about it. It's true that the number 54.57999999 might be a more accurate rendition of converting from base2 to base10, but it's not precisely 54.12 + 2.46. See the difference? It's accurate from a machine perspective, but still imprecise from a real word perspective. From my perspective, any time you are using a decimal number, it should behave properly. Really, when do you ever want to see the burps and hic-ups of the underlying system?

So, while technically wrong of me, I still posit - we, the coders, look bad when computers (in this case, the Double data type in LS) do this kind of thing. Now we'll all look less bad thanks to you, again I thank you, but I guess this is just one of those shoots of bamboo under my nail with "the way it is". writhing... in agony... over binary maths....

Richard Schwartz (03/07/2009 08:01 PM) website / e-mail

Part of the blame goes to Intel. The Currency data type in LotusScript is a software emulation of BCD, which IBM mainframes and many other ancient computer systems fully supported. Intel's x86 chips, however, support only very limited BCD instructions.

And BTW: number items in documents are stored in binary floating point representation, so if you store the result of Currency calculations in a NotesItem and then read the value back, some precision is lost.

Nathan T. Freeman (03/07/2009 09:35 PM) website / e-mail

I guess. The thing is: you can't ever tell a computer about the number: 1.23. It doesn't truly EXIST for the computer, any more than pi or 1/3 or sqrt2 truly exists in decimal. They're all infinite (or at least undetermined) sequences.

So what you think you're telling the computer when you say "1.23" isn't what you thought. You can't change that unless you can invent a base10 computer. (And the idea has been tried. Higher-base digital computers were attempted in the early days of circuitry.)

And Rich's observation is correct. It's possible to lose fidelity with repeated NotesItem translation to things other than Double.

Jerry (03/09/2009 11:10 AM)

Thanks Nathan, and Rich, for the information - it helped being able to explain the root of the issue to my CEO this morning. Getting our customer to accept the explanation will be another story!

Richard Schwartz (03/10/2009 10:44 PM) website / e-mail

Jerry, Why does your customer need to accept anything? He should never see the problem, because your code does the rounding.

Jerry (03/11/2009 07:48 AM)

Normally, absolutely correct you are. In this case, there was (at present root cause not completely identified) a case where this error manifested in a penny error in a long series of calculations. We ran into the rounding and precision issue while troubleshooting.

So, in this particular instance, the horse has left the barn and getting it back will entail some public spectacle, eg explanation. We're still looking at whether it's the floating point precision problem or the document item precision problem. We have exposure to both in some lengthy routines.

As Nathan said, Excel is famous for accounting, Notes is not. :-)

Jerry (03/11/2009 10:51 AM)

Adding fuel to the now raging fire.... A couple of us simultaneously spotted this in the Notes help.

Round returns a Double.

If the first non-significant digit is 5, and all subsequent digits are 0, the last significant digit is rounded to the nearest even digit. See the example that follows.

Statistically, this is apparently a good thing with large lists of numbers. Rule-set wise, it is of course incorrect math.

You can see this by running the following code snippet.

Msgbox Round(202.5,0) ' returns 202, this is technically wrong but consistent with how Round works.
Msgbox Round(203.5,0) ' returns 204, this is correct
Msgbox Round(8192.175,2) ' returns 8192.17, this is wrong, as above.
Msgbox Round(8192.1751,2) ' returns 8192.18, this is correct

So - even rounding won't guarantee the expected result! The solution we'll probably have to implement is a custom rounding function written by one of our other Sr. Consultants here a while back which will round in a way that humans everywhere expect.

The wicket, it is sticky.

Nathan T. Freeman (03/13/2009 09:27 AM) website / e-mail

Jerry, again, this is complete standard behavior as defined by IEEE754-2008. {Link}

"The standard defines five rounding algorithms. The first two round to a nearest value; the others are called directed roundings:

* Round to nearest, ties to even – rounds to the nearest value; if the number falls midway it is rounded to the nearest value with an even (zero) least significant bit, which occurs 50% of the time; this is the default algorithm for binary floating-point and the recommended default for decimal
* Round to nearest, ties away from zero – rounds to the nearest value; if the number falls midway it is rounded to the nearest value above (for positive numbers) or below (for negative numbers)
* Round toward 0 – directed rounding towards zero (also called truncation)
* Round toward +\infty – directed rounding towards positive infinity
* Round toward -\infty – directed rounding towards negative infinity.
"

But I'm confused. Why not just switch the LS processing to Currency data types and be done with it?

Jerry (03/13/2009 09:37 AM)

Thanks Nathan. I understand that computer science and engineering standards have set down rules for this stuff, it's just not correct math. Just about everyone seems to agree, .5 rounds up, .4 rounds down - always (except with computers).

So, it's not that we can't solve the problem with the currency data type. It's that we have a customer who is worried about the accuracy of certain calculations and desires an understanding - which entails us explaining it. I'm sure with sufficient time and restored confidence for the long time customer, we'll get this all working properly.

I do appreciate your help and effort to educate myself and my coworkers on this issue. Your information has been key to helping us work through it. The finer points of floating point math are sadly something some of us haven't had to cope with to this degree before.

Nathan T. Freeman (03/13/2009 10:19 AM) website / e-mail

I guess I keep coming back to the assertion that "it's not correct math." The rules for mathematically modeling include all the rounding options.

{Link}

The rule taught in grade school is a standard that's used because it's easy, not because it's somehow more "correct." There are lots of standards used all over the world in different use-cases for rounding.

{Link}

Excel is not the be-all, end-all of the definition of correct math.

Jerry (03/13/2009 10:32 AM)

So the right answer is to know the use case. For accounting, use the "standard grade school" rounding method - aka accounting software, excel, and the Currency data type.

For statistics, which are more interested in avoiding a bias towards rounding up, use floating point math with Doubles as commonly found in computers everywhere where accounting and currency are not specified... because computers are for science first and book-keeping second. :-)

Nathan T. Freeman (03/13/2009 10:39 AM) website / e-mail

Well, the rules are even different for different accounting purposes, from what I can tell. GAAP doesn't use the same approach as Tax accounting, from my reading. (And I bet public sector accounting uses a different standard too, where the numbers are rounded in the direction that makes the bureaucrat look better.)

But in the case, it seems like "use Currency data types for Currency calculations" seems like a good rule. And maybe one that we should all be saying "duh" about. :-)

Now, a native Currency data type for NoteItems would be great. We should ask IBM about that.

Richard Schwartz (03/13/2009 08:41 PM) website / e-mail

Jerry -- I even disagree about the "standard grade school" assertion, because I was taught round five to the even number as the rule when I was in grade school! I was even taught the reason in simple, elementary school terms that I can still remember almost verbatim today: zero doesn't round, and that leaves 9 digits that do round. To make it fair, four of them (1, 2, 3, and 4) round down, four of them (6, 7, 8, and 9) round up, and the one in the middle rounds up half the time and down half the time.

And for those who still didn't believe this was the right way to do rounding, there was a simple lesson that drove it home: 1+2+3=6; and 2+3+4=9. If you have 1.5+2.5+3.5, common sense says the answer has to be between 6 and 9, but if you always round the .5 up you get 9, or if you always roudn the .5 down you get 6. Neither of those can possibly be the best answer. The correct unrounded answer is of course 7.5, so your rounded answer had better be 7 or 8, and the round-to-the-even rule gives 8.

Now, it may be true that more elementary schools teach the accounting method today rather than the mathematically preferred method, but I blame that on the dumbing down of school curriculums. The round-to-the-even-number rule, when properly explained is simple enough for a 4th or 5th grader, and it really is a matter of common sense. The only reasons that "humans everywhere" believe the other method is correct are because they were not taught correctly, and because accountants always want you to pay 9 cents instead of 8.

Years ago I thought about implementing a decimal arithmetic LSX for LotusScript. Never did it, but now that they're supposedly finally re-issuing the LSX toolkit maybe I'll give it a try.

Jerry (03/13/2009 09:45 PM)

@ Rich - wow! Now that's useful stuff. Thanks!

It also confirms the public school I attended did, in fact, suck big time.

I think your idea for a decimal arithmetic LSX is a good one, but you'd need to reeducate a bunch of us mathematic rubes so we'd know when to use it. Yeah - dumbing down is right. I wish they would have taught math in the 70's and 80's the way they taught it in the 50's and 60's, or earlier. A whole generation probably robbed of a proper understanding of rounding of all things!