Thursday, September 29, 2016

Troubleshooting:Display bug in Excel 2007 and 2010. What to do?

Display bug in Excel 2007 and 2010. What to do?

I've found a display bug that affects Excel 2007 and 2010.
There was a previously documented issue with numbers nearing 65535, but this one affects calculations that should result in 0.

For example,

65.57 - 58.03 - 7.54 = 0
65.57 - 7.54 - 58.03 = -7.99361 x 10^15

but to verify it's a display bug, add an extra helper at the end...

65.57 - 7.54 - 58.03 + 1 = 1

These numbers originally came from a calculation involving each number being in separate cells.
I did test in a new workbook, by manually typing the values into one cell (=65.57-7.54-58.03) and I get the same display result.

I then tested on a separate PC, using Excel 2010, and got the same visualization.

I've tried formatting it, but I use comma or accounting format for most things...
I'm not use to seeing 0.00 randomly inserted in my column of all '-'s

Any suggestions on what to do, or how to work around it?

For what it's worth: Office 2007 on XP SP3, Office 2010 on Win7 x64 SP1



Edit: =2059.32-1879.32-120.48+184.8-244.32 displays 2.27374e-13 as an answer, when it should be 0 as well.

Keys to the Problem Display bug in Excel 2007 and 2010. What to do?

Download SmartPCFixer for Free Now

Neil wrote:
For example,
65.57 - 58.03 - 7.54 = 0
65.57 - 7.54 - 58.03 = -7.99361 x 10^15

Actually, I think you have the examples reversed.  For me:

65.57 - 58.03 - 7.54 = -7.99361E-15
65.57 - 7.54 - 58.03 = 0
65.57 - 7.54 - 58.03 - 0 = -7.10543E-15

I added #3 to make a point:  in this case, #2 results in exactly zero only because Excel forces it to be zero due to the dubious heuristic poorly described under the misleading title "Example When a Value Reaches Zero" at

As I indicated previously, the problem is that none of those numbers are represented exactly.  They are:

65.57:  65.5699999999999,9317878973670303821563720703125
58.03:  58.0300000000000,01136868377216160297393798828125
  7.54:  7.54000000000000,003552713678800500929355621337890625

The order of operations makes a difference only because of the differences in the approximate representations of the intermediate results (pairwise operations).  So:

65.57-58.03:  7.53999999999999,2041921359486877918243408203125
65.57-  7.54:  58.0299999999999,9403144101961515843868255615234375
[EDIT] Well, of course the intermeditate results are different.  I meant:  differences between the approximate representations
relative to the last operand, the latter being closer to 58.03 than the former is to 7.54.

Excel forces #2 to zero only because the representation of 65.57-7.54 is "close enough" to the representation of 58.03 that Excel decides
arbitrarily the subtraction is "close enough" to zero.

But as #3 demonstrates, Excel does that only if the
last subtraction (or addition of operands with opposite signs)
causes a change and is "close enough" to zero.  Since "- 0" does not cause a change, Excel does not employ the heuristic.  (Klunk!)

Neil wrote:
2059.32-1879.32-120.48+184.8-244.32 displays 2.27374e-13 as an answer, when it should be 0 as well.

The explanation is similar.  I could provide the details, but I hope you get the point.

Because of this Display bug in Excel 2007 and 2010. What to do? error, Windows can fail in starting some processes and the system's performance can significantly slow down. In some cases, Windows has problems starting up and the system can freeze.

Lastly, this error Display bug in Excel 2007 and 2010. What to do? can even result in the Blue screen of death error message, just like other critical Windows errors. If any of the above problems and symptoms occur in your PC, then you should immediately seek to resolve this problem.

In order to resolve Display bug in Excel 2007 and 2010. What to do? error or repair the corrupted and damaged Windows files, you will need a Display bug in Excel 2007 and 2010. What to do? error repair tool. There is more than one method of treating this kind of error code, and here is how you can manually do it:

  1. Start the computer and log in as administrator
  2. Go to the start button and select All Programs. Go to Accessories, System Tools, and then System Restore
  3. Click on System Restore and in a new window, select the 'Restore my computer to an earlier time's option and click Next
  4. On the new confirmation window that appears, click Next
  5. Restart your computer after the restoration has been completed

Another Safe way to Fix the Problem: Display bug in Excel 2007 and 2010. What to do?:

How to Fix Display bug in Excel 2007 and 2010. What to do? with SmartPCFixer?

1. Click the button to download Error Fixer . Install it on your computer.  Open it, and it will perform a scan for your computer. The errors will be shown in the scan result.

2. After the scan is finished, you can see the errors and problems which need to be repaired.

3. The Fixing part is finished, the speed of your computer will be much higher than before and the errors have been fixed.

Related: Way to Update & Download SONY SVE14A2X1EH Wireless State Device driver,How to Update & Download SONY SVS1511X9RB Graphics Driver for D2 (Intel) v.9.17 -,Method to Download SONY VGN-CR290N Realtek High Definition Audio Driver v.,Download SONY VGN-FS710/W Utilities DLL Update v. driver,Method to Update & Download SONY VGN-NR460E/P Intel Graphics Media Accelerator Driver v.,How to Fix Error 0x0000c1f5 Solución?,Error 0x80071a91 Win7 Fix Patch,Error 0x800ccc60 Windows Live Mail [Solved],Error Message 0x800ccc79 Outlook 2000 Fix,What is Error 0xc1ab0001?,[Answered] x64 saplugin dll,How to Fix Problem - Kb943729 Download?,Troubleshooting: Bccode D1 Error,Java Runtime Tech Support,Troubleshooting: ERROR NO SUCH GROUP Error,Solution to ashoutxt.dll Missing Error,Solution to cxcore096.dll Missing Error,Where can I Find dsauth.dll File,How to Fix msde.dll Not Found Error,Best Method to Fix psizewizres.dll Error
Read More: Fast Solution to Error: Does Easy Transfer work from XP to XP?__,How to Fix Problem - Driver ISSues Error\"PCI Simple Communications Controller does not have a driver\"?,Fast Solution to Problem: Downloading files to UNC shares using IE9 always fails the first download attempt.,How Can You Fix - Downloaded windows Live mail but cannot access e-mail account.?,Fast Solution to Problem: dont know windows 8 admin password,Data validation question. Maintaining and updating named ranges.,Desktop Gadget Gallery on Windows 7,delete empty rows,Deleting a page and also how can I copy an exact page lay out on a new page,Date Formula for 1st Sunday of each Month (3rd to 9th)

No comments:

Post a Comment