Int() confusion

[-Queue-]

If I have a number field num and create a calculation num - Int( num ), using a number such as 15.1 returns 0.0999999999999996 instead of 0.1. But if I use the same calculation in a scripted Set Field [ ], it returns the correct answer 0.1.

Any idea for the variation, and more importantly, how can you force the calculation to return the correct number without screwing it up?

[Ugo DI LUCA]

Hi JT,

Classic Computer limitation vs Math Logic !

It's good practice to round the result of a Mod function when working with decimal value. I assume it's the same here.

I've recently seen posted somewhere that on some computer, a "0.1" value would return something like .09999999999996 (was it 8 at the end ?), and that any computer consider a decimal value as a calculated value.

So Round(Num-Int(Num),1) would do it

or

if you want to catch any decimal places,

Round(Num-Int(Num),Rightwords(Num,13)), 13 being the decimal limits of FM.

[Ugo DI LUCA]

Oops, doesn't work.

Round(Num- Int(Num), Lenght(MiddleWords(Substitute(Num, ".", " "), 2,1)))

There must be an easier way...

[Ugo DI LUCA]

Round(Num- Int(Num), Length(MiddleWords(Substitute(Num, ".", " "), 2,1)))

Sorry mispelled Length as always....

[Ugo DI LUCA]

Here's the quotes from another list about quite the same. Might be of interest.

The Question

" I am no mathematician but this seems peculiar...

I have a test database with two fields:

1. Number, Type: Number

2. Calc, Type: Calculation = NumToText(Mod(Number,1))

a) When I type 1.7 into Number the calculation field displays ".7"

: When I type 43.7 into Number the calculation field displays

".700000000000003"

c) When I type 47.3 into Number the calculation field displays

".299999999999997"

I would expect .7 for : and .3 for c)

Why do I get these results??? "

The Answer , by Charles Durrwachter.

"The short answer is that numbers have a 14 place limit in FMPro.

Numbers under .x5 round down, numbers > .5 and < 1 round up. [Where x is any number of digits up to 13] In the grand scheme of things as far as computers are concerned they average out. [Actually it was a limit of the 16 bit and the 32 bit emulators OS systems]

All computers really do is add and subtract at their basic level; and a

decimal value is a calculated value for a computer. Any calculated value

will show the typical compound error accumulation of

[-Queue-]

Hmm. I am a mathematician and it still bugs me. I understand the compounded error problem, but I didn't know it affected subtraction so heavily.

My solution was to use

Round(num - Int(num), Length(RightWords(num, 1))).

[Ugo DI LUCA]

or

Duh...I definitely needed some sleep

I just went back to post this too.

13 words I was putting there in my first version. My god !

But then the whole calc coud also be :

RightWords(Num,1)*.1^Lenght(RightWords(Num,1))

[-Queue-]

That one prepends extra decimal places. That doesn't work either.

[Ugo DI LUCA]

:?

Strange. Works perfectly on the French version. When tested over a file I've download from here with US settings, not workey

Why did you say neither. Doesn't yours work ?

[-Queue-]

This works Right(num, Length(num) - Length(Int(num))).

[Ugo DI LUCA]

Ok, got it.

while the numeric separator in the French version is ",", the US separator is ".".

This means, on my computer,

MOTSFIN(num; 1)*0,1^ LONGUEUR(MOTSFIN(num; 1))

works perfectly

(RightWords(num,1)*0,1^Length(RightWords(num,1))

While to make it work with the US, I needed a substitute step

(RightWords(Substitute(num,"."," "),1)*0,1^Length(RightWords(Substitute(num,"."," "),1))

[-Queue-]

Superduh! Mod(num,1).

[-Queue-]

Yeah, I figured it had something to do with the period/comma difference.

[Ugo DI LUCA]

Queue,

You'd have to test your drums twice, or I should buy a US version.

Mod(num) :?

Mod(num, 1) ---> ,0999999999999996

[Ugo DI LUCA]

-Queue- said:

This works Right(num, Length(num) - Length(Int(num))).

Returns 1 for a value of .1, is it what is expected ?

[-Queue-]

You're right. It doesn't work for all numbers either. What a pain in the butt!

[-Queue-]

Case(not Int(num), ".") & Right(num, Length(num) - Length(Int(num)))

[Ugo DI LUCA]

But what exactly is your goal ?

After 20 posts, may be I should ask

Retrieve the decimal places ? Right.

[-Queue-]

The goal is to format a text field that contains numbers, to 3 decimal places. So 37 becomes 37.000 and 37.0138 becomes 37.014, etc.

I believe I have it now.

Int(num) & Case(not Int(num) or not (num - Int(num)), ".") & Left(Round(Right(num, Length(num) - Length(Int(num))), 3) & "000", 4)

[-Queue-]

You mean Mod(num, RightWords(Substitute(num, ".", " "),1))

[Ugo DI LUCA]

I should have asked first.

So here's one that doesn't round to 3 decimal places

MOD(MOD(num1, 1), 1)

and here's one that does...

Round(MOD(MOD(num1,1), 1),3)

[-Queue-]

Excellent!

Int(num) & "." & Left(Substitute(Round(Mod(num,1),3),".","") & "000",3)

[Ugo DI LUCA]

Int(num) & "." & Left((RightWords(num,1)*1000),3)

[-Queue-]

It still requires a substitute due to the ".", and I don't want to only truncate it to three decimal places. I want to round so it appears exactly like it would if you had a number field formatted to show three decimal places.

Thanks for all the help. I think I've got my answer now.

[ernst]

Hi Ugo and Queue,

Just to jabber on a bit about this....

Int(num) & "." & Left(Substitute(Round(Mod(num,1),3),".","") & "000",3)

Fails on negative numbers. I've played (actually struggled) with this some time ago, and the 'easiest' calculation I could come up with was:

Case(Round(num, 2) < 0 , "-") &

Truncate(Round(Abs(num), 2), 0 ) &

"," &

Right(100 + Mod(Round(Abs(num) * 100 , 0 ), 100 ), 2 )

This is of course for rounding to two decimal places...

Regards,

Ernst

[-Queue-]

Ah, good point. This, however, works with negatives.

Int(num) & "." & Left(Substitute(Round(Mod(Abs(num),1),3),".","") & "000",3).

Or, in general, Int(num) & "." & Left(Substitute(Round(Mod(Abs(num),1),Y),".","") & Z,Y), where Y is the number of desired decimal places and Z is "0" repeated Y number of times.

Thanks for the warning, Ernst.

[ernst]

Hey Queue,

Sorry to be such a pain, but I think your last formula goes wrong between 0 and -1.

Background for this nitpicking:

I had to sort this out when I made a quotation generator for our company some time ago; when I just started using it I sent customers quotations with -small- errors in the grand total. Did not really give the right impression... So I made a database with some 'critical' ranges of input values for test purposes.

Regards,

Ernst.

[BobWeaver]

This one works with negative numbers too:

Round(Num, 3) &

Middle(".000", 2+Length(Round(Num, 3))-Position(Round(Num, 3)&".",".",1,1), 4)

So, you can take your pick.

And, a general formula for any number of decimal places:

Round(Num, D) &

Middle(Left(".00000000000000000",D+1), 2+Length(Round(Num, D))-Position(Round(Num, D)&".",".",1,1), D+1)

where D is the required number of decimal places.

<<Edit: Well, it doesn't put the zero in front of the decimal point for numbers in the range -1< n < 1, so it's not perfect >>

[ernst]

Congrats Bob, your calculation stands the test!

Just one thing though, numbers between 0 and -1 do not get a leading zero, which does not look so nice.

I mean -0.5 is displayed as -.5

But I do agree that this is nitpicking in it's purest form

Regards,

Ernst.

[BobWeaver]

Well, it's really not nitpicking. The leading zero really should be there. I noticed that too after I posted it.

[Ugo DI LUCA]

Some hours later....

On my French Version, this calc turns OK for negative values, including 0 to 1 or -1 to 0 values.

Int(num) & "," & Left((RightWords(Round(num,3),1)*1000),3)

which would mean that to be acounted for dots separator instead of commas, this calc should be modified to :

Int(num) & "." & Left((RightWords(Substitute(Round(num,3),"."," "),1)*1000),3)

Is it working for you ?

[ernst]

Well I tried and maybe I'm doing something wrong, but this one does not work for me.

Int(num) for example, will give 0 as result between 0 and -1, so there will be no minus sign between those values.

Hey, I see that your modfied version (for period in stead of comma) has an extra step in it. I'll try that one and get back...

Ernst.

[ernst]

Hmmm sorry, does not appear to work either.

Goes wrong when the decimal part of the input is smaller then 0,1

Excusez moi.

Ernst.

[Ugo DI LUCA]

Right.

I guess it was just good play time around this feature.

Waiting for FM7 then for a subtil change in the way the decimal place can be extended.

[Ugo DI LUCA]

Bob,

I guess it xon't be a problem, as you can turn on the "decimal" option in the Number options.