+ Reply to Thread
Results 1 to 3 of 3

concatenated cell reference returns wrong value (by a quadrillionth)

  1. #1
    Registered User
    Join Date
    02-02-2011
    Location
    Oregon
    MS-Off Ver
    Excel 2010
    Posts
    2

    concatenated cell reference returns wrong value (by a quadrillionth)

    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??
    Attached Files Attached Files
    Last edited by kalmiopsis; 02-02-2011 at 03:05 PM.

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: concatenated cell reference returns wrong value (by a quadrillionth)

    Try like this

    =A3&DOLLAR(B3)
    Audere est facere

  3. #3
    Registered User
    Join Date
    02-02-2011
    Location
    Oregon
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: concatenated cell reference returns wrong value (by a quadrillionth)

    Thank you, DDL! That solved my problem. (I'm mildly interested in the reason for the calculation error, but VERY HAPPY to have a new tool to fix it.)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1