# Thread: Decimals in Excel (2000, 2002)

1. ## 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. ## 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. ## 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. ## 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
•