Decimal Formatting in Calculation Field

[Chris Medeiros]

Hello Tap Forms Community,

Can someone give me a tip on how to format a calculated field result in the attached pic?

Instead of “…(0.5600000000000001)…”, I’d prefer it to be (0.56).

Attachments:

You must be logged in to view attached files.

[Sam Moffatt]

What is the type of current year rate? I was trying to figure out how to repro this to figure out a viable solution but I didn’t see one. I create a number field and populated it with 0.56 and the text calculation field printed .56 as the rate as I’d expect. I tried doing it as a calculation field with 56/100 but that didn’t seem to trigger the weird behaviour either.

What you’re seeing is floating point rounding errors. The 0.56 ends up being stored as 0.560000000000000053290705182007513940334320068359375 which is why you see 0.5600000000000001 as the 5 rounds up:

0.5600000000000001
0.560000000000000053290705182007513940334320068359375

I suspect this is a reflection of the underlying JSON data model (Javascript floating point numbers are IEEE-754 64-bit double precision numbers) and unfortunately leaking out. For some more gory details I found a YouTube video on floating point representation and rounding error and an online converter that lets you put in 0.56 and see what the computer is doing.

I have an sprintf implementation that I ported for printing formatted strings in Javascript that should handle it if you want, there was a not dissimilar thread on if to use a calc or script field that shows formatting floating point numbers. That may yet be the solution for you as well but it’s a bit heavier than a simple calc field.

[Daniel Leu]

Assuming that the result of your calculation is a number: in the field properties, you can select the number of decimal places to something like 2.

[Chris Medeiros]

Sam,

To answer your question, the Current Year Rate field type is “Number”, set to 3 decimal places. The odd part…If I change the Current Year Rate value to 0.55 or .57, the report is formatted fine. It’s just 0.56 that seems to have the rounding issue. You’re probably right on the money with your explanation. The only person that will see this report is my Tax Person, so it’s not critical. Thank you for your time and thoughts on this Sam!

Daniel,

Thanks for your suggestion. The calculation field result is “Text”. However, I could try sandwiching a calculation field set to Number, 2 Decimals in between the text and see if it handles the rounding issue better.

I’ll let you know how that works.

Attachments:

You must be logged in to view attached files.

[Sam Moffatt]

The other two numbers round correctly to the right value:

0.5600000000000001
0.560000000000000053290705182007513940334320068359375
0.5500000000000000444089209850062616169452667236328125
0.56999999999999995115018691649311222136020660400390625

The script field is probably the answer if you really want to fix it, I don’t think there is a way within a calc field you can make it work when embedded in a string like that.

[Brendan]

For a Calculation field, use the FORMAT() function to format your results.

FORMAT(Deduction Amount; "0.00")

That’ll ensure your values are always prefixed with a 0 if less than 1 and will always have 2 decimal places.

[Sam Moffatt]

oooh it’s in the text section, would it be possible to copy the documentation for that in the math section as well? I was looking in the math section for something to format numbers and TRUNC was the only thing I saw. Also looking in the text section, should STR2NUM be in the math area too?

[Chris Medeiros]

Thank you Brendan. That did the trick! I had used the format function built into the field Properties column for Date & Time formats, but I didn’t realize there was a separate FORMAT function to use in this case. Thanks for the example also!

Thanks for your help also Sam!

–Chris

[Author]

Posts