Excel 2003/2007 paste special values shows bizarre results

[TeeDub]

When I subtract a set of numbers, then take the result and paste special ->
values, often my results show a bizarre number of decimal places.

The cell shows the correct value, but the formula bar shows the oddball
numbers.
(Apparently it is using the imsub function to grab the value instead of the
sub function.) This is causing havoc with a few comparison calculations I am
trying to do within the worksheet.

Any suggestions on how to fix this?

 

[TeeDub]

I figured I had better give a specific example (or two)

592.58-591.57=1.01 --> paste special = 1.0099999999999999
115.53-114.79=0.74 --> paste special = 0.7399999999999995

 

[ryguy7272]

This just shows the precision of your numbers. Right-click the cells of
interest, click format, and choose the appropriate number of decimal places
for your needs.

Regards,
Ryan---

 

[Gaurav]

I guess its just the formatting issue. Select the entire range>right
click>select number in the list>set to 2 decimal places.

that should do it.

 

[Gaurav]

Sorry...missed a step.

Select the entire range>right click>Format Cells>select 'Number' in the
list>set to 2 decimal places.

 

[Ron Rosenfeld]

When I subtract a set of numbers, then take the result and paste special ->
values, often my results show a bizarre number of decimal places.

The cell shows the correct value, but the formula bar shows the oddball
numbers.
(Apparently it is using the imsub function to grab the value instead of the
sub function.) This is causing havoc with a few comparison calculations I am
trying to do within the worksheet.

Any suggestions on how to fix this?

It has nothing to do with the imsub function. Rather it is due to the fact
that Excel, and most other spreadsheet programs, adhere to the IEEE convention
regarding storage as binary of floating point numbers.

See http://support.microsoft.com/kb/214118/en-us for a more detailed
explanation and a suggestion of some work-arounds.
--ron

 

[TeeDub]

If I do an =if (hard coded 1.01=calculated value of 1.01) (from above) if
comes out as false. I understand that I can format the numbers to make them
look righ, in fact, excel does that for me.... The problem is that they are
not what they appear to be. Why is it not just giving me an answer of 1.01?

 

[TeeDub]

Sorry...missed a step.

Select the entire range>right click>Format Cells>select 'Number' in the
list>set to 2 decimal places.

In the formula bar it still shows 1.00999999999999 etc.... Formating only
effects the value in the cell.

 

[TeeDub]

It has nothing to do with the imsub function. Rather it is due to the fact
that Excel, and most other spreadsheet programs, adhere to the IEEE convention
regarding storage as binary of floating point numbers.

See http://support.microsoft.com/kb/214118/en-us for a more detailed
explanation and a suggestion of some work-arounds.
--ron

That would be fine if I had no other calculations longer than two decimal
places.

 

[Jim]

Use =round(A - B,2)

 

[Ron Rosenfeld]

That would be fine if I had no other calculations longer than two decimal
places.

How does that affect the application of Method 1?
--ron

 

[Ashish Mathur]

Hi,

As mentioned by another user, this is because of Excel's floating point
number algorithm. You may read more on this on the MSDN blog:

http://blogs.msdn.com/excel/archive...es-excel-give-me-seemingly-wrong-answers.aspx

--
Regards,

Ashish Mathur
www.ashishmathur.com
http://www.linkedin.com/in/excelenthusiasts