Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What happened to paste special in Excel 2007? | Excel Discussion (Misc queries) | |||
paste special as text to excel 2007 ( sp1 updated) | Excel Discussion (Misc queries) | |||
Paste Special Help (Excel 2003) | Excel Discussion (Misc queries) | |||
Paste Special in Excel 2007 | Excel Discussion (Misc queries) | |||
Paste Special, Text in Excel 2007 | New Users to Excel |