Loss of precision when concatenating an exchange rate with a string


In response to a Q on Web Applications [WA] the asker self-answered with:

=CONCATENATE("@ £/€ rate: ",text(J21,"0.00000"))

The issue was that, with "in a cell J21 I have a set value 1.10000":.

=CONCATENATE("@ £/€ rate: ",(J21))

was returning @ £/€ rate: 1.1 rather than @ £/€ rate: 1.10000.

Is there a shorter and/or clearer formula to the same effect?

Why should this post be closed?


1 answer


Provided a slight change to procedure is acceptable, then perhaps 'yes'. The J21 cell is presumably in Number format to five decimal places. First, for brevity the CONCATENATE may be replaced by & (as is mentioned by WA user @marikamitsos in the original thread).

Second, the TEXT function might be made redundant by choosing Plain text for Number format for J21.

Then the following should be adequate:

="@ £/€ rate: "&J21

It is considerably shorter and, in my view, a little clearer. Clearer because there is less risk that the precision might be misinterpreted. With TEXT used as above the precision will appear to be to five decimal places even if not. So an exchange rate of say 1.11111 entered in J21 as just 1.11 will show as:

@ £/€ rate: 1.11000

The disadvantage with formatting J21 (or most/all ColumnJ) as Plain text is that Google will not assume any 0s. So 1.10000 is 7 keystrokes, rather than 3 if in Number format to five decimal places. However exchange rates close to unity are rarely as imprecise as to one tenth.

Operating with text in J21 should not be a problem thanks to automatic treatment by Sheets of such text as a number. For example, with J21 as Plain text a J21 entry of 1.1 multiplied by say 1,000 in a cell of Automatic format shows 1100, or if formatted as Number > Number the locale equivalent of 1,100.00.

In addition, the short version is easy to adapt to an array formula, say:

=ArrayFormula("@ £/€ rate:"&J21:J)


Sign up to answer this question »