Ask Your Question
0

problem with conditional [closed]

asked 2012-06-03 13:38:33 +0200

this post is marked as community wiki

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 flag offensive 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

Comments

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

3 Answers

Sort by » oldest newest most voted
1

answered 2012-06-03 15:09:44 +0200

this post is marked as community wiki

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)

edit flag offensive delete link more
0

answered 2013-03-14 05:15:22 +0200

this post is marked as community wiki

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.

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 ... (more)

edit flag offensive delete link more
-1

answered 2012-06-03 17:06:18 +0200

this post is marked as community wiki

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.

But thanks for your help!

edit remove flag flag offensive (1) delete link more

Comments

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

qubit gravatar imagequbit ( 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.

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

Question Tools

Stats

Asked: 2012-06-03 13:38:33 +0200

Seen: 292 times

Last updated: Mar 14 '13