Calculation Error

The following cells have the following values in EXCEL 2007

A1: 192.32
B2: 16.28
C2: 93.04

The formula A1-B1-C1=0 returns FALSE
The formula A1=(B1+C1) returns TRUE

What!?!?!

Further...
$A$1-B$1-$C$1>-0.0000000000001 returns TRUE
But..
$A$1-B$1-$C$1>-0.00000000000001 returns FALSE

Like I said... WHAT?!?!?!

Finally...
if you put the formula =A1-B1-C1 in cell D1, then the value is shown as 0 and the formula D1=0 returns TRUE

I have reproduced this on three computers, on freshly minted spreadsheets - definitely something weird going on. Can anyone else reproduce or explain this. I haven't found any other combination of numbers that does this

Thanks.

Assuming you meant B1 and C1 instead of B2 and C2, using your numbers and formulas in the cells you specified I get:

=A1-B1-C1=0 returns FALSE
=A1=(B1+C1) returns FALSE

=$A$1-B$1-$C$1>-0.0000000000001 returns TRUE
=$A$1-B$1-$C$1>-0.00000000000001 returns TRUE

and finally, in D1
=A1-B1-C1 gives correct result of 83 - not 0
so, of course, =D1=0 returns FALSE

All results are correct.

Originally Posted by TMShucks

Firstly, you refer initially to A1, B2 and C2 but your formulae refer to A1, B1 and C1

But, 16.28 + 93.04 = 109.32 not 192.32

Regards

Quite right. I meant 109.32. Try it...

Have a look at this: http://www.airplaystudios.co.uk/book2.xlsx
cells D1 and E2 are doing the same thing but get different results,

Well now I see the "WHAT?!?!?!"

To further point out the inconsistency try this:

In your formula =A1-B1-C1=0 (which returns FALSE) highlight the A1-B1-C1 portion in the formula bar. Hit F9 and it calculates to 0 so you have =0=0. Hit enter and you get TRUE!
Hit Undo to return formula to its original form and you get FALSE.

I'm sure the wise ones can explain it. I can't.

Thanks for checking. At least I know I'm not going mad! As I said, if you change the numbers (to ones that add up), it operates as expected. change them back and it's all wrong again...

No, you're not going mad. I recreated it on a new spreadsheet.

I have asked for some input from the Excel gurus on this thread so expect something soon.

A couple of other head scratchers:

=SUM(B1:C1)=A1 is TRUE but =A1-SUM(B1:C1)=0 is FALSE

and

=B1+C1=A1 is TRUE but =A1-(B1+C1)=0 is FALSE

I think I'll just use ROUND() - these numbers represent money, so 14 decimal places aren't necessary. I'm amazed that this sort of issue can arise at two decimal places - very disappointing.

In case anyone is still reading this... OpenOffice copes with this perfectly