Answered by:
rounding bug in vfp8?
Question

I have found what seems to be a rounding bug in VFP8. Any ideas how I can get around this please?
I was doing some testing when I found that I was a penny out on digging deeper it seems that when lnValue = 0.57500000000000, Round(lnValue,2) gives 0.57!!!!! when it should give 0.58. This is causing my validation to fail. Is this a bug?
The strange thing is that Round(.575000000000,2) gives 0.58 but the extra 0's in the scenario above are causing the difference.
Friday, July 21, 2006 12:28 PM
Answers

Hi Darren
>> The strange thing is that Round(.575000000000,2) gives 0.58 but the extra 0's in the scenario above are causing the difference
I think that this is a limitation of VFP's floating point number implementation. As stated in the help file (in respect of the numeric data type):
When storing floatingpoint numbers in Numeric fields, numeric precision is limited to approximately 15 digits in Visual FoxPro.
The number you are testing is "correct" when it has 13 decimal places (i.e. 15 digits including the "0.") but goes wrong as soon as there are more than 15 digits total.
One solution is to limit the maximum length of a result to no more than 13 decimal places. You could do this by using the STR() function:
lnVal1 = 0.57500000000000
? ROUND( lnVal1, 2 ) && 0.57
? ROUND( VAL( STR( lnVal1,15, 13)), 2) && 0.58Note, using STR( nnn, 15, 13 ) does not force VFP to lose any significant digits. It will always preserve the significant digits and limit the number of decimal places to a MAXIMUM of 13. Thus:
lnVal2 = 12345.57500000000000
? ROUND( lnVal2, 2 ) && 12345.57
? ROUND( VAL( STR( lnVal1,15, 13)), 2) && 12345.58
Saturday, July 22, 2006 11:53 AM
All replies

same problem also in vfp6 and vfp9...
Friday, July 21, 2006 12:48 PM 
Darren,
Since you're dealing with 2 decimals only this might be used as a workaround:
? round( ntom( m.lnValue ), 2 )
Friday, July 21, 2006 1:24 PM 
Hi Darren
>> The strange thing is that Round(.575000000000,2) gives 0.58 but the extra 0's in the scenario above are causing the difference
I think that this is a limitation of VFP's floating point number implementation. As stated in the help file (in respect of the numeric data type):
When storing floatingpoint numbers in Numeric fields, numeric precision is limited to approximately 15 digits in Visual FoxPro.
The number you are testing is "correct" when it has 13 decimal places (i.e. 15 digits including the "0.") but goes wrong as soon as there are more than 15 digits total.
One solution is to limit the maximum length of a result to no more than 13 decimal places. You could do this by using the STR() function:
lnVal1 = 0.57500000000000
? ROUND( lnVal1, 2 ) && 0.57
? ROUND( VAL( STR( lnVal1,15, 13)), 2) && 0.58Note, using STR( nnn, 15, 13 ) does not force VFP to lose any significant digits. It will always preserve the significant digits and limit the number of decimal places to a MAXIMUM of 13. Thus:
lnVal2 = 12345.57500000000000
? ROUND( lnVal2, 2 ) && 12345.57
? ROUND( VAL( STR( lnVal1,15, 13)), 2) && 12345.58
Saturday, July 22, 2006 11:53 AM 
CetinBasoz wrote: Darren,
Since you're dealing with 2 decimals only this might be used as a workaround:
? round( ntom( m.lnValue ), 2 )
Thank you Cetin. I have also found that Round(Round(m.lnValue,3),2) works. Obviously on the same principle.
Monday, July 24, 2006 9:20 AM 
giuln wrote: same problem also in vfp6 and vfp9...
Thank you for the confirmation giuln.
Monday, July 24, 2006 9:20 AM 
Thank you Andy for your full explanation and suggestions.
In case anyone else is following this thread I think In practice you might be able to get away with shortening that slightly by removing the round altogether as the STR & VAL combination rounds to 2 DP anyway. I think this does depend upon the SET DECIMALS however.
When I actually traced back my production code to see how I came to such a ridiculous scenario in the first place, it seems that I had a discount percentage being pulled from a table as 2.5000000000 so the easy solution is for me to just round that to 2 dp in the first place.
Thanks for the help Guys. This has been an interesting journey for me.
Regards
Darren
Monday, July 24, 2006 9:32 AM 
Not all solutions works, check your solution for these values:
Some value that cause incorrect round() results
? round(7.00*120.437500*8.00/100.00,2)
? round(1.0050000000000,2)
? round(31.935*305,2)
? round(336.874996,2)
? round(0.57500000000000,2)
function roundOK(nValue,nDec)
local cValue,nDotPos,cRet
cValue = transf(nValue,'99999999999999.999999999')
nDotPos = at('.',cValue)
if substr(cValue,nDotPos+nDec+1,1)>='5'
cRet = substr(cValue,1,nDotPos+nDec)+'9'
else
cRet = substr(cValue,1,nDotPos+nDec)+'0'
endif
return round(val(cRet),nDec)Wednesday, May 22, 2019 5:19 PM 
This works on the premise transform makes perfect conversions from float to string representation.
You already get floating point imprecisions when converting from the decimal representation of numbers in source code or input from textbox values to numeric fields or variables.
SET DECIMALS TO 18 is the maximum allowed and almost all randomly generated numbers don't convert back to the original string.
Even worse than necessary, if you don't push up SET DECIMALS. In its default setting of 2 your transform won't even convert 0.999 correctly:
? transf(0.999,'99999999999999.999999999')
I get 1.000000000, even though .999 is far off the precision problem of floats.
You don't get rid of the problem for any case, you'll always end up with corner cases not working, even after you SET DECIMALS TO 18.
Bye, Olaf.
Wednesday, May 22, 2019 8:33 PM 
Not all solutions works, check your solution for these values:
Some value that cause incorrect round() results
? round(7.00*120.437500*8.00/100.00,2)
? round(1.0050000000000,2)
? round(31.935*305,2)
? round(336.874996,2)
? round(0.57500000000000,2)
function roundOK(nValue,nDec)
local cValue,nDotPos,cRet
cValue = transf(nValue,'99999999999999.999999999')
nDotPos = at('.',cValue)
if substr(cValue,nDotPos+nDec+1,1)>='5'
cRet = substr(cValue,1,nDotPos+nDec)+'9'
else
cRet = substr(cValue,1,nDotPos+nDec)+'0'
endif
return round(val(cRet),nDec)Why would you say that? You forgot to include NTOM() in all samples.
How to create a Minimal, Reproducible Example
The way to Go.
World's most advanced open source (object) relational Database.Wednesday, May 22, 2019 10:20 PM