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


#1

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.

Any insight is appreciated, thanks!


#2

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

#3

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


#4

Rounding did the trick, thank you both!