Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Aug 2005
    Location
    London/Kingston, Surrey, United Kingdom
    Posts
    518
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Decimals in Excel (2000, 2002)

    Hi everyone,
    I cannot display decimal values in Excel as I want them i.e. I have copied the values from Rate 1 column (see the attached spreadsheet) and did a paste special ->values into rate two column. when i click on the column on the function the value is coming up as -0.277200000000001 instead of -0.28 Does anyone know how to change this. And my second question is: on the total rate column I have typed in the following formula =L3*J3 the value on L3 is: 10 and the value on J3 is: = -

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Decimals in Excel (2000, 2002)

    the value in K3 is a number and can be edited to be -0.28 if desired. Before doing the range value you could use the round function:
    =round(F3-H3,2)

    If you want -2.8 instead of -2.77 you want the decimal places to be 1 not 2.

    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Decimals in Excel (2000, 2002)

    You must keep in mind that the value Excel displays in a cell is not necessarily as the stored value. The display is governed by the format specified in the Number tab of Format | Cells...

    Cell J3 contains the formula =F3-H3.
    F3 contains 12.32.
    H3 contains the formula =F3/100*102.25. The result of this calculation is 12.5972, but you see 12.60 because of the number format.
    Hence, the value of J3 is actually 12.32-12.5972 = -0.2772.
    Excel stores numbers internally in binary format (0s and 1s); when converting from and to the decimal format, small rounding errors occur. Hence the -0.277200000000001.
    If you want the value of H3 rounded to 2 decimals, change the formula in H3 to =ROUND(F3/100*102.25,2).
    And change the formula in J3 to =ROUND(F3-H3,2).

    Note: another possibility is to select Tools | Options..., activate the Calculation tab and tick the check box "Precision as displayed". But this will affect ALL cells, so it may have unexpected/undesired consequences.

  4. #4
    4 Star Lounger
    Join Date
    Aug 2005
    Location
    London/Kingston, Surrey, United Kingdom
    Posts
    518
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Decimals in Excel (2000, 2002)

    Hi Steve,

    I have used the round function and it solved both problems I did not have to change decimal places to 1..

    Many thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •