truncation error

[Guest]

I am writing a function in VBA to implement an algorithm that my company
uses. This algorithm takes the input, multiplies the number by a certain
factor (1.025 in this case), and then truncates the number to the tenth
place. This process is repeated TableUp number of times. Below is the code
I am using:

Public Function TableChange(OriginalRate As Double, TableUp As Long) As Double
If TableUp = 1 Then
TableChange = Int(OriginalRate * 1.025 / 0.1) * 0.1
Else
TableChange = Int(TableChange(OriginalRate, TableUp - 1) * 1.025 /
0.1) * 0.1
End If
End Function

With a seed of OriginalRate = 83.1 and TableUp = 7, this function works
correctly until the last iteration where the value the function returns is
98.3, but should be 98.4.

I believe the error comes from the Int function or possibly data type
changes, but I do not know enough, to say definitely. Can anyone help me
with the cause of this loss of value? Thank you very much.

I am using Access 2000.

 

[John Spencer (MVP)]

I modified your function slightly to take care of the imprecision of the double
type. I'm not sure this will work for you, but try it.

Public Function TableChange(OriginalRate As Currency, TableUp As Long) As Currency
Const rate As Currency = 1.025

If TableUp = 1 Then
TableChange = CCur(Int(OriginalRate * rate * 10) / 10)
Else
TableChange = CCur(Int(TableChange(OriginalRate, TableUp - 1) * rate * 10) / 10)
End If
End Function

 

[Guest]

I tried your solution and unfortunately I am still encountering the same
problem.

 

[Mike Sherrill]

On Tue, 15 Feb 2005 16:25:03 -0800, "Clayton Osterman" <Clayton

I am writing a function in VBA to implement an algorithm that my company
uses. This algorithm takes the input, multiplies the number by a certain
factor (1.025 in this case), and then truncates the number to the tenth
place.

The short story (the only one I have time for, unfortunately) is that
you don't get to decide how many decimal places a Double has.
Consider using Decimal instead.

What range of values (maximum and minimum) do you need to deal with?

 

[Guest]

My range of values is between 0 - 9999.99, although for practical purposes
the high value is very unlikely to go over 5000.

 

[Guest]

I have re-written the function to no longer be recursive, and following some
of the advice hinted to, I have made my function behave as I believe it
should in this scenario. Below is the code I used:

Function TblInc(Factor As Variant, Tbls As Long, BaseRate As Variant)
Dim IncRate As Variant, I As Long
IncRate = CDec(BaseRate)
For I = 1 To Tbls
IncRate = Int(IncRate * Factor * 10) / 10
Next
TblInc = IncRate
End Function

Because of my earlier problem, even though this function produces the
results I am expecting, I do not trust it at all for it to produce the
results I need in future situations. Can anyone explain (or give a link that
explains) why changing from the double data type to the variant, or decimal
data type, has fixed my problem? Your help is greatly appreciated, thank you.

 

[Mike Sherrill]

My range of values is between 0 - 9999.99, although for practical purposes
the high value is very unlikely to go over 5000.

Did you mean to say this?

0 to 9999.9999999999

Ten digits to the right of the decimal point?

 

[Mike Sherrill]

My range of values is between 0 - 9999.99, although for practical purposes
the high value is very unlikely to go over 5000.

I think Decimal will cover that. Note that you need to get the data
type right in the function and in the table's column definition. Off
the top of my head, I think you need Decimal(14,10) for the column
definition, but you should test that.