Help

Why does this number product have a repeating decimal when converted to text?

Topic Labels: Formulas
Solved
Jump to Solution
2232 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Tedco_Shop
4 - Data Explorer
4 - Data Explorer

I recently discovered that when I multiply 6.1 * 3, and convert the product to a string using

product & “”

the result is a string with repeating decimals, even though the numeric result is simply 18.3.

This is actually causing a serious problem in my table when formatting prices for invoices.

Repeating decimal.JPG

Any insight is appreciated, thanks!

1 Solution

Accepted Solutions
Justin_Barrett
18 - Pluto
18 - Pluto

The default formatting for formulas that generate numeric output is to show only one place past the decimal point, but that’s just rounding the number. Internally the data is sometimes more precise. However, why it’s not simply 18.3 to begin with is beyond me. Gotta love floating point math!

Anyway, it looks like you’ll have to round it yourself in your final formula:

ROUND(product, 1) & ""

See Solution in Thread

3 Replies 3
Justin_Barrett
18 - Pluto
18 - Pluto

The default formatting for formulas that generate numeric output is to show only one place past the decimal point, but that’s just rounding the number. Internally the data is sometimes more precise. However, why it’s not simply 18.3 to begin with is beyond me. Gotta love floating point math!

Anyway, it looks like you’ll have to round it yourself in your final formula:

ROUND(product, 1) & ""

There is a long-standing issue with bizarre floating point errors cropping up as the result of implicit or explicit number-to-string conversions. At the moment, the workaround is to include an explicit call to ROUND() prior to the conversion to string.

(In addition, IIRC, my pretty-print routines for formatting numeric and currency amounts into more user-friendly strings also correct such potential infelicities. They also add thousands separators to the output string, provide for right and decimal-point alignment, support alternate [i.e., non-U.S.] decimal and thousands separators, allow the use of parentheses rather than the hyphen-minus character to indicate negative amounts, and correctly pad integer currency amounts to include a trailing ‘.00’, in case any of those capabilities are of interest.)

Tedco_Shop
4 - Data Explorer
4 - Data Explorer

Rounding did the trick, thank you both!