Results 1 to 4 of 4

Math Help - help! - calculating to 15 decimal places gives incorect answer in excel

  1. #1
    Newbie
    Joined
    Sep 2008
    Posts
    2

    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
    Last edited by stew9021; September 22nd 2008 at 04:30 AM.
    Follow Math Help Forum on Facebook and Google+

  2. #2
    Grand Panjandrum
    Joined
    Nov 2005
    From
    someplace
    Posts
    14,972
    Thanks
    4
    Quote Originally Posted by stew9021 View Post
    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
    Follow Math Help Forum on Facebook and Google+

  3. #3
    Newbie
    Joined
    Sep 2008
    Posts
    2
    thanks for the quick reply think i will just have to use the round function when doing this sum.
    Follow Math Help Forum on Facebook and Google+

  4. #4
    MHF Contributor
    Joined
    Aug 2007
    From
    USA
    Posts
    3,111
    Thanks
    2
    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.
    Follow Math Help Forum on Facebook and Google+

Similar Math Help Forum Discussions

  1. Rounding to three decimal places
    Posted in the Math Topics Forum
    Replies: 2
    Last Post: August 24th 2010, 04:57 PM
  2. decimal places
    Posted in the Math Software Forum
    Replies: 6
    Last Post: July 10th 2010, 04:10 AM
  3. Decimal Places
    Posted in the Algebra Forum
    Replies: 4
    Last Post: April 1st 2008, 02:12 PM
  4. solve..round your answer to three decimal places
    Posted in the Pre-Calculus Forum
    Replies: 1
    Last Post: March 31st 2008, 06:08 AM
  5. divide. round answer to 2 decimal places
    Posted in the Algebra Forum
    Replies: 1
    Last Post: May 25th 2007, 04:12 AM

Search Tags


/mathhelpforum @mathhelpforum