compare to cell values

[ridhirao22]

Hi,

Using Excel 2003.

I have file where I am trying to compare the cells values as

= A1=B1 in Cell C1 and get results as below, even though both the cells are same and matching How can I get either true or 0 when the cells match?

For eaxmaple
A B C
0.0011 0.0011 true
(0.0026) (0.0026) false
(0.0021) (0.0021) false
0.0001 0.0001 true

Any help is appreciated.

TIA,
RR

 

[ridhirao22]

I think I figure by rounding the cell values.

Thanks!

 

[SkipVought]

Hi,

Comparing decimal values can be tricky. Normally this would work...
[tt]
=A1=B1
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mmerlinn]

[ ]
Why are you trying to get a false when both cells are negative?

=A1=B1 will not give you a false when both cells are negative and equal.

mmerlinn

http://mmerlinn.com

Poor people do not hire employees. If you soak the rich, who are you going to work for?

"We've found by experience that people who are careless and sloppy writers are usually also careless and sloppy at thinking and coding. Answering questions for careless and sloppy thinkers is not rewarding." - Eric Raymond

 

[SkipVought]

even though both the cells are same and matching How can I get either true or 0 when the cells match?

The OP said NOTHING about how negative values act any different.

If you think that parentheses MAKE a value negative, you are mistaken. Parentheses or red shading CAN indicate a negative number, but that condition is merely a DISPLAY FORMAT.

So if the requirement is for negative values to have a different specification, then it ought to be explicitly and clearly stated, in which case a solution could be...
[tt]
=AND(A1=B1,ABS(A1)=A1)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lionelhill]

and if you do run into the problems of comparing decimals in the presence of rounding errors, try
=if(abs(A1-B1)<TinyValue, TrueResult, FalseResult)
where TinyValue is a number that is much smaller than is relevant to you, but larger than rounding errors.

 

[ridhirao22]

Thank you lionelhil, Skip.