Numerical Precision (Decimal Places)

[Macintodd]

Numerical Precision

This should be a very simple thing, but it has stumped me. All I want to do is to have two full decimal places displayed after a number has been used in a calculation. It seems simple, right, just use the field format feature and zap. However, I don't just want to look at the number, I want to insert the number with some text into a text field. For example the output text could be something like: "The total cost is $2.10". FMP will return "The total cost is $2.1" instead!

There seems to be only two relevant functions, Truncate, which is no good, and Round, which doesn't work the way I would like it to. From my testing, it seems that when you use a number in a calculation, all the extra precision (extra zeros) vanish. So 1.00 + 1.00 returns 2 instead of the proper 2.00. I even tried adding $0.001 to the amount and rounding it to two decimal places, but no luck.

If anybody can help me with this I would be very grateful. Thanks!

[Lee Smith]

Try this one:

"The total cost is " &

Int(Round(Amount, 2)) & "." & Right(Round(Amount, 2) * 100, 2)

HTH

Lee

[RussBaker]

In my Handy Bits post in the samples section of this forum there is a dowlonadable file which has a calculation under the number tab which will convert a number into currency format, with $ sign and"-" if appropriate. It works up to 15 digits and inserts "," as thousands separators.

[Lee Smith]

Hi Russ,

This is the second time today that you have pointed at your file (thank you for sharing by the way), but you fail to tell them to look for the newest one at the bottom of the post.

Lee

[Macintodd]

Thank you very much for that file Russ! I can't believe that was so complicated. Don't you think that sort of thing should be incorporated into the Round command or have its own command. Are you listening FMP programmers?

[Macintodd]

Lee,

Thank you very much for giving me a relatively simple solution. I am using your calculation with a minor modification.

"The total cost is " &

Int(Round(Amount, 2)) & "." & Right(Round(Amount, 2) * 100, 2)

I think the first Round command you used is redundant, so I removed it and here it is:

"The total cost is $" &

Int(SHI$CAN) & "." & Right(Round(SHI$CAN, 2) * 100, 2)

I greatly appreciate your quick, insightful response.

[RussBaker]

Yes - it is a bit complicated in my sample file but the complication is only needed to get the thousands separator commas in the right place. Otherwise, the guts of the calc is the same as has generally been described here.

FMP will format numbers very well (and very easily) but its when you want to incorporate them as text in other areas - primarily for web publishing as text - that you need these workarounds.

[Macintodd]

Russ,

I am sorry, but I don't agree with your statement about FMP formatting numbers very well and easily. Sure, changing the appearance of a number in a field is easy, but the actual number that is stored there is the issue I am concerned about. I can also understand having to make workarounds when combining text and numbers, but that is not really the issue, I just used that as a simple example. The root of the problem is that FMP seems to loose the precision of a number after any calculation. This is a serious mathematical error, is it not?

The result of the Round command should not drop the rightmost zeros after the decimal place. FMP should retain all the zeros, and if specified in a (not yet available) command it should even fill in zeros if there aren't any.

I find it hard to believe that this issue hasn't been addressed with all of the people out there that must be using FMP to do financial type calculations. Surely there must be a more elegant solution that chopping off the integer and the decimal digits and recombining them! Am I missing something?

Thanks for your responses!

[BobWeaver]

Depending on exactly what you are doing with the result of this data, there may be a simpler solution. If you just want to display this information, you can create a merge field with the text "The total cost is <<TotalCost>>". Then with this text selected, select the number formatting menu item, and select the formatting that you want.

Any numbers that appear in merge fields in the text block will get the chosen formatting. The drawback to this method is that there is no way to give different merge fields different formatting within the same text block. On the other hand, you can apply time, date and number formatting to the same text block, and it will affect the respective merge fields.

[Macintodd]

Bob,

Yours is the most elegant solution to my immediate problem. Thank you very much. The principle of the thing still bothers me a bit, but I can live with it for now.

I hope it's not too cold out in "toon-town" he he he....

[Macintodd]

Bob,

Actually that doesn't quite work for my immediate problem now that I try it. The problem is that the <<MergeField>> only seems work outside of a proper field. I want to take a some text, some properly formatted currency, and paste it into a field with other text. Perhaps after I get some sleep, I will think of something clever. Anyway, I have it working acceptably well for now. I thank you again for your assistance.

[BobWeaver]

Correct; it doesn't work in a field, only in a block of text that contains a merge field. That's why I said it would only be useful for display or printing, but not if you need to put this in a field for some other use.

[kennedy]

The root of the problem is that FMP seems to loose the precision of a number after any calculation. This is a serious mathematical error, is it not?

The result of the Round command should not drop the rightmost zeros after the decimal place. FMP should retain all the zeros, and if specified in a (not yet available) command it should even fill in zeros if there aren't any.

You're mixing up the formatting of the number with the number itself. Per my experiments, FMP does all math using the same IEEE precision. Dropping zeroes off the end of a number is NOT loss of precision... 2.1 = 2.10 = 2.1000.

Rather, dropping zeroes off the end is just one way of formatting numbers.

The problem here is that much of FMP functionality is not available in all situations. Were I the architect of FMP, any number formatting functionality available on a number field would also be available in the NumToText function, such that you can easily format the number the way you want.

Actually, if I were the architect, I'd have also made <<>> merge fields first class objects, allowing you to use them in normal text functions... giving you yet another way.

FMP, instead, does not let you utilize all the nice number formatting it has in all situations. So, if you find yourself in such situations, you end up needing to re-implement that formatting by hand using text functionality that is itself fairly weak... resulting in some really ugly computations.

Brian

[daltwin]

I agree with Brian, though I think that with a little ingenuity you can always get to something workable ... as many of the posts and answers on this forum aptly show.

However I think that with its formatting options, FileMaker has tried to make it easier for new users to adapt (with a lower curve for learning, maybe) by offering formatting options which are built like many spreadsheet applications. Users of Excel et al will be more at home.

What would be best, though, is if they provided all that but also implement the more advanced features for the rest of us. Still it's a great program.

[ernst]

I was thinking about a solution for this problem and tried Lee's suggestion, but found that it produces the wrong output with input values between 0 and -1.

The easiest (?) I could come up with until now is:

MyNumberAsText =

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

Case(Abs(Round( MyNumber , 2 )) < 1 , "0" , Abs(Truncate( MyNumber , 0 ))) &

"," &

Right(100 100

NumToText Again.fp5.hqx

[cjaeger]

Here is anothe rcalculatio for currency display. it is very flexible as it calculates correct regardless of international decimal and grouping separators.

number formatting.zip

[Lee Smith]

I like it.

Thanks for sharing.

Lee

[ernst]

Hi Christian,

Maybe my computer is broken -or there's something wrong with the international settings- but if I input -1,1 the formatted output is $ -1,-,

(decsep is comma, grpsep is period)

I also tried inputting -1.1 which gives $ -11,00 as formatted output.

Uhhhhh,

Any ideas?

Regards,

Ernst.

[Lee Smith]

Hi Christian,

There is a problem with the decimal. If I choose 2, it just adds 2 zeros to the end. If I put a decimal in the input number, it adds a comma and a period and the number looks like $ 1,000,012,001.,2

BTW, I did switch the decimal to . and the thousand separator to , so these show up proper.

TIA

Lee

[cjaeger]

just set the file to use local number format and it should work (when opening the file you should have been prompted for "use System format?" - simply press "OK")

[cjaeger]

but - there is a little bug in the formula as it shows a decsep even if you set the decimal places to 0.

But that can be easily corrected with an if(# of decimals>0, ...) statement.

The purpose of this file is to show how you can shorten a lot of calculations by using Trim() and Substitute() instead of checking for a variety of conditions.

In this case, you dont have to check for

if(length(number)< 3 ,... 6, ...., 9,...., 12, ....etc.

Any surplus spaces will simply be cut off, then spaces within the string will be replaced by the desired result....

[ernst]

Hi Christian,

Funny, but it really doesn't work at my place. And of course I tried answering both Yes and No to the "Use System format" dialog.

Also when entering a negative number with a multiple of three digits before the decimal point (i.e. -444) the formatted value has a leading 0 in stead of the minus sign (0.444,00)

Any ideas?

Best regards,

Ernst

[cjaeger]

Well, there were some glitches.

Now the calc is about as long as the checking for length, but it works.

number formatting.zip

[Lee Smith]

Hi Christian,

[color:"blue"]> Now the calc is about as long as the checking for length, but it works.

Yes it does, and thank you for sharing once again.

Lee

[ernst]

Hi Christian,

Indeed -no more glitches- Thank you for this very clever solution!

Regards,

Ernst

[ernst]

Though Christian's Solution to convert from numbers to text is definitely very neat and flexible, I wanted a simpler calculation for one of my database.

I came up with a solution somewhere up in this thread, but discovered a few days ago that it produces the wrong output in some cases.

I found that this is caused by using the MOD function on a number that is not integer, but has a fraction.

So, for example,

100,99 MOD 1 produces an output of 0,989999999999995

in stead of 0,99 as one (to be more precise, I ) would expect.

Since the calculation is used in a quotation system, I was certainly not very happy to find this, so I rethought the problem and came up with the attached solution.

It includes a sort of 'calculation generator' so if the numerical field that you have to convert to text happens to be called 'SlartiBartFart' you can just input that and copy the calculation with 'SlartiBartFart' filled in to the appropriate places back to the field definitions in your database.

Maybe someone finds this useful, somehow.

Best regards,

Ernst.

NumToTextSimple_v2.fp5.zip