When removing links with copy and paste special some values appear in scientific notation or expanded decimal places.

[BYahr]

This Excel spreadsheet pulls data (values) form 20 other spreadsheets. I'd like an effective way to remove the links and replace them with the value accurately. When I use the Copy and Paste Special values, some - not all - of the values are changed to either scientific notation or with expanded decimal places. The visible value appears correct but if that value were copies for another purpose the value isn't accurate.

Example: Link show 0.00. After Copy and Paste Special values, the cell visibly shows 0.00 but is actually 3.63797880709171E-12.
Or: Link shows -20.00. After Copy and Paste Special values, the cell visibly shows the same but is actually -19.9999999999927.

The source data is an amount generated from a simple formula adding or subtracting several cells all formatted as 2 decimal places as in cash and coin. That is to say there is no rounding or division involved.

Any suggestions that don't involve adding columns or re-creating the sheet?

 

[Micron]

Did you check the format of the target cells? They probably defaulted to scientific. Altering the format may fix the display. If so, I'd say that the issue is in the original data. You see 0.00 but the value is actually 3.63797880709171E-12. You may need to find a way to examine the copied value first - perhaps by pasting into notepad. That will strip all formatting from the pasted value and show its true value.

 

[Artik]

In my opinion, the problem lies in the formulas in the source cells. You say that the cells are formatted to display 2 decimal places. But that is just displaying, not storing the expected values. The result of an action, such as division, can be a value that contains more than 2 digits of the fractional part. You can check what your source cells store by changing their formatting to General.
If you care about values with an accuracy of two places, wrap the current formulas additionally with the ROUND function.

=ROUND(Your_formula, 2)

Artik

 

[BYahr]

While I can agree with the above-mentioned possible issues, I know they are not the cause for the change of the values. There is no division involved. All the values in the source documents are entered as dollars and cents, so no fractional amounts anywhere. When I check the source data by changing the format to General, no fractional or scientific notation appears. (No, I didn't check them all as that would be ludicrously time consuming.) All the data comes from either a direct entry or a formula adding or subtracting from previous cells. I could use the ROUND function as suggested, but I prefer not to give the impression that the amounts could be changing.

Any other suggestions?

 

[Fluff]

It will be down to floating point arithmetic. Floating-point arithmetic may give inaccurate result in Excel - Microsoft 365 Apps

 

[BYahr]

It will be down to floating point arithmetic. Floating-point arithmetic may give inaccurate result in Excel - Microsoft 365 Apps

Thanks for the link. It does help explain what is happening.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[BYahr]

Thanks for the link. It does help explain what is happening.

I went in and tried the ROUND function without success. If I understand correctly, the ROUND command would need to be added after the copy and paste special action. This would mean entering the formula in every cell and that is not practical. Even then it doesn't change the value stored to the rounded figure and the cell already displays the correct format. Perhaps there is not a 'sweeping' solution (one action correction) and I still need to adjust the cells that change after the copy/paste action.