if(a1=b1=c1,1,0) is 0 when should be 1, why?

[bobby7007]

I have a1, b1, and c1 all with the number 1 in them. Excel is telling me
that the result of if(a1=b1=c1,1,0) in cell d1 is 0 or that the if statement
is false. Why?

This is driving me nuts. If I do if(a1=b1,1,0) it is equal to 1, if
(b1=c1,1,0) is equal to 1, and if(c1=a1,1,0) is also equal to 1. Can I not
do three equal cells in an if statement?

 

[PCLIVE]

=IF(OR(A1=B1,B1=C1,C1=A1),1,0)

HTH,
Paul

 

[Bernie Deitrick]

Bobby,

Your "three equal cells"

a1=b1=c1

are evaluated left to right by Excel. A1=B1 returns TRUE or FALSE, so the rest is then evaluated as

TRUE = C1

or else

FALSE = C1

You could use

AND(A1=B1,B1=C1)

No need for the third comparison (A1=C1) because of some pesky math rule that says you don't need to
do that one as well ;-)

HTH,
Bernie
MS Excel MVP

 

[T. Valko]

if(a1=b1=c1,1,0)

Can I not do three equal cells in an if statement?

Not the way you tried it.

You can try one of these:

=--(COUNTIF(A1:C1,A1)=3)

=IF(AND(A1=B1,A1=C1),1,0)

Biff