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.

Excel calculation error?
 

As a extra note, if I change the formula to (CE145-CJ145)*CS145 +
(IF(...)-IF(...))*CS145, the answer is 0.

"Mike" wrote in message
...
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.