EXCEL 2007/2010 can't add 0.01 accurately??


  • hi,

    Excel 2007/2010 adding 0.01:

    For the problem description, used a formula to add 0.01 for each cell in the row, as X1-Y1 table. For comparison purposes, use X2-Y2 as the numbers entered manually; that is 0.56, 0.57, ...., 0.66. Then use vlookup to capture the Y1 or Y2 values again.

    The "3decimal" column is to describe the precision lost; details later...

    It appears EXCEL is unable to add 0.01 accurately; when performing 0.56+0.01, appears to be 0.5699999999999..... So, vlookup failed. Since this is related to engineering work, I have to use "FALSE" in the vlookup search.

    On internet searches, this can be fixed by setting "Excel options - Advance - Set precision as display". HOWEVER, my data 0.123456789 got truncated to 0.123 on the 3decimal column (Format cell - Number - 3 decimal places). This is giving me another problem as I will need the non-truncated numbers for other calculations.


    A corporate user.

    • Edited by qpwong Tuesday, April 10, 2012 12:17 AM correction 0.56999999..
    Monday, April 09, 2012 11:59 PM