Extra Value appearing multiple decimal places later on a value within a formula, but not a cell.

[NatetheGreat]

HI,

I have two values In two cells. Values are 3.08 and 3.07. When I click on either cell the value 3.08 or 3.07 only shows on the formula bar, and no matter how many time I click the increase decimal place button.. the value is still 3.08 (i.e. 3.080000000000000000 forever on...). These are therefore not rounded figures.

However I got a result on a formula using this value where I wouldn't have expected.
It is quite a long if... however that isn't too important...just whats bold is important.

=IF(Table4[[#This Row],[Type]]="ENTER",IF(Table4[[#This Row],[BS]]="Ak",IF((Table4[[#This Row],[BestAPrice]]-Table4[[#This Row],[Price]])<0.03,IF((Table4[[#This Row],[BestAPrice]]-Table4[[#This Row],[Price]])>0.01,U3+Table4[[#This Row],[Trans]]),U3),U3),U3)

The strange part is where it thinks that bolded part is true... and therefore yeidling a value. This should not true! i.e.... 3.08-3.07 is not larger than .01 !

When I evaluate the formula it shows

IF(TRUE,IF(TRUE,IF(TRUE,IF((0.01000000000000002)>.01,U3+TABLE4[[#THISROW],[TRANSVOL]]),U3),U3),U3)

Where is excel picking up this extra .000000000000002 ? Of course if it thinks that the .00000000000002 is there, then indeed the formula becomes true. As I said regardless of how many times you increase the decimal place on either of those values in their respective cells... there is nooooo .0000000000000000002 at the end. This is very frustrating as it is breaking down the logic of these formulas.

I would prefer it to be =1 rather than ,1 in the formulas anyway, but that isn't going to work with this extra value hanging on the end.

Ideas of where this is coming from ?

 

[jkpieterse]

This is caused by the fact that decimal numbers have to be converted to binary numbers before the computer can do math with them. Not all decimal numbers can be exactly represented by a binary number, which is why there is sometimes a very small difference between what is expected and what is shown. You can cater for such differences by rounding before testing.