Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenMon, 17 Jun 2013 00:11:18 +0200problem with conditionalhttps://ask.libreoffice.org/en/question/3067/problem-with-conditional/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 CalcSun, 03 Jun 2012 13:38:33 +0200https://ask.libreoffice.org/en/question/3067/problem-with-conditional/Comment by manj_k for <p>I have a problem with de function "if"</p>
<p>I edit 3 consecutives cells with the same value:</p>
<p>1234 e.g.</p>
<p>B1347 1234
B1348 1234
B1349 1234</p>
<p>in another cell i write the funtion: =IF(B1347=B1348=B1349;1;0)</p>
<p>the function always returns 0, but the values are the same.</p>
<p>What i doing wrong?</p>
<p>P.S:</p>
<p>i created a new spreadsheet and i tried to do the same.</p>
<p>I edited 3 consecutives cells with the same value:</p>
<p>1234 e.g.</p>
<p>A1 1234
A2 1234
A3 1234</p>
<p>in another cell I wrote the function: =IF(B1347=B1348=B1349)</p>
<p>The function returned the value FALSE</p>
<p>but when i edited A1 and put the value 1, the function returned the value TRUE</p>
<p>I tried other combinations, and only when the first cell that appears in the function was 1, the function returns TRUE.</p>
<p>note: i am Brazilian and i use the portuguese version of Calc</p>
https://ask.libreoffice.org/en/question/3067/problem-with-conditional/?comment=19138#post-id-19138OP: @André CamargoMon, 17 Jun 2013 00:11:18 +0200https://ask.libreoffice.org/en/question/3067/problem-with-conditional/?comment=19138#post-id-19138Answer by ROSt52 for <p>I have a problem with de function "if"</p>
<p>I edit 3 consecutives cells with the same value:</p>
<p>1234 e.g.</p>
<p>B1347 1234
B1348 1234
B1349 1234</p>
<p>in another cell i write the funtion: =IF(B1347=B1348=B1349;1;0)</p>
<p>the function always returns 0, but the values are the same.</p>
<p>What i doing wrong?</p>
<p>P.S:</p>
<p>i created a new spreadsheet and i tried to do the same.</p>
<p>I edited 3 consecutives cells with the same value:</p>
<p>1234 e.g.</p>
<p>A1 1234
A2 1234
A3 1234</p>
<p>in another cell I wrote the function: =IF(B1347=B1348=B1349)</p>
<p>The function returned the value FALSE</p>
<p>but when i edited A1 and put the value 1, the function returned the value TRUE</p>
<p>I tried other combinations, and only when the first cell that appears in the function was 1, the function returns TRUE.</p>
<p>note: i am Brazilian and i use the portuguese version of Calc</p>
https://ask.libreoffice.org/en/question/3067/problem-with-conditional/?answer=14583#post-id-14583@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.
Thu, 14 Mar 2013 05:15:22 +0100https://ask.libreoffice.org/en/question/3067/problem-with-conditional/?answer=14583#post-id-14583Answer by André Camargo for <p>I have a problem with de function "if"</p>
<p>I edit 3 consecutives cells with the same value:</p>
<p>1234 e.g.</p>
<p>B1347 1234
B1348 1234
B1349 1234</p>
<p>in another cell i write the funtion: =IF(B1347=B1348=B1349;1;0)</p>
<p>the function always returns 0, but the values are the same.</p>
<p>What i doing wrong?</p>
<p>P.S:</p>
<p>i created a new spreadsheet and i tried to do the same.</p>
<p>I edited 3 consecutives cells with the same value:</p>
<p>1234 e.g.</p>
<p>A1 1234
A2 1234
A3 1234</p>
<p>in another cell I wrote the function: =IF(B1347=B1348=B1349)</p>
<p>The function returned the value FALSE</p>
<p>but when i edited A1 and put the value 1, the function returned the value TRUE</p>
<p>I tried other combinations, and only when the first cell that appears in the function was 1, the function returns TRUE.</p>
<p>note: i am Brazilian and i use the portuguese version of Calc</p>
https://ask.libreoffice.org/en/question/3067/problem-with-conditional/?answer=3076#post-id-3076The 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!Sun, 03 Jun 2012 17:06:18 +0200https://ask.libreoffice.org/en/question/3067/problem-with-conditional/?answer=3076#post-id-3076Comment by froz for <p>The comparison result B1347=B1348 only can be 0 / 1, and this is compared with B1349.</p>
<p>It sounds crazy for me.</p>
<p>The comparison should be made with the 3 values, and not with the value and the result.</p>
<p>But thanks for your help!</p>
https://ask.libreoffice.org/en/question/3067/problem-with-conditional/?comment=14680#post-id-14680IF(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.Sat, 16 Mar 2013 08:03:20 +0100https://ask.libreoffice.org/en/question/3067/problem-with-conditional/?comment=14680#post-id-14680Comment by qubit for <p>The comparison result B1347=B1348 only can be 0 / 1, and this is compared with B1349.</p>
<p>It sounds crazy for me.</p>
<p>The comparison should be made with the 3 values, and not with the value and the result.</p>
<p>But thanks for your help!</p>
https://ask.libreoffice.org/en/question/3067/problem-with-conditional/?comment=14514#post-id-14514@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!Wed, 13 Mar 2013 08:18:22 +0100https://ask.libreoffice.org/en/question/3067/problem-with-conditional/?comment=14514#post-id-14514Answer by m.a.riosv for <p>I have a problem with de function "if"</p>
<p>I edit 3 consecutives cells with the same value:</p>
<p>1234 e.g.</p>
<p>B1347 1234
B1348 1234
B1349 1234</p>
<p>in another cell i write the funtion: =IF(B1347=B1348=B1349;1;0)</p>
<p>the function always returns 0, but the values are the same.</p>
<p>What i doing wrong?</p>
<p>P.S:</p>
<p>i created a new spreadsheet and i tried to do the same.</p>
<p>I edited 3 consecutives cells with the same value:</p>
<p>1234 e.g.</p>
<p>A1 1234
A2 1234
A3 1234</p>
<p>in another cell I wrote the function: =IF(B1347=B1348=B1349)</p>
<p>The function returned the value FALSE</p>
<p>but when i edited A1 and put the value 1, the function returned the value TRUE</p>
<p>I tried other combinations, and only when the first cell that appears in the function was 1, the function returns TRUE.</p>
<p>note: i am Brazilian and i use the portuguese version of Calc</p>
https://ask.libreoffice.org/en/question/3067/problem-with-conditional/?answer=3073#post-id-3073The 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)Sun, 03 Jun 2012 15:09:44 +0200https://ask.libreoffice.org/en/question/3067/problem-with-conditional/?answer=3073#post-id-3073