Something wrong adding numbers

[Guillermo_Lopez]

Hello All,

I am using VBA in access to perform some calculations. There is a
particular sumation that is wrong (barely).

this code is withing a loop.

TDist = TDist + TempDist

Both TDist and TempDist are declared as doubles:

Dim TDist As Double
Dim TempDist As Double

After looping through 7000+ iterations the following addition produces
this result
These are the exact values of the variables:
TDist = 6388254.993
TempDist = 36.334

When: TDist = TDist + TempDist

TDist = 6388291.3269999 9

TDist is suppose to be 6388291.327

Although the difference is negligable and a simple Format would fix
it. I noticed it only happened here, I can't reproduce it in the
Immediate window.

I am worried if it's going to generate a much larger error. And when
its accumulative, it tends to grow errors like this, especially when
running 200k iterations or more.

Is this something anybody has experienced?
Is there a solution to prevent this error?
Or is this just some highly unlikely situation that occured to me for
a very remote and Highly Unlikely combination of data? (Sounds alot
like Douglas Adams)

- GL

[Rick Brandt]

Re: Something wrong adding numbers

Guillermo_Lopez wrote:

Hello All,
>
I am using VBA in access to perform some calculations. There is a
particular sumation that is wrong (barely).
>
this code is withing a loop.
>
TDist = TDist + TempDist
>
Both TDist and TempDist are declared as doubles:
>
Dim TDist As Double
Dim TempDist As Double
>
After looping through 7000+ iterations the following addition produces
this result
These are the exact values of the variables:
TDist = 6388254.993
TempDist = 36.334
>
When: TDist = TDist + TempDist
>
TDist = 6388291.3269999 9
>
TDist is suppose to be 6388291.327
>
Although the difference is negligable and a simple Format would fix
it. I noticed it only happened here, I can't reproduce it in the
Immediate window.
>
I am worried if it's going to generate a much larger error. And when
its accumulative, it tends to grow errors like this, especially when
running 200k iterations or more.
>
Is this something anybody has experienced?
Is there a solution to prevent this error?
Or is this just some highly unlikely situation that occured to me for
a very remote and Highly Unlikely combination of data? (Sounds alot
like Douglas Adams)
>
- GL

Floating point numbers in computers are imprecise because they are stored as
binary. If you Google on the topic you should find tons of sites describing
the issue. You have to code around that or use inexact comparisons rather
than testing for equality.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Stuart McCall]

Re: Something wrong adding numbers

"Guillermo_Lope z" <g.lopez@iesdr. comwrote in message
news:52a2b731-332b-47a7-9b37-fadefbdaa704@y3 8g2000hsy.googl egroups.com...

Hello All,
>
I am using VBA in access to perform some calculations. There is a
particular sumation that is wrong (barely).
>
this code is withing a loop.
>
TDist = TDist + TempDist
>
Both TDist and TempDist are declared as doubles:
>
Dim TDist As Double
Dim TempDist As Double
>
After looping through 7000+ iterations the following addition produces
this result
These are the exact values of the variables:
TDist = 6388254.993
TempDist = 36.334
>
When: TDist = TDist + TempDist
>
TDist = 6388291.3269999 9
>
TDist is suppose to be 6388291.327

<SNIP>

Try using the Round function:

Debug.Print Round(TDist + TempDist, 3)

Result: 6388291.327

[Guillermo_Lopez]

Re: Something wrong adding numbers

On Apr 24, 8:22 pm, "Stuart McCall" <smcc...@myunre albox.comwrote:

"Guillermo_Lope z" <g.lo...@iesdr. comwrote in message
>
news:52a2b731-332b-47a7-9b37-fadefbdaa704@y3 8g2000hsy.googl egroups.com...
>
>
>

Hello All,

>

I am using VBA in access to perform some calculations. There is a
particular sumation that is wrong (barely).

>

this code is withing a loop.

>

       TDist = TDist + TempDist

>

Both TDist and TempDist are declared as doubles:

>

   Dim TDist As Double
   Dim TempDist As Double

>

After looping through 7000+ iterations the following addition produces
this result
These are the exact values of the variables:
TDist = 6388254.993
TempDist = 36.334

>

When:      TDist = TDist + TempDist

>

TDist = 6388291.3269999 9

>

TDist is suppose to be 6388291.327

>
<SNIP>
>
Try using the Round function:
>
Debug.Print Round(TDist + TempDist, 3)
>
Result: 6388291.327- Hide quoted text -
>
- Show quoted text -

Curious. I guess i'll have to use Round(TDIst + TempDist,7). I got
into the articles about Floating Point Numbers and their limitations.

Thanks for your help.

- GL

[lyle fairfield]

Re: Something wrong adding numbers

On Apr 25, 4:44 pm, Guillermo_Lopez <g.lo...@iesdr. comwrote:

On Apr 24, 8:22 pm, "Stuart McCall" <smcc...@myunre albox.comwrote:
>
>
>
>
>

"Guillermo_Lope z" <g.lo...@iesdr. comwrote in message

>

news:52a2b731-332b-47a7-9b37-fadefbdaa704@y3 8g2000hsy.googl egroups.com...

>

Hello All,

>

I am using VBA in access to perform some calculations. There is a
particular sumation that is wrong (barely).

>

this code is withing a loop.

>

       TDist = TDist + TempDist

>

Both TDist and TempDist are declared as doubles:

>

   Dim TDist As Double
   Dim TempDist As Double

>

After looping through 7000+ iterations the following addition produces
this result
These are the exact values of the variables:
TDist = 6388254.993
TempDist = 36.334

>

When:      TDist = TDist + TempDist

>

TDist = 6388291.3269999 9

>

TDist is suppose to be 6388291.327

>

<SNIP>

>

Try using the Round function:

>

Debug.Print Round(TDist + TempDist, 3)

>

Result: 6388291.327- Hide quoted text -

>

- Show quoted text -

>
Curious. I guess i'll have to use Round(TDIst + TempDist,7).   I got
into the articles about Floating Point Numbers and their limitations.
>
Thanks for your help.
>
- GL

For many or most non-integer uses currency will solve the problems you
describe.

From the help file.

"Currency variables are stored as 64-bit (8-byte) numbers in an
integer format, scaled by 10,000 to give a fixed-point number with 15
digits to the left of the decimal point and 4 digits to the right.
This representation provides a range of -922,337,203,685 ,477.5808 to
922,337,203,685 ,477.5807. The type-declaration character for Currency
is the at sign (@).

The Currency data type is useful for calculations involving money and
for fixed-point calculations in which accuracy is particularly
important."

Many beginners neglect the second role for currency viz fixed-point
calculations in which accuracy is particularly important, and think,
"Oh this isn't money so I shouldn't use currency". Nine times our of
ten they should use currency.

Of course the choice of the term, "Currency" is the culprit here. So
MS changed the name for MS-SQL Server to Money and SmallMoney (I
believe the second was in anticipation of the plunging American
dollar). Now it's more confusing than ever, but that's Microsoft, eh?

Probably in 90% (yes, same ratio as above) of the cases when floats
are used, they shouldn't be used.

So, I recommend using currency unless ten thousandths are important to
your application.

[Larry Linson]

Re: Something wrong adding numbers

"lyle fairfield" <lyle.fairfield @gmail.comwrote

So, I recommend using currency unless ten thousandths
are important to your application.

And, if you need exact calculations, and tenthousandths or hundredthousant hs
are important, go ahead, use currency, and do your own additional scaling.

Larry Linson
Microsoft Office Access MVP

[Guillermo_Lopez]

Re: Something wrong adding numbers

On Apr 25, 9:48 pm, "Larry Linson" <boun...@localh ost.notwrote:

"lyle fairfield" <lyle.fairfi... @gmail.comwrote
>
 So, I recommend using currency unless ten thousandths
 are important to your application.
>
And, if you need exact calculations, and tenthousandths or hundredthousant hs
are important, go ahead, use currency, and do your own additional scaling.
>
 Larry Linson
 Microsoft Office Access MVP

Thanks, Using currency will definetly save the effort in the loop
calculations.

- GL.

[CDMAPoster@fortunejames.com]

Re: Something wrong adding numbers

On Apr 25, 6:05 pm, lyle fairfield <lyle.fairfi... @gmail.comwrote :

For many or most non-integer uses currency will solve the problems you
describe.
>
From the help file.
>
"Currency variables are stored as 64-bit (8-byte) numbers in an
integer format, scaled by 10,000 to give a fixed-point number with 15
digits to the left of the decimal point and 4 digits to the right.
This representation provides a range of -922,337,203,685 ,477.5808 to
922,337,203,685 ,477.5807. The type-declaration character for Currency
is the at sign (@).
>
The Currency data type is useful for calculations involving money and
for fixed-point calculations in which accuracy is particularly
important."
>
Many beginners neglect the second role for currency viz fixed-point
calculations in which accuracy is particularly important, and think,
"Oh this isn't money so I shouldn't use currency". Nine times our of
ten they should use currency.
>
Of course the choice of the term, "Currency" is the culprit here.  So
MS changed the name for MS-SQL Server to Money and SmallMoney (I
believe the second was in anticipation of the plunging American
dollar). Now it's more confusing than ever, but that's Microsoft, eh?
>
Probably in 90% (yes, same ratio as above) of the cases when floats
are used, they shouldn't be used.
>
So, I recommend using currency unless ten thousandths are important to
your application

Both Double and Currency use 8 bytes so as far as discretization goes
they have the same potential accuracy. The Decimal data type has 14
bytes and therefore has more native potential for finer resolution
with appropriate manipulation. The value of the Currency data type
over Double, in my experience, only shows up in two situations. The
first is to store Currency values in a way that makes it obvious that
the field is about money. The second is in a simple summation. The
Currency data type has no advantage over Double for almost any kind of
calculation beyond that, even multiplication, let alone
exponentiation. Plus, there are ways that are even better than using
the Decimal type for getting increased accuracy for all fixed-point
calculations (not to be confused with fixed-point iteration in
mathematics). Your recommendation is not a bad one, but floats are
not as bad as you depict once you're beyond simple summation.

James A. Fortune
CDMAPoster@Fort uneJames.com

[lyle fairfield]

Re: Something wrong adding numbers

On Apr 29, 1:17 pm, CDMAPos...@fort unejames.com wrote:

Both Double and Currency use 8 bytes so as far as discretization goes
they have the same potential accuracy.

Do they use the bits of the bytes in the same way?

> The Decimal data type has 14
bytes and therefore has more native potential for finer resolution
with appropriate manipulation.

Where do those 14 bytes live? Are they part of a 16 byte variant? Have
you explored how these work?

 The value of the Currency data type
over Double, in my experience, only shows up in two situations.  The
first is to store Currency values in a way that makes it obvious that
the field is about money.  The second is in a simple summation.  The
Currency data type has no advantage over Double for almost any kind of
calculation beyond that, even multiplication, let alone
exponentiation.

Hmmmm. I must be doing something wrong here:

Sub temp()
Dim Message$
Dim Multiplicand As Double
Dim Multiplier As Double
Dim PredictedProduc t As Double
Dim Product As Double

Multiplicand = CDbl(1 / 10)
Multiplier = CDbl(1 / 10)
PredictedProduc t = CDbl(1 / 100)
Product = Multiplier * Multiplicand

If PredictedProduc t = Product Then
Message = " equals "
Else
Message = " doesn't equal "
End If
Debug.Print "Double: " & Product & Message & PredictedProduc t
'Double: 0.01 doesn't equal 0.01
End Sub

Sub temp2()
Dim Message$
Dim Multiplicand As Currency
Dim Multiplier As Currency
Dim PredictedProduc t As Currency
Dim Product As Currency

Multiplicand = CDbl(1 / 10)
Multiplier = CDbl(1 / 10)
PredictedProduc t = CDbl(1 / 100)
Product = Multiplier * Multiplicand

If PredictedProduc t = Product Then
Message = " equals "
Else
Message = " doesn't equal "
End If
Debug.Print "Currency: " & Product & Message & PredictedProduc t
'Currency: 0.01 equals 0.01
End Sub

What is it?

[CDMAPoster@fortunejames.com]

Re: Something wrong adding numbers

On Apr 29, 2:08 pm, lyle fairfield <lyle.fairfi... @gmail.comwrote :

On Apr 29, 1:17 pm, CDMAPos...@fort unejames.com wrote:
>

Both Double and Currency use 8 bytes so as far as discretization goes
they have the same potential accuracy.

>
Do they use the bits of the bytes in the same way?
>

 The Decimal data type has 14
bytes and therefore has more native potential for finer resolution
with appropriate manipulation.

>
Where do those 14 bytes live? Are they part of a 16 byte variant? Have
you explored how these work?
>

 The value of the Currency data type
over Double, in my experience, only shows up in two situations.  The
first is to store Currency values in a way that makes it obvious that
the field is about money.  The second is in a simple summation.  The
Currency data type has no advantage over Double for almost any kind of
calculation beyond that, even multiplication, let alone
exponentiation.

>
Hmmmm. I must be doing something wrong here:
>
Sub temp()
Dim Message$
Dim Multiplicand As Double
Dim Multiplier As Double
Dim PredictedProduc t As Double
Dim Product As Double
>
Multiplicand = CDbl(1 / 10)
Multiplier = CDbl(1 / 10)
PredictedProduc t = CDbl(1 / 100)
Product = Multiplier * Multiplicand
>
If PredictedProduc t = Product Then
    Message = " equals "
Else
    Message = " doesn't equal "
End If
Debug.Print "Double: " & Product & Message & PredictedProduc t
'Double: 0.01 doesn't equal 0.01
End Sub
>
Sub temp2()
Dim Message$
Dim Multiplicand As Currency
Dim Multiplier As Currency
Dim PredictedProduc t As Currency
Dim Product As Currency
>
Multiplicand = CDbl(1 / 10)
Multiplier = CDbl(1 / 10)
PredictedProduc t = CDbl(1 / 100)
Product = Multiplier * Multiplicand
>
If PredictedProduc t = Product Then
    Message = " equals "
Else
    Message = " doesn't equal "
End If
Debug.Print "Currency: " & Product & Message & PredictedProduc t
'Currency: 0.01 equals 0.01
End Sub
>
What is it?

Your example is multiplying money by money, a bad idea :-). I agree
that constants with only a few significant figures preserve the
benefits of the Currency data type. I think more precise constants do
not. Let's see:

Sub temp3()
Dim Message$
Dim Multiplicand As Currency
Dim Multiplier As Currency
Dim PredictedProduc t As Currency
Dim Product As Currency

Multiplicand = CCur(35.67)
Multiplier = CCur(0.2311)
PredictedProduc t = CCur(8.243337)

Product = Multiplier * Multiplicand

If PredictedProduc t = Product Then
Message = " equals "
Else
Message = " doesn't equal "
End If
Debug.Print "Currency: " & Product & Message & PredictedProduc t
End Sub

Currency: 8.2433 equals 8.2433

but the true value is still 8.243337, so the Currency data type isn't
doing any better than rounding a Double. In fact, it's doing much
worse.

The six extra bytes (48 bits) of the Decimal data type represent a
potential about 13 extra significant figures (+/- 1 / 2 ^ 48 = +/-
3.55 x 10 ^ -15) of accuracy.

Your argument seems to be that the Currency data type, because of the
particular scaling used, is superior to the Double data type, which
can also, BTW, be rescaled. I agree that there's a possibility that
the bits aren't used optimally in any of the data types. Why not
scale the range so that you can always get, say, eight places past the
decimal point and keep the Currency value under 92 billion?
Personally, I'll keep using Double's or even Decimal's whenever
possible for money calculations, then store the result as Currency.
If those options aren't accurate enough I'll roll my own mantissa
separately using available data types. Beyond that, I'd have to
finish my so-called infinite precision string calculation code.
Anyway, I still see little reason to use the Currency data type for
anything besides storage of money amounts and simple summations of
Currency values. This is an interesting subject so later I might
delve more into the representations of the data types and the
tradeoffs made.

James A. Fortune
CDMAPoster@Fort uneJames.com

[Guillermo_Lopez]

Re: Something wrong adding numbers

On Apr 29, 5:37 pm, CDMAPos...@fort unejames.com wrote:

On Apr 29, 2:08 pm, lyle fairfield <lyle.fairfi... @gmail.comwrote :
>
>
>
>
>

On Apr 29, 1:17 pm, CDMAPos...@fort unejames.com wrote:

>

Both Double and Currency use 8 bytes so as far as discretization goes
they have the same potential accuracy.

>

Do they use the bits of the bytes in the same way?

>

> The Decimal data type has 14
bytes and therefore has more native potential for finer resolution
with appropriate manipulation.

>

Where do those 14 bytes live? Are they part of a 16 byte variant? Have
you explored how these work?

>

 The value of the Currency data type
over Double, in my experience, only shows up in two situations.  The
first is to store Currency values in a way that makes it obvious that
the field is about money.  The second is in a simple summation.  The
Currency data type has no advantage over Double for almost any kind of
calculation beyond that, even multiplication, let alone
exponentiation.

>

Hmmmm. I must be doing something wrong here:

>

Sub temp()
Dim Message$
Dim Multiplicand As Double
Dim Multiplier As Double
Dim PredictedProduc t As Double
Dim Product As Double

>

Multiplicand = CDbl(1 / 10)
Multiplier = CDbl(1 / 10)
PredictedProduc t = CDbl(1 / 100)
Product = Multiplier * Multiplicand

>

If PredictedProduc t = Product Then
    Message = " equals "
Else
    Message = " doesn't equal "
End If
Debug.Print "Double: " & Product & Message & PredictedProduc t
'Double: 0.01 doesn't equal 0.01
End Sub

>

Sub temp2()
Dim Message$
Dim Multiplicand As Currency
Dim Multiplier As Currency
Dim PredictedProduc t As Currency
Dim Product As Currency

>

Multiplicand = CDbl(1 / 10)
Multiplier = CDbl(1 / 10)
PredictedProduc t = CDbl(1 / 100)
Product = Multiplier * Multiplicand

>

If PredictedProduc t = Product Then
    Message = " equals "
Else
    Message = " doesn't equal "
End If
Debug.Print "Currency: " & Product & Message & PredictedProduc t
'Currency: 0.01 equals 0.01
End Sub

>

What is it?

>
Your example is multiplying money by money, a bad idea :-).  I agree
that constants with only a few significant figures preserve the
benefits of the Currency data type.  I think more precise constants do
not.  Let's see:
>
Sub temp3()
Dim Message$
Dim Multiplicand As Currency
Dim Multiplier As Currency
Dim PredictedProduc t As Currency
Dim Product As Currency
>
Multiplicand = CCur(35.67)
Multiplier = CCur(0.2311)
PredictedProduc t = CCur(8.243337)
>
Product = Multiplier * Multiplicand
>
If PredictedProduc t = Product Then
    Message = " equals "
Else
    Message = " doesn't equal "
End If
Debug.Print "Currency: " & Product & Message & PredictedProduc t
End Sub
>
Currency: 8.2433 equals 8.2433
>
but the true value is still 8.243337, so the Currency data type isn't
doing any better than rounding a Double.  In fact, it's doing much
worse.
>
The six extra bytes (48 bits) of the Decimal data type represent a
potential about 13 extra significant figures (+/- 1 / 2 ^ 48 = +/-
3.55 x 10 ^ -15) of accuracy.
>
Your argument seems to be that the Currency data type, because of the
particular scaling used, is superior to the Double data type, which
can also, BTW, be rescaled.  I agree that there's a possibility that
the bits aren't used optimally in any of the data types.  Why not
scale the range so that you can always get, say, eight places past the
decimal point and keep the Currency value under 92 billion?
Personally, I'll keep using Double's or even Decimal's whenever
possible for money calculations, then store the result as Currency.
If those options aren't accurate enough I'll roll my own mantissa
separately using available data types.  Beyond that, I'd have to
finish my so-called infinite precision string calculation code.
Anyway, I still see little reason to use the Currency data type for
anything besides storage of money amounts and simple summations of
Currency values.  This is an interesting subject so later I might
delve more into the representations of the data types and the
tradeoffs made.
>
James A. Fortune
CDMAPos...@Fort uneJames.com- Hide quoted text -
>
- Show quoted text -

In terms of databases, storing the information as a Decimal data type
is not particularly efficient. It takes up twice as much size as the
double and currency data type, and some DB cannot afford the extra
space.

The error in floating point calculations, seems particularly small.
And will become an something to be concerned when the precision is
ultimately needed, or when the iterations are really high, that they
might affect the end results.

I wish I'd known about this when I worked on my Thesis so that I would
have protected my precise calculations over billions of iterations.
Luckly I did not see any substantial error in my results.

As for my application know. Although there is a potential of running 1
million iterations, I am at most using 3 decimal places where a
precision of +/- 1.000 inch is sufficient. Using doubles and rounding,
or using currency work well. I prefered the currency data types
because i am only adding up to 3 decimal places and i dont need to use
the round function which takes procesing time running the algorithm 2%
faster.

- GL