Thread: help! - calculating to 15 decimal places gives incorect answer in excel

If you type "=50.12-49.99" into an spreadsheet and set format to 15 decimal places why is the =0.129999999999995?

this is surely madness the answer is 0.13 but the value of the cell is 0.129999999999995 , is there some mathematical rule im not aware of or am i just going crazy!
Ive tried using several different claculators and programs but im specifically refering to excel, rounding up is not an option as although i can get the cell to display 0.13 by setting the formating to 2 decimal places when another piece of software im running gets the value of the cell it reads it as 0.129999999999995 . the really strange thing is that this is not an infinately recuring number as if you go over 15 decimal places for example 20 it displays 0.12999999999999500000

Originally Posted by stew9021

If you type "=50.12-49.99" into an spreadsheet and set format to 15 decimal places why is the =0.129999999999995?

this is surely madness the answer is 0.13 but the value of the cell is 0.129999999999995 , is there some mathematical rule im not aware of or am i just going crazy!
Ive tried using several different claculators and programs but im specifically refering to excel, rounding up is not an option as although i can get the cell to display 0.13 by setting the formating to 2 decimal places when another piece of software im running gets the value of the cell it reads it as 0.129999999999995 . the really strange thing is that this is not an infinately recuring number as if you go over 15 decimal places for example 20 it displays 0.12999999999999500000

This is because double prescission floating point can only hold about 16-17 significant digits and the numbers in quasetion cannot be represented exactly in the number of bits available in the mantisa of floating point format. Then when you subtract them you lose the two most significant digits leaving you with 14-15 significant digits that are correct.

RonL

thanks for the quick reply think i will just have to use the round function when doing this sum.

It is important to remember that not every decimal number can be represented by a finite number of binary digits.

If you want to have some real fun, figure out why these are NOT the same.

50.12 - 49.99

50.12 - 50.00 + 0.01

50.13 - 50.00

50.00 - 50.00 + 0.12 + 0.01

One of my favorite solutions to this problem was in very old TI calculators (59, 58, 58C). I can't tell you if they still do it. They would show 10 decimal places, but carry 13 decimal places internally. You could get the other 3 if you tried, but the calculator really did not want to do that. Keeping the machine error out of sight is one way to deal with it. Understanding why it is doing it and understanding the impact are more appropriate ways to deal with it.