Excel 2007 Stricken with Bizarre Calculation Bug

Front and center in any "working guy" software arsenal is Microsoft Excel, the spreadsheet that's become a de facto industry standard. Earlier this week, reports surfaced of a strange error in Excel 2007 which caused calculations involving the number 65,535 (and thereabouts) to return wildly inaccurate results.

You can try it for yourself in Excel by multiplying 850 by 77.1. Excel will return 100,000 instead of 65,535. Similar operations that should return 65,535 will also come back incorrect, and there's a problem with 65,536, too. The problem is only present in Excel 2007; earlier versions of Excel are not affected.

Microsoft has traced the problem back to a floating point issue and how results are displayed within a cell in Excel. Microsoft says the calculation is actually done correctly, it's just that when it comes time to show the result on screen, Excel chokes. For example, if you multiply that "100,000" above by 2 and put the answer in a new cell, you'll get 131,070, not 200,000. However, this isn't reliable either: Try adding one and you get 100,001, not 65,536.

Microsoft says a fix is on the way and is in the final stages of testing. I'll update this post when the patch is available; hopefully Windows Update will also push it down automatically.

Improve PC Performance: Defragment Your Hard Drive

Windows XP stores files on your hard drive in blocks of data called sectors. When a file is stored in several sectors that aren't located next to each other, it becomes fragmented, and a hard drive with lots of fragmented files can slow down PC performance. The Disk Defragmenter Utility scans your hard drive and rearranges scattered files so the hard drive runs as efficiently as possible. Running Disk Defragmenter once a month is enough for the average home user.

Defragmenting a large partition can take a long time. Plan to run Disk Defragmenter when you're not using your computer.

1. Go to Start | All Programs | Accessories | System Tools | Disk Defragmenter.

   

   Sometimes Disk Defragmenter conflicts with other programs running on the computer. If this happens, shut down all other programs including anti-virus programs.

2. Select the drive you want to defragment and click Analyze (see Figure 1).

3. Look at the horizontal bar in Figure 2 labeled Estimated Disk Usage Before Defragmentation. Red represents fragmented files. If you see lots of red, you should defragment the disk.

4. When the analysis is complete, a small window pops up telling you if you need to defragment the disk. Click View Report for more details.

5. The Analysis Report shown in Figure 3 provides all kinds of statistics about the hard disk. Click Close to continue.

6. Click the Defragment button to defragment the disk.

   

   If Disk Defragmenter freezes and won't shut down, press CTRL-ALT-DELETE to shut down the program.