truncating a number in VB

[Eric]

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

 

[Ron Rosenfeld]

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

 

[Harald Staff]

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

 

[Anya]

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

 

[eric]

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

-----Original Message-----

 

[Ron Rosenfeld]

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