Extracting the Fractional Digits of a Number

[Cyberdude]

It must be the heat (it?s currently 105 degrees here today), but I just discovered that I don?t know how to extract the fractional digits from a number like 100.544. Consider the following code:
[vba]Sub DigitsTest
Dim X As Double, Y As Double, Z As Double
X = 100.544 ?This is a constant and not the result of another calculation
Y = Application.RoundDown(X, 0) ?This puts 100 into ?Y?
Z = X ? Y ?This is 100.544 ? 100
Msgbox Z ?This displays 0.543999999999997
End Sub[/vba] Why doesn?t ?Z? contain 0.544 ??
Instead of Y = Application.RoundDown(X, 0) I used Y = Cint(X) , but that rounds up so I get 101 instead of 100.

[OBP]

Wierd isn't it. It just goes to show that VBA Int, CInt and Roundown are not accurate, this is though.
[vba]Dim X As Single, Y As Single, Z As Double
X = 100.544
Y = Int(X) * 10000
X = X * 10000
MsgBox (X - Y) / 10000[/vba]
So is this -

[VBA] Dim X As Double, Y As Double, Z As Double
X = 100.544
Y = Int(X) * 10000000
X = X * 10000000
MsgBox (X - Y) / 10000000[/VBA]

[bigdoggit]

I was checking out the web, cause I was intrigued by the question. I found a web site at http://www.cpearson.com/excel/rounding.htm that explained the limitations of the floating point decimal system. I mentions that fractional numbers are not stored exactly accurate, but whole numbers are! So when we multiply fractions to make them whole numbers, excel can accuratly store them using the floating point decimal system. Hopefully this helps and I didn't miss the point or anything. Air out any thoughts, curious about them.

[matthewspatrick]

It's all just "Fun with FLoating Point Arithmetic"

BTW, this worked just fine in the Immediate window:

[VBA]
?Val("." & Split(100.544 - 100, ".")(1))
[/VBA]

[Cyberdude]

?Val("." & Split(100.544 - 100, ".")(1))

OK, I'll bite ... what's "?Val("??

Hey, bigdoggit, that's the first time I've heard that. But one wonders, if 0.544 is stored as 0.543999 (etc), then why doesn't mulitplying it by a thousand give you 543.999 (etc)?? Strange ... very strange.

Thanx for the replies guys!
Sid

[malik641]

BTW, this worked just fine in the Immediate window:

[vba]
?Val("." & Split(100.544 - 100, ".")(1))
[/vba]

not for me

?Val("." & Split(100.544 - 100, ".")(1))
0.543999999999997

I've been doing some small procedures in AutoCAD and I have a feeling I'm going to run into a problem if some numbers are not going to be accurate like this...so I'll have to code carefully.

This worked for me from the Immediate window
[vba]?Right("100.544",Len("100.544") - Application.WorksheetFunction.Find(".","100.544") + 1)[/vba]

?Right("100.544",Len("100.544") - Application.WorksheetFunction.Find(".","100.544") + 1)
.544

[lucas]

In Cad--From experience and conversations with Tommy I have found you need to calculate pi to at least 14 digits to maintain accuracy over many calculations Joseph.

[matthewspatrick]

Sid,

OK, I'll bite ... what's "?Val("??

Taking the expression inside out...

1. Split() returns a zero-based array, breaking the first argument by the delimiter specified in the second argument. Using Split()(1) is shorthand for grabbing just the 2nd (remember, zero-based!) element of that array. In this case, the return value is the string "544"
2. Val() takes its argument and tries to convert it into a number. Effectively, my expression becomes Val(".544"), which should be 0.544.
3. ? is just the old BASIC shorthand for Print

Hey, bigdoggit, that's the first time I've heard that. But one wonders, if 0.544 is stored as 0.543999 (etc), then why doesn't mulitplying it by a thousand give you 543.999 (etc)?? Strange ... very strange.

Remember, it is not necessarily the case that 0.544 was stored as 0.543999.... Rather, the result of subtracting 100 from 100.544 was represented as....

[matthewspatrick]

Joseph,

How odd that our Immediate Windows disagree! Shouldn't make a difference, but I ran my test from Excel VBA. How about you?

not for me
I've been doing some small procedures in AutoCAD and I have a feeling I'm going to run into a problem if some numbers are not going to be accurate like this...so I'll have to code carefully.

Indeed. For some floating point comparisons, you should not use the = operator. Instead, you should test to see whether the absolute difference between two numbers is vanishingly small.

[malik641]

In Cad--From experience and conversations with Tommy I have found you need to calculate pi to at least 14 digits to maintain accuracy over many calculations Joseph.

14 huh? Wow. Is there a constant built-in to VBA for that? I know that Excel VBA does (Application.WorksheetFunction.Pi ....which is stored at 14 digits after the decimal point)

How odd that our Immediate Windows disagree! Shouldn't make a difference, but I ran my test from Excel VBA. How about you?

I know! It's very strange. I'm running Excel 2003, how about you?

Indeed. For some floating point comparisons, you should not use the = operator. Instead, you should test to see whether the absolute difference between two numbers is vanishingly small.

Nice idea, I'll look into that. Thankfully my line of work with CAD isn't an exact science....but if I get a job with cad in the field of Mechanical Engineering where it WILL matter, I'd like to know.

[matthewspatrick]

Excel XP

[matthewspatrick]

Joseph,

14 huh? Wow. Is there a constant built-in to VBA for that? I know that Excel VBA does (Application.WorksheetFunction.Pi ....which is stored at 14 digits after the decimal point)

A Double has maximum precision of 15 significant digits, so what the PI() function returns is the maximum precision possible in Excel or VBA.

You could always just define a global constant in your project for pi...

[lucas]

Const pi = 3.14159265358979

[malik641]

Excel XP....same as 2003 right?

A Double has maximum precision of 15 significant digits, so what the PI() function returns is the maximum precision possible in Excel or VBA.

You could always just define a global constant in your project for pi...

I see and good idea... It's amazing how PI has yet to be found an end.

[matthewspatrick]

No, Office XP = Office 2002.

[malik641]

No, Office XP = Office 2002.

Thanks.....still wondering why you had a different value than me eh...oh well. I'll worry about it later, it's getting late over here.

[Bob Phillips]

Excel XP

I have XP and I get 0.543999999999997, but I agree with you on testing for > than a small number, which the different results we all get is amply validated.