Excel calculation error?
Hi all,
Excel 2K SP3, Win2kSP4, Pentium4
I have the following formula in a workbook of mine:
=((CE145-CJ145)+(IF(BY145=0,0,BT145/BY145)-(IF(BZ145=0,0,BU145/BZ145))))*CS1
45
including all significant (non zero) decimal places, CE145 and CJ145 are
both 1.564, BY145 and BZ145 are 39, BT145 & BU145 are both 0, and CS145 is
26400.
the answer should be 0. However, the answer is
apparently -7.34412530789541E-12 (-0.000000000007344..)
when I go through the formula a reference at a time F9'ing everything, it
turns out to be 0. But when I F9 on (CE145-CJ145), I get a very small
negative number (-2.77555756156289E-17). if I F9 on CE145-CJ145 (not
including the brackets this time) I get 0.
I realize that the brackets around CE145-CJ145 are slightly extraneous, but
if I remove those brackets, (and the extra brackets around the IFs and the
second IF) so that the formula is
=(CE145-CJ145+IF(BY145=0,0,BT145/BY145)-IF(BZ145=0,0,BU145/BZ145))*CS145
and I evaluate everything before *CS145 (whether or not I include the
brackets), I get that tiny little number
WTF?
I mean I can fix the formula to ignore tiny little numbers, but what's going
on? This is a spreadsheet app that my company uses and I'm responsible for
updating, so when one of the users showed me this I had to scramble to
figure out that Excel is (apparently) tripping up here.
|