Excel 2003/2007 paste special values shows bizarre results
 

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?

Excel 2003/2007 paste special values shows bizarre results
 

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

Excel 2003/2007 paste special values shows bizarre results
 

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---

--
RyGuy


"TeeDub" wrote:


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

Excel 2003/2007 paste special values shows bizarre results
 

I guess its just the formatting issue. Select the entire rangeright
clickselect number in the listset to 2 decimal places.

that should do it.



"TeeDub" wrote in message
...

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

Excel 2003/2007 paste special values shows bizarre results
 

Sorry...missed a step.

Select the entire rangeright clickFormat Cellsselect 'Number' in the
listset to 2 decimal places.



"Gaurav" wrote in message
...
I guess its just the formatting issue. Select the entire rangeright
clickselect number in the listset to 2 decimal places.

that should do it.



"TeeDub" wrote in message
...

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

Excel 2003/2007 paste special values shows bizarre results
 

On Tue, 22 Apr 2008 11:24:01 -0700, TeeDub
wrote:

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

Excel 2003/2007 paste special values shows bizarre results
 

"TeeDub" wrote in message
...

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


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?

Excel 2003/2007 paste special values shows bizarre results
 

"Gaurav" wrote:

Sorry...missed a step.

Select the entire rangeright clickFormat Cellsselect 'Number' in the
listset to 2 decimal places.


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

Excel 2003/2007 paste special values shows bizarre results
 

"Ron Rosenfeld" wrote:


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.

Excel 2003/2007 paste special values shows bizarre results
 

Use =round(A - B,2)

"TeeDub" wrote:

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?

Excel 2003/2007 paste special values shows bizarre results
 

On Tue, 22 Apr 2008 12:20:00 -0700, TeeDub
wrote:


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

Excel 2003/2007 paste special values shows bizarre results
 

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/...g-answers.aspx

--
Regards,

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

"TeeDub" wrote in message
...
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?