I am nonplussed by this behavior in a very simple accounting spreadsheet:
CONCATENATE appears to override cell formatting, and express integers to great length.
The error of one quadrillionth (or so) appears to occur somewhere in the reference between workbook sheets, because if I manually enter "49.66", the CONC result is exactly 2-decimal.
How can I limit the expression and still use the cross-sheet formula?
See attached sample workbook for demonstration.
CONCissue.xlsx
the problem cell (January!B3) is set to accounting, 2 decimals, and reads 49.66, which is itself a reference to cell "AMEX!A3"
I have played with the decimals, but it just adds 0's, (unless I go to 13 decimal places)
When I concatenate A3 and B3 , I get more numbers than I want. see examples:
cell A5: " =A3 & B3 " results in this display " AmEx Balance Due=49.6600000000001 "
setting decimals on A5 has no effect on the length of the expression. Setting format to text in any/all cells had no effect on the final result.
[oddly (to me),the result I get in my actual spreadsheet is off a quadrillionth the other direction:
"AMEX bal = 49.6599999999999" ]
I'm assuming the quadrillionth error is just an Excel thing, but I'd like to limit the expression in the concatenation to just 2 decimals. How do I do that??
Bookmarks