Monday, April 09, 2012 11:59 PM
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..
Tuesday, April 10, 2012 12:29 AM
The easy fix is to replace your VLOOKUP formula with this
Actually, you don't really need the ROUND function. This will also work
As for why this happens, read Chip's explanation of floating point numbers
- Marked As Answer by Jaynet ZhangMicrosoft Contingent Staff, Moderator Monday, April 16, 2012 1:12 AM