## Rounding Problems

## Rounding Problems

(OP)

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

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

## RE: Rounding Problems

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

## RE: Rounding Problems

## RE: Rounding Problems

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'

## RE: Rounding Problems

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

## RE: Rounding Problems

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

www.ml-consult.demon.co.uk

## RE: Rounding Problems

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

## RE: Rounding Problems

## RE: Rounding Problems

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.

## RE: Rounding Problems

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)

## RE: Rounding Problems

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:

## CODE

? 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".

## RE: Rounding Problems

Conversion Functions: Dec,Hex,Binary,Oct Back and Forth

FAQ184-4461

-Dave Summers-

Even more Fox stuff at:

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

## RE: Rounding Problems

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.

## CODE

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.

## RE: Rounding Problems

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

## RE: Rounding Problems

mmerlinn,

If you need extremely precise numbers, the only way to consistantly get them is to use assembly languageHmm. 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)

## RE: Rounding Problems

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

## RE: Rounding Problems

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.

## RE: Rounding Problems

## RE: Rounding Problems

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

## RE: Rounding Problems

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

## RE: Rounding Problems

Try the following:

? VAL(STR( round(75397.275,2),11,2)) produces 75397.28

? VAL(STR( round(75397.2750000000,2),11,2)) produces 75397.27