How do I truncate numbers in VB Excel(2000)?


I am in Excel's VB and having problems getting the VB to
see the two figures below as the same amount. I have tried
it as a single and double, but it doesn't work.

number1= 1.06000201020
number2= 1.06000606060

I'd like excel to see it as
number1=number2
not
number1<number2

On Thu, 26 Aug 2004 12:41:24 -0700, "Eric" wrote:

How do I truncate numbers in VB Excel(2000)?


I am in Excel's VB and having problems getting the VB to
see the two figures below as the same amount. I have tried
it as a single and double, but it doesn't work.

number1= 1.06000201020
number2= 1.06000606060

I'd like excel to see it as
number1=number2
not
number1<number2

Well, there are a bunch of ways of doing this in VB.

You can Round the numbers to the desired degree of precision and test for
equality.

For example:

?round(1.0600020102,4)=round(1.0600060606,4)
True

You could decide how closely you want the numbers to agree and right a little
routine to compare them:

===================
Sub foo()
Const number1 As Double = 1.0600020102
Const number2 As Double = 1.0600060606

Const Difference As Double = 10 ^ -5

If Abs(number1 - number2) < Difference Then
MsgBox (number1 & " is the same as " & number2)
Else
MsgBox (number1 & " is not the same as " & number2)
End If

End Sub
=========================

And there are other ways, too, depending on what you want to do with the
result.


--ron

Hi

I like using "significant digits" for this, it scales the slack according to
number size. Your needs may be different, but here we go anyway:

Function SigDig(ByVal D As Double, i As Long) As Double
Dim L As Long
L = i - 1 - Int(Log(Abs(D)) / Log(10#))
SigDig = Round(D * 10 ^ L, 0) / (10 ^ L)
End Function

Sub tester()
Dim number1 As Double
Dim number2 As Double
number1 = 1.0600020102
number2 = 1.0600060606
MsgBox SigDig(number1, 2)
MsgBox SigDig(number1, 3)
MsgBox SigDig(number1, 3) = SigDig(number2, 3)
End Sub

HTH. Best wishes Harald
"Eric" skrev i melding
...
How do I truncate numbers in VB Excel(2000)?


I am in Excel's VB and having problems getting the VB to
see the two figures below as the same amount. I have tried
it as a single and double, but it doesn't work.

number1= 1.06000201020
number2= 1.06000606060

I'd like excel to see it as
number1=number2
not
number1<number2

This could be an awkward way to do it but it will do the
job and truncate the numbers to 2 decimals:

Dim number1 As Double
Dim number2 As Double


number1 = Left(Range("A1").Value, InStr(1, Range
("A1").Value, ".", vbTextCompare) - 1) & Mid(Range
("A1").Value, InStr(1, Range("A1").Value, ".",
vbTextCompare), 3)

number2 = Left(Range("A2").Value, InStr(1, Range
("A2").Value, ".", vbTextCompare) - 1) & Mid(Range
("A2").Value, InStr(1, Range("A2").Value, ".",
vbTextCompare), 3)


this example assumes that your number1 is in cell A1 and
number2 is in cell A2. if you want to truncate to more
than 2 decimals, change the number at the end of each
line, for example from ), 3) to ), 4) to have 3 decimals

Ron,
How can I subtract these two numbers and get zero?

-----Original Message-----
On Thu, 26 Aug 2004 12:41:24 -0700, "Eric"
wrote:

How do I truncate numbers in VB Excel(2000)?


I am in Excel's VB and having problems getting the VB to
see the two figures below as the same amount. I have
tried
it as a single and double, but it doesn't work.

number1= 1.06000201020
number2= 1.06000606060

I'd like excel to see it as
number1=number2
not
number1<number2

Well, there are a bunch of ways of doing this in VB.

You can Round the numbers to the desired degree of
precision and test for
equality.

For example:

?round(1.0600020102,4)=round(1.0600060606,4)
True

You could decide how closely you want the numbers to
agree and right a little
routine to compare them:

===================
Sub foo()
Const number1 As Double = 1.0600020102
Const number2 As Double = 1.0600060606

Const Difference As Double = 10 ^ -5

If Abs(number1 - number2) < Difference Then
MsgBox (number1 & " is the same as " & number2)
Else
MsgBox (number1 & " is not the same as " & number2)
End If

End Sub
=========================

And there are other ways, too, depending on what you want
to do with the
result.


--ron
.

On Thu, 26 Aug 2004 13:26:24 -0700, "eric" wrote:

Ron,
How can I subtract these two numbers and get zero?

Uh, by rounding them to a level of precision where they are identical.

eg:

res = Round(Number1,4) - Round(Number2,4)

res will equal zero given the two numbers in your example.


--ron