Hi - I'm stumped by this problem ... Excel 2007 on Win7 Pro 32



I have a simple test: =IF(G262=J265,"OK","ERROR")

My problem: both G262 and J265 contain the value 8805.13, but instead of OK, I get ERROR!

G262 gets its value from a sum =SUM(G2:G261)

J265 gets its value from an addition: =E262+F262+I262+J262

I have ensured all fields have the same format (was currency, tried number, made no difference).

I have also tried forcing for Value (e.g. IF(VALUE(G262)=VALUE(J265),"OK","ERROR") but that makes no difference.

But, if I manually type 8805.13 in G262, the IF works as it should!!!

What is going on here???

OK, I have found that if I evaluate the formula, I can see that

G262 gets a value of 8805.1300000000001 while J265 is 8805.13.

But stepping into the evaluation, SUM(G2:G261) = 8805.13, yet this is fed to the IF as 8805.1300000000001.

What is going on here?

OK, there is more. Even though the sum if of numbers with only two decimal places, the sum itself is 8805.13000000001 !!!

So the only way I can get a correct result is by using TRUNC(SUM(G2:G261),2).

Weird, as I have several worksheets all very similar, but only this one produces this odd result.

Thanks Bill!!!

have a read here to understand what is happening....


http://www.cpearson.com/excel/rounding.htm