7

I have this obscure rounding problem in VBA. 

a = 61048.4599674847
b = 154553063.208822
c = a + b   
debug.print c
Result: 
154614111.66879

Here is the question, why did VBA rounded off variable c? I didn't issued any rounding off function. The value I was expecting was 154614111.6687894847. Even if I round off or format variable c to 15 decimal places I still don't get my expected result.

Any explanation would be appreciated.

Edit:

Got the expected results using cDec. I have read this in Jonathan Allen's reply in Why does CLng produce different results?

Here is the result to the test:

a = cDec(61048.4599674847)
b = cDec(154553063.208822)
c = a + b
?c
154614111.6687894847
Community
  • 1
  • 1
Grekoz
  • 275
  • 1
  • 6
  • 14
  • 1
    [Floating-point arithmetic may give inaccurate results in Excel](http://support.microsoft.com/kb/78113) – SeanC Nov 12 '12 at 18:18
  • well heres the thing right.. if you want to be able to add very very very very large numbers in excel with tons of dp, you have to introduce a little bit of imprecision. sorry to say. what you are doing is very very basic maths. which the decimal datatype will treat you well. – hamish Nov 23 '22 at 03:48

2 Answers2

13

The reason is the limited precission that can be stored in a floating point variable.
For a complete explanation you shoud read the paper What Every Computer Scientist Should Know About Floating-Point Arithmetic, by David Goldberg, published in the March, 1991 issue of Computing Surveys.

Link to paper

In VBA the default floating point type is Double which is a IEEE 64-bit (8-byte) floating-point number.

There is another type available: Decimal which is a 96-bit (12-byte) signed integers scaled by a variable power of 10
Put simply, this provides floating point numbers to 28 digit precission.

To use in your example:

a = CDec(61048.4599674847)
b = CDec(154553063.208822)
c = a + b   
debug.print c
Result: 
154614111.6687894847
chris neilsen
  • 52,446
  • 10
  • 84
  • 123
3

Its not obscure, but its not necessarily obvious.

I think you've sort of answered it - but the basic problem is one of the "size" of the values that is how much data can be stored in a variable of a given type.

If (and this is very crude) you count the number of digits in each of the numbers in your first example you will see that you have 15 so whilst the range of values that a float (the default type) can represent is huge the precision is limited to 15 digits (I'm sure someone will be along to correct this, I'll tick the wiki box...)

So when you add the two numbers together it loses the least significant values in order to remain within the allowable precision for a flow.

By doing a cDec you're converting to a different type of variable (decimal) that is capable of greater precision

Murph
  • 9,985
  • 2
  • 26
  • 41