true false

[justme1122]

Ok so this is no doubt way simple but....
I have some cells with the same text in them.


=a1=b1
comes back true

=b1=c1
comes back true

=a1=b1=c1
Comes back false.

Any ideas why last formular comes back false when the other 2 prove it to be true?

 

[T. Valko]

Ok so this is no doubt way simple but....
I have some cells with the same text in them.


=a1=b1
comes back true

=b1=c1
comes back true

=a1=b1=c1
Comes back false.

Any ideas why last formular comes back false when the other 2 prove it to be true?

Let's assume the cells all contain the number 10.

Here's how those expressions get evaluated:

=A1=B1 = TRUE (10=10 = TRUE)
=B1=C1 = TRUE (10=10 = TRUE)
=A1=B1=C1 = FALSE (10=10 = TRUE = 10 = FALSE)

 

[indiantrix]

When I do it, I get the same result. It looks like use of the F9 key provides a hint of the reason: when the first part of the formula is calculated (=A1=B1) this equates to "TRUE". And THEN the second
part of the formula kicks in... and it now becomes (=TRUE=C1) which
yields a "FALSE" for obvious reasons.
Larry.

 

[Aladin Akyurek]

Ok so this is no doubt way simple but....
I have some cells with the same text in them.


=a1=b1
comes back true

=b1=c1
comes back true

=a1=b1=c1
Comes back false.

Any ideas why last formular comes back false when the other 2 prove it to be true?

The following re-writes might also help..

=(A1=B1)=C1

=A1=(B1=C1)

 

[energman58]

It should be a logical AND:

a1=b1 AND b1 = c1;

Either =And(a1=b1,b1=c1) or =(a1=b1)*(b1=c1) will return the equivalent of TRUE

a1=b1=c1 evaluates as

A1=B1 returns TRUE
TRUE = C1 returns FALSE (as C1 is a number)
​

A1=(B1=C1) or similar variants evaluates in the same way except the brackets determine which bit of the expression is evaluated first:

B1=C1 returns TRUE

A1=TRUE returns FALSE (as A1 is a number)
​

And so on. The only way to get the result you are wanting (TRUE if a1, B1 and C1 are the same) is to use AND or the algbraic equivalent - it helps me to think of TRUE and FALSE as 1 and 0 respectively: AND is equivalent of multiplication OR is addition:

TRUE*TRUE = 1*1 = 1 = TRUE (and)
TRUE*FALSE = 1*0 = 0 = FALSE (and)
​

but

TRUE + FALSE = 1+0 = 1 = TRUE (or)
​

So you can write an equivalent of an AND as

(A1=B1)*(B1=C1)*(C1=D1) etc which will return TRUE only if all the evaluations are TRUE

You can use this to do some interesting things - like use it to count how many true evaluations there are and use this to determine what happens next. So maybe you have some rules in a football competition that says if you win more than 3 games out of 5 you get a bonus point or something you can have a simple expression which says something like if((a1>A2)+(b1>b2)+(c1>c2)+(d1>d2)+(e1>e2)>=3,bonus, no bonus) which would be very involved to write as a series of nested IF statements.