Error with int() calculation

[DilemmaDave]

Hi All,

This is driving me batty! The int() function returns obviously wrong results and I can't figure out why.

I've simplified things with the following bit of code:

Sub Int_Error()
x = 39.8
y = x * 100
z = Int
End Sub

If you step through this and watch the values , 'z' evaluates as 3979, instead of the correct value of 3980.

I've tested this on two different systems and have the same issue. I'm using Excel 2003, SP3.

z=Int(3980) returns the correct value, so it seems it only happens when the argument is passed as a variable.

This happens consistently with some specific values, 39.8 being one of those values.

Thoughts? Ideas?

Cheers,
David W.

 

[DilemmaDave]

You should always qualify the property you wish to use. In this case it is using the default range Value property, but you ought to be using Value2.

 Cells(c.Row, 3).Value2 * 100

Problem is, Some of the values that need to be evaluated have more than 3 decimals, I want to discard any beyond two, hence the use of int().

Edit: Just fully read that. My bad.

 

[DilemmaDave]

Still evaluates to 3979.

Dim HoleTo as Long
HoleTo = Int(Cells(C.Row, 3).Value2 * 100)


Value in the cell referenced is 39.8

 

[Jon von der Heyden]

Lose the int mate.

Dim HoleTo As Long
HoleTo = Cells(c.Row, 3).Value2 * 100

It will be truncated because Long Integers do not support decimals values.

 

[DilemmaDave]

Lose the int mate.

Dim HoleTo As Long
HoleTo = Cells(c.Row, 3).Value2 * 100

It will be truncated because Long Integers do not support decimals values.

Yes, Of course. I was too hung up on the original problem.

Thank you so much.


It still concerns me that something as simple as the original I posted evaluates incorrectly. Is there a reason for that? It's pretty straight forward. How is int(3980) different from:

y=3980
z=int

???

 

[Jon von der Heyden]

Open a new workbook and enter 39.8 in A1
In any other cell enter =LEN(MOD(A1,1))-2.

This should result in 1 because you entered a number with a single decimal space, yet the answer you get is ???

Why? Well the answer to that is not so straight forward to explain, and not so easy to understand. All I'm going to do is refer you to this article.

 

[DilemmaDave]

Thank You Jon.

Interesting read.

Now I'm going to be questioning the validity of all my calculations.

Ah well, at least this problem is solved and it explains some other issues I have had in the past. With your helpful guidance at least I'll have some insight in the future.

Thanks again,
David W.

 

[Jon von der Heyden]

Now I'm going to be questioning the validity of all my calculations.

That's why we're here.