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?

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.

Oops, doesn't work.

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

There must be an easier way...

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

Sorry mispelled Length as always....

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

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))).

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))

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

:?

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 ?

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

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))

Superduh! Mod(num,1).

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

Queue,

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

Mod(num) :?

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

-Queue- said:

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

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

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

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

But what exactly is your goal ?

After 20 posts, may be I should ask

Retrieve the decimal places ? Right.

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)

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

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)

Excellent!

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

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

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.

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

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.

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.

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 >>

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.

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

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 ?

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.

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.

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.

Bob,

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

I remember encountering this problem a couple of years ago, and I know that I used a different method at that time. I have a feeling that I may have ended up using a case function similar to Ernst's.

Okay, try this one.

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

Sorry Queue,

This one goes wrong with inputs between 0.9995 en 0.9999 (positive & negative)

It think that's because of the integer function that you use.

I attached a database with a digest of the suggested methods so far. All calculations have been adjusted to use 'comma' as decimal separator.

And for those who think that this subject is trivial: if you want to include a formatted number in a text field then this function is really a must. And prefarably without error...

regards,

Ernst.

NumToTextDigest.fp5.zip

How 'bout this:

Left("-",-Sign(Num)) &Middle(10^Int(1+Log(Max(1,Abs(Num))))+.0001 +Round(Abs(Num),3),2,5+Log(Max(1,Abs(Num))))

I'm trying to see if I can do it without any logical functions, and this is just about as illogical as you can get.

My computer got overheated but this one works like a charm! What a genius you are, Bob...

Now let's see who can come up with the least efficient way to do this.

My approach will be to just make one big database which looks up the right answer in a table.

Dont fear, I wont post it...

God yes Bob is a God

Thanks for this elegancy and logical approach. Ernst, good idea your digest...

Wow, my head is swelling already!

I really only posted that last one as a joke. It uses 13 functions. I think I would stick with Ernst's formula which only uses 9, unless someone can do it with fewer than that.

I left it here :

Case(Left(Num,1) = "-","-")& Choose(Left(Abs(Num),1),"0") & Abs(Round(Num,3)) & Case(Patterncount(Num,","),

Choose(Length(Round(Num,3)) - Position(Round(Num,3), ",", 1, 1), "000", "00", "0"),",000").

14 functions.

This later wasn't working neither on values kind of 12,9999.....

Bob, yours doesn't work neither with values of type 99,9999 giving 0,000 instead of 100,000

Another try so I can end this week-end with a semi-workable calc

Case(Num<0, "-")& Case(Left(Abs(Num),1)=".","0") & Abs(Round(Num, 3)) &

Case(Patterncount(Round(Num,3), "."),

Choose(Length(Num) - Position(Num, ".", 1, 1), "000", "00", "0"),".000")

12 functions....

I guess Ernst is the most robust *anyway*....

I'm not surprised that my last one fails with large numbers, because it appends a 1 on the left and right side of the number to ensure that when the middle function trims off the first and last characters, there will be the correct number of digits including a leading zero if necessary. This reduces the total accuracy of the number by at least 2 significant digits.

This one will put thousand separator too

Case( NUMBER < 0 , "-")

&

Choose( Abs( Round( NUMBER , PRECISION )) < 10^9 ,

Right( Abs(Int( Round( NUMBER , PRECISION ) / 10^9 )) , 3 ) & "," )

&

Choose( Abs( Round( NUMBER , PRECISION )) < 10^6 ,

Right( Abs(Int( Round( NUMBER , PRECISION ) / 10^6 )) , 3 ) & "," )

&

Choose( Abs( Round( NUMBER , PRECISION )) < 10^3 ,

Right( Abs(Int( Round( NUMBER , PRECISION ) / 10^3 )) , 3 ) & "," )

&

Right( Abs( Int( Round( NUMBER , PRECISION ))) , 3 )

&

Choose( PRECISION = 0 , "." &

Right( 10^PRECISION & Abs( Round( NUMBER , PRECISION ) )

* ( 10^PRECISION ) , PRECISION ))

NUMBER and PRECION are numbers (no kidding)

Dj