Arithmetic issue

[Glasgow]

I have a program that pulls data from a spreadsheet, does a heap load of calculations then populates a database table with the results.

This has been working for months no problem but this morning it crashed with a SQL 'Multi-step operation generated errors' message. This error usually crops up when the database field format is not appropriate to accept the incoming data. The offending database field was defined as decimal(12,4) but the value the program was trying to put into it was 3.00000000000011E-02. As soon as I used the CDbl function to convert, it ran through OK.

This would appear to have been the result of of a simple calculation (58.75 - 58.72). I have come across this kind of thing before but not for years. What causes this kind of minute discrepancy? The spreadsheet itself does not appear to contain any 'odd' values - i.e. they appear to be presented and held to 2 decimal places. My CDbl fix is a satisfactory workaround but it worries me when else this kind of 'unpredictable' problem could crop up.

 

[MajP]

Google floating point arithmetic for information on this subject.

http://en.wikipedia.org/wiki/Floating_point

Floating point precision errors are most noticeable in subtraction of numbers that are very close together. i.e
58.75 and 58.72

 

[Glasgow]

Thanks.

Indeed this code:

Dim fred As Double
fred = 58.75 - 58.72

yields 3.00000000000011E-02 in the variable fred.

Any suggestions for how to code around this?

 

[MajP]

There are a lot of strategies out there. Not sure how you import your data. Most strategies involve setting a standard amount of precision to and rounding or fixing the data to that precision. However, like you say data type conversions may solve the issue. VB has the decimal type variable which solves some of these issues. Here is a demo

Public Sub testVarType()
  Dim x As Double
  Dim y As Double
  Dim a As Variant
  Dim b As Variant
  x = 58.75
  y = 58.72
  a = 58.75
  b = 58.72
  'co
  Debug.Print x - y
  Debug.Print Round((x - y), 14)
  'strategy 2 convert a  variant to decimal
  Debug.Print a - b
  Debug.Print CDec(a) - CDec(b)
End Sub

Output
 3.00000000000011E-02 
 0.03 
 3.00000000000011E-02 
 0.03

here is a good discussion

http://groups.google.com/group/microsoft.public.excel/msg/7bcfb52bf32ebd93

This is just scratching the surface, there are a lot of much more complex algorithms for handling floating point errors.

 

[MajP]

Also the round function in vb "opens a can of worms". You may want to google this subject for better functions.

 

[SBerthold]

Try using a single
or

fred = CDec(58.75 - 58.72)

 

[SBerthold]

oops

Try using a single
or

fred = CDec(58.75) - CDec(58.72)

 

[MajP]

Try using a single

No, that will not make a difference. Still floating point arithmetic.