Help with Formula

[supersub15]

I need to create a formula that compares 2 amounts in cells C9 and E9.
If the difference between the 2 amounts is equal to or greater than
1000000 (1 million), then the answer to my question is "Yes".
Otherwise, it's no. I got this far:

=IF(AND(C12="Acceptable Level",LEN(C9-E9)>=7),"Yes","No")

It works fine if the C9 is greater than E9. But if E9 > C9, then the
minus (-) is counted as a character and I get a "Yes", even though the
amount is lower than 1000000.

1. I'm not sure how to fix this problem.
2. Would it be possible to display the difference amount (C9 - E9 OR
E9 - C9) instead of the "Yes"?

Thanks for any help.

Carl

 

[Guest]

=IF(AND(C12="Acceptable Level",C9-E9>=1000000),"Yes","No")

or

=IF(AND(C12="Acceptable Level",ABS(C9-E9)>=1000000),"Yes","No")

 

[JW]

This what you are after?
=IF(AND(C12="Acceptable Level",C9-E9>=1000000),C9-E9,E9-C9)

 

[supersub15]

This what you are after?
=IF(AND(C12="Acceptable Level",C9-E9>=1000000),C9-E9,E9-C9)









- Show quoted text -