Problem with conditional

[AndréCamargo]

I have a problem with de function “if”

I edit 3 consecutives cells with the same value:

1234 e.g.

B1347 1234
B1348 1234
B1349 1234

in another cell i write the funtion: =IF(B1347=B1348=B1349;1;0)

the function always returns 0, but the values are the same.

What i doing wrong?

P.S:

i created a new spreadsheet and i tried to do the same.

I edited 3 consecutives cells with the same value:

1234 e.g.

A1 1234
A2 1234
A3 1234

in another cell I wrote the function: =IF(B1347=B1348=B1349)

The function returned the value FALSE

but when i edited A1 and put the value 1, the function returned the value TRUE

I tried other combinations, and only when the first cell that appears in the function was 1, the function returns TRUE.

note: i am Brazilian and i use the portuguese version of Calc

[manj_k]

OP: @AndréCamargo

[mariosv]

The comparison result B1347=B1348 only can be 0 / 1, and this is compared with B1349.

IF((B1347=B1348)*(B1347=B1349);1;0)

For a long range you can use for example:

=COUNT($B$1347:$B$1400;$B1347)=ROWS($B$1347:$B$1400)

[AndréCamargo]

The comparison result B1347=B1348 only can be 0 / 1, and this is compared with B1349.

It sounds crazy for me.

The comparison should be made with the 3 values, and not with the value and the result.

But thanks for your help!

[qubit1]

@AndréCamargo – Based on your description of the problem, mariosv’s answer seems to work for me. Perhaps we’re not understanding the problem you’re seeking to solve?

Please clarify further, so we can help you.

Thanks!

[froz]

IF(A1=A2=A3;0;1) —> this is wrong syntax and I am little bit amazed it does not return a syntax error. By definition you can only have a single comparison like IF(A1=A2;0;1). If you need more then single comparison you need to solve it by using nested IF or using AND function etc. See other posts in this thread for details.

[ROSt53]

@AndréCamargo - My following statement is based on my understanding that you want to have 3 cells (A1, A2, A3) linked to another cell B1. B1 shall have the value 0 when the cells A1, A2, A3 have the same value and B1 shall have the value 1 if at least one of the cells A1, A2, A3 has a different value.

I tried a different approach than @mariosv and did a test with LibO 3.6.5 and found that Calc reacts correct if the functions are used in the right way. Error messages are missing when the functions are not used as required in the syntax.

I set a1=1 a2=1 a3=1

First I tested the logical function AND and wrote in b1:

=AND(A1=A2,A2=A3)

whenever the values in a1, a2, a3 are the same b1 shows TRUE, which is correct

whenever there is at least one of the values of a1, a2, a3 is different from the others, b1 shows FALSE, which is correct again

Next I combined AND with IF and wrote in b2:

=IF (AND(A1=A2,A2=A3),0,1)

whenever the values in a1, a2, a3 are the same b2 shows 0, which is correct

whenever there is at least one of the values of a1, a2, a3 is different from the others, b2 shows 1, which is correct again.

Thus above IF function could solve your problems - if my understanding is correct.

However your IF function

=IF(B1347=B1348=B1349)

triggered thoughts that this cannot work because of the string

B1347=B1348=B1349

has a problem and the function IF is not completed according to its syntax.

My test is using in b3

=IF(A1=A2=A3)

which shows a wrong results. Exactly the opposite of what is should be. b3 shows FALSE whenever the values in a1, a2, a3 are the same, and TRUE whenever there is at least one of the values of a1, a2, a3 is different from the others.

IMHO, and as far as I understand the syntax of the IF function, an error message should appear indicating the formula is not completed and not show any value. Additionally the string “A1=A2=A3” should also lead to error message as a computer can in one action only compare two values. That is why there is the AND function.

I continued the test with the IF function written as

=IF (A1=A2,A2=A3,0,1)

and results are inconsistent; sometimes correct and sometimes wrong. Above said about the string “A1=A2=A3” an error message should pop up here as well.

I am open for discussions.