Excel 2000 Calculation Error ???
Posted by Ed Ziarko on April 09, 2001 10:39 PM
In Excel 2000, I'm encountering a calculation problem that prevents me from using a logical comparison operator to determine if two cells are equal.
Cell A1: enter 39917.97 [formatted as a number, with 14 decimal places]. Cell A2: enter 39571.74 [formatted as a number, with 14 decimal places]
Cell A3: enter the following formula: =A1-A2 [formatted as a number, with 14 decimal places]
Question: In cell A3, where does the "3" at the 12th decimal place come from?
Posted by Aladin Akyurek on April 10, 2001 5:31 AM
==================
Such imprecision is to be expected with real numbers. Use long integer instead to avoid the garbage of insignificant digits. Cell formatting in fact causes this when there aren't enough decimals to fill the memory that you reserve for a number.
You can make use of the following construction for logical evaluations:
=(A1*1E+14)<(A2*1E+14)
Aladin
Posted by Ed Ziarko on April 10, 2001 9:41 PM
Aladin, thanks for your reply.
I discovered this problem while trying to incorporate the "Capital Gain Tax Worksheet - Line 40" found on page 33 of the current Forms and Instructions for the year 2000 1040(Fed) into a spreadsheet using simple calculations and trying to make decisions based on the results. When things didn't work out the way I expected, I started expanding the decimals displayed to see which cells were causing the problem. These 'spurious' digits seem to appear with no discernable pattern or consistency. How can I prevent this in the future without resorting to adding more calculations, as you've indicated in your reply, or is there no other way? (Before your answer I thought I might have a bug in the program.)
Ed
Posted by Aladin Akyurek on April 11, 2001 9:05 AM
Ed
I don't know if there is another way.
Aladin
Posted by Ed Ziarko on April 11, 2001 10:54 PM
Aladin,
See MARK W. 10:50:21 04/10/01 (1) reply to Stephanie Lee above. Q78113 at Microsoft Support explains, (I think), the problem and a work around - Use the ROUND function or Precision as Displayed [calculation option, under tools]
Thanks alot, Ed
