EXCEL 2007/2010 can't add 0.01 accurately??
-
Monday, April 09, 2012 11:59 PM
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.
Help!
A corporate user.
- Edited by qpwong Tuesday, April 10, 2012 12:17 AM correction 0.56999999..
All Replies
-
Tuesday, April 10, 2012 12:29 AM
The easy fix is to replace your VLOOKUP formula with this
=SUMPRODUCT($C$7:$C$22*(ROUND($B$7:$B$22,3)=D7))
Actually, you don't really need the ROUND function. This will also work
=SUMPRODUCT($C$7:$C$22*($B$7:$B$22=D7))
As for why this happens, read Chip's explanation of floating point numbers
http://www.cpearson.com/excel/rounding.htm
Ed Ferrero
www.edferrero.com- Marked As Answer by Jaynet ZhangMicrosoft Contingent Staff, Moderator Monday, April 16, 2012 1:12 AM
.png)
