# problem with conditional [closed]

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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

edit retag reopen merge delete

### Closed for the following reason question is not relevant or outdated by Alex Kemp close date 2015-10-26 22:10:28.062004

( 2013-06-17 00:11:18 +0200 )edit

Sort by » oldest newest most voted

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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)

more

This post is a wiki. Anyone with karma >75 is welcome to improve it.

@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.

=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 ...

more

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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.

more

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

Thanks!

( 2013-03-13 08:18:22 +0200 )edit

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.

( 2013-03-16 08:03:20 +0200 )edit