numeric calculation in logical formula

[Guest]

cell a1 = 1.2999, cell b1=1.3000

I want a "if" formula like this:

=if(((b1-a1)=0.0001),true,false)...it seems in the logical formula, the
calculation is ignored and it always gives a false result.

Can anybody help how to do it??

Thanks

 

[Anne Troy]

Interesting. When I do b1-a1 (only), I get this result:
0.0000999999999999890


*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com

 

[Chip]

Yea there is a wierd rounding issue there. This works for me:

=IF(ROUND(B1-A1,3)=0.001,TRUE,FALSE)

 

[Tom Ogilvy]

You will be hard pressed to make that comparison because of the way the IEEE
standard dictates the storage of floating point numbers.

If you want to know that it is exactly a difference of .00001

you might use an approach like:

=IF(ABS(ABS(B1-A1)-0.0001) < 0.00001,TRUE,FALSE)

you would need to play with it to be as restrictive as you want. In any
event, just like you can't eactly represent 1/3 in decimal, there are many
decimal numbers that can't be exactly represented in binary.

This characterstic isn't unique to Excel:

http://support.microsoft.com/default.aspx?scid=kb;en-us;48606
XL: Comparison of Values Does Not Return Correct Result

http://support.microsoft.com/default.aspx?scid=kb;en-us;78113
XL: Floating-Point Arithmetic May Give Inaccurate Results

http://support.microsoft.com/default.aspx?scid=kb;en-us;42980
(Complete) Tutorial to Understand IEEE Floating-Point Errors


http://support.microsoft.com/default.aspx?scid=kb;en-us;165373
Rounding Errors In Visual Basic For Applications

http://support.microsoft.com/default.aspx?scid=kb;en-us;69333
HOWTO: Work Around Floating-Point Accuracy/Comparison Problems

 

[Guest]

Hi Everyone,

Thanks for all the responses. It seems the issue hereis going into decimals.
I'm goint to try the suggestions. Another option I'm considering is to have
a secondary cell where c1=a1*10000. which means the value goes to 2999 and
not1.2999. This will then take the value for the sake of the logical formula
out of decimals, thus 12999 and 13000. Will keep posted.

Thanks again

 

[Guest]

If you multiply the value with 10000 in a secondary cell and use the
secondary cells for the logical fromula is works. Thanks again

ChrisThanks