Rounding Problems 1

[tryn2learn]

Help, this is making no sense! I’m having a problem with the round() function. I’m running visual 6.0. My code is like the following where salary is a variable (N 10 2) and fields a, b & c are all defined as numeric 10 2

Select a, b, c from table one into cursor query
Amount = query.a + ((query.b /100) * (salary – query.c))
Rnd_amt = round(amount,2)

However, if amount = 279.77500000 then the result of rnd_amt comes out as 279.77! But, sometimes it will work. For instance, if amount is 76.97500000 the rnd_amt comes out as 76.98, which is what I expect. If the thousandths position is a 6 it rounds up each time, but when it is a 5 it seems to me that it depends on the phase of the moon or something!

My set decimals is set to 8. Rnd_amt is initialized to 0.00000000

 

[craigsboyd]

lspeaks,

Sorry, couldn't reproduce what you are seeing. Tried a bunch of different things and couldn't get it to round 279.775 incorrectly, always came out to 279.78

Slighthaze = NULL

 

[tryn2learn]

It seems to work okay if I run it from the command window, but if I run my program then it gives incorrect results.

 

[darrellblackhawk]

Very strange! I can't reproduce your problem either.

I thought first it might be one of the environment settings,
but I tried many variants to no avail.

Not sure how to advise you. Maybe you have some type of
processor problem.

Darrell


'We all must do the hard bits so when we get bit we know where to bite'

 

[agit72]

Ispeaks,
I have this kind of experiennce, and I've notice that
the problem appears when it's involve a form...
and that form have a "Private Data Session"...

so beware of "Private Data Session"...


Agit Permana (08561052915)
Mitra Solusi Pratama, PT

http://www.msp-works.com

 

[Mike Lewis]

Agit,

Ispeaks,
I have this kind of experiennce, and I've notice that
the problem appears when it's involve a form...
and that form have a "Private Data Session"...

That would suggest that the culprit is one of the SET settings that is scoped to the current data session, such as SET DECIMALS or SET FIXED.

so beware of "Private Data Session"...

Isn't that a bit draconian? It would be better to find and fix the problem, rather than go without all the many benefits of private data sessions.

Mike




Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[agit72]

Mike Lewis,

My mistake, I just want to remind Ispeaks, the behave of
Private Data Session.

Thanks Mike.

Agit Permana (08561052915)
Mitra Solusi Pratama, PT

http://www.msp-works.com

 

[tryn2learn]

It turned out that I didn't have the service pack 5 installed. Once I did that the numbers rounded as expected.

 

[benlau]

Hi All,

I have this kind of experiennce, I use VFP9 and SET DECIMALS TO 18, my question is when I try to calc 7539727.5 / 100 and round to 2 decimals, I get 75397.27, I found the error was came from " / 100", because after "/ 100" the answer as 75397.27499999999 so round to 2 decimals get 75397.27, I think this might be a bug and I have no idea how to solve this.

 

[Mike Lewis]

Benlau,

Not sure about this, but I suspect it's a floating-point round-off issue. It's not really a bug. It's a consequence of the way that floating point numbers work.

When you do arithmetic with a very high precision, you inevitably get round-off errors. The solution is to round the result to an acceptable number of places.

This behaviour is not confined to Visual FoxPro. It's inherent in the floating point system. Think of it as the price you pay for being able to store very large and very small numbers in a very few number of bytes.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[Olaf Doschke]

Hi Mike,

Fully acknowledged.

Hi benlau,

this is no bug, simply a limitation of floating point. Although fields like N(10,2) do save the values as strings internal, if you calculate something numbers get conversed to floating point, so even simple calculations can have slight differences compared to the correct result due to the limits of floating point numbers, which lead to false rounding.

Even doing the /100 operation as a string operation and then converting with val will get the same error:

set decimals to 18
? val("75397.275")
&& result 75397.2749999...

So you have to invent a round function that works with strings: round("75397.275",2)="75397.28", and then convert back with Val("75397.28"). This works in this case, but you always risk the effect, that such results convert back fals (eg to 75397.2799999...) with VAL.

That's the nature of floating point numbers. You see them in decimal format, but they are stored in the dual system, which is capable to store exact 1/2,1/4,1/8 etc., but 0.275 is 1/4+1/40 and 1/40 is a finite decimal number 0.025, but infinite as a sum of power-of-two numbers 1/64+1/128+1/1024+1/2048+... or written in the dual system 0.00000110011001100110011001100110011... with 0011 repeating endless. That can't be stored in a finite number of bytes, so it's rounded.

The recommendation is to store values with some decimal places more than you need and just round end results, maybe even on a string level. And if you are on the string level, then you have to stay there to not risk conversion errors.

There are also classes, that work with strings in calculations. Search in the universal thread for "math unlimited".

 

[DSummZZZ]

For a decent visual representation of how all this sort of ticks, take a look at this FAQ, more particularly, cut/paste and run the code in items 16 and 17:

Conversion Functions: Dec,Hex,Binary,Oct Back and Forth
faq184-4461

-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[Olaf Doschke]

Hi Dave,

nice FAQ.

Another tip: Because of these rounding errors of float values there are certain rules for working with such numbers. The simplest rule is for a sum of many numbers varying from very large to very small values, it's better to sort the values by ABS(value) and then sum these up in ascending order, as the sums of many small numbers may sum up to something larger which affects the total sum at least a little compared to starting with a very large number, for which each summation of a very small number simply results in the large number unchanged.

=Rand(-1)
Set Decimals To 18
Create cursor curFloats(nNumber F(16,14))
For i = 1 to 10000
Insert into curFloats values (Rand()*2*(Rand()-.5))
EndFor i
Set Talk Off 
Calculate Sum(nNumber) to nCalcSum
Select nNumber, Abs(nNumber) from curFloats ORDER BY 2 Into Cursor curSortedFloats Nofilter
Select curSortedFloats
Calculate Sum(nNumber) to nCalcOrderedSum
Clear
? nCalcOrderedSum
? nCalcSum
? nCalcOrderedSum-nCalcSum

You'll mostly have a difference in the two results, nCalcOrderedSum being more precise.

Bye, Olaf.

 

[mmerlinn]

Actually it is a limitation of the decimal system. Computers use binary numbers which are all multiples of 2 represented by turning bits (switches) in the machine either ON or OFF.

The decimal system is based on multiples of 2 & 5 and there is no way that the five can be represented (or any other number not evenly divisible by 2) easily and simply for the computer to understand. No one yet has found a way to make a 5-way switch in a processor chip.

Floating point numbers are the work-around, and as long as you don't need extremely precise numbers (that is, numbers containing a lot of significant digits), floating point works just fine.

If you need extremely precise numbers, the only way to consistantly get them is to use assembly language and binary numbers, by writing your own math routines at the machine language level.s

 

[Mike Lewis]

mmerlinn,

If you need extremely precise numbers, the only way to consistantly get them is to use assembly language

Hmm. I don't dispute your analysis, but I don't see how the choice of language affects the ability to work with "extremely precise numbers". Surely, what's important is being able to manipulate individual bits. Assembly language isn't a pre-requisite for that.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[mmerlinn]

Mike:

You are correct in stating that using assembly language is not required to manipulate individual bits.

The problem in using other languages is that it is very cumbersome and slow to manipulate the massive numbers of bits needed to process extremely precise numbers. Such numbers contain hundreds of bits, each of which must be manipulated one at a time in every computation. The end result is that this is very slow in higher languages, even when they are compiled.

mmerlinn

 

[Olaf Doschke]

MMerlin,

Another way to work with large numbers/high precision is working with strings. That needs more moemory, but almost every language has strong and fast string operation commands. See "math unlmited" I pointed to in universal thread. That's done in VFP. And it's not true that you have to work bit by bit. High precision computing libraries often work with fixed point arithmetic and integers that put together make up the real number. A partial summation then works with 32 bits at a time, not only single bits.

Bye, Olaf.

 

[dbMark]

Years ago I read that programs like dBase, and I presume FoxPro too, calculated numeric valus out to 18 digits but compared numbers out to only 15 digits since the last 2 or 3 digits could include small calculation variances. As mentioned above it was due to the design and implementation of floating point calculaions. I don't know if VFP does it any more precise.

 

[mmerlinn]

Olaf,

Never thought of that, and since I don't work with precise numbers, it has never been a real issue for me.

I only ran into this problem on my first computer, an Apple II+. Even simple computations, like 200/4, sometimes gave answers like 49.9999999999 at that time. The only way I could make numbers work correctly all of the time was to do my own computations at the machine language level, as Apple Basic at 1MHz was not capable of doing it any other way in a reasonable amount of time.

However, I can see that using strings and partial calculations would indeed speed things up in the higher languages. So, if I ever need to manipulate extremely precise numbers, I will consider using strings and partial computations in a higher language. Probably beats tearing my hair out trying to debug Assembly language routines, and definitely beats having to track and manipulate hundreds of bits at a time.

mmerlinn

 

[Evbcs]

Try the syntax

Rnd_amt = VAL(STR( round(amount,2),11,2))