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

B

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?
 
B

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top