Lengthy and weird, but interesting

[Dan Wilson]

Good day. I am using Excel 2002 with Windows ME. I have
a worksheet that I use to produce order sheets for
products that I sell. On the worksheet there is a
reconciliation area where the following formula resides.

Cell E103 contains the formula =(F84-E102)

F84 contains a formula that sums all of the products sold.
E102 contains the amount of money tendered for the sale.

Ideally, the result in E103 should be 0 (formatted as
Currency with 2 decimal points = $0.00). One other thing
about cell E103 is that the formatting for the cell is
pattern tan in its normal state.

I have added the following Conditional Formatting to E103;

If Cell Value not equal 0 format as pattern red

This was working fine and would make the E103 cell
background red if the difference between the amount owed
and the amount tendered was not zero, either plus or minus.

I then decided to copy the worksheet to a new workbook to
enter a different customer sale. When I got to the E103
computation, the result was showing $0.00, but the
background was red. I tried re-entering the formula, the
conditional formatting, and the cell formatting, but
nothing worked until I removed the parenthesis in the E103
formula and made it look like this;

Cell E103 =F84-E102

This fixed the problem. I went back to the worksheet that
I copied the formula from and it has the parenthesis in
the formula and it works. I even went back and put the
parenthesis back into the E103 formula to confirm the
problem and sure enough, with the parenthesis in the
formula, the conditional formatting does not work and the
cell background stays red no matter what the value is.

Any reason for this? I even tried reformatting the F84
and E102 cells to show up to 6 decimal places, thinking
that maybe the calculation of the sales prices were not
ending in even currency amounts, but that was not the case.

Every once in a while I stumble across something in Excel
that tweaks my mind and leaves me mumbling to myself. If
anyone can help with this one, please do so.

Thanks, Danno...

 

[Dan Wilson]

Good day again. I just thought of additonal data to go
with the original problem. After creating the new
workbook, I deleted 7 of 20 product rows to allow room for
entering Notes at the botton of thw worksheet. All of the
formulas seemed to have converted properly.

Thanks, Danno...

 

[Harlan Grove]

Cell E103 contains the formula =(F84-E102) ....
I have added the following Conditional Formatting to E103;

If Cell Value not equal 0 format as pattern red ....
I then decided to copy the worksheet to a new workbook to
enter a different customer sale. When I got to the E103
computation, the result was showing $0.00, but the
background was red. I tried re-entering the formula, the
conditional formatting, and the cell formatting, but
nothing worked until I removed the parenthesis in the E103
formula and made it look like this;

Cell E103 =F84-E102

This fixed the problem. I went back to the worksheet that

....

Excel provides fudge factors for simple arithmetic operations not nested
within parentheses. That's the sole cause of the different behavior. Format
as 0.000E+00 to confirm. You'll see that the formula that doesn't trigger
conditional formatting evaluates to zero exactly while the other evaluates
to a very small but not zero value. Moral: always round to match formatting,
e.g., if the format were #,##0.00, make your formula

=ROUND(F84,2)-ROUND(E102,2)