Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenSun, 03 Feb 2013 00:33:10 +0100Calc - if then otherwise otherwisehttps://ask.libreoffice.org/en/question/7411/calc-if-then-otherwise-otherwise/Hi, can anybody tell me, how to interlace an if-clause ?
I.e.: =IF(0<=A1<=80;"A";IF(80<=A1<=95;"B";"C")) doesn't work.
Is it necessary to use an other function therefor ? Or is there no possibility to do that ?
Thanks !Fri, 02 Nov 2012 13:54:59 +0100https://ask.libreoffice.org/en/question/7411/calc-if-then-otherwise-otherwise/Answer by m.a.riosv for <p>Hi, can anybody tell me, how to interlace an if-clause ?
I.e.: =IF(0<=A1<=80;"A";IF(80<=A1<=95;"B";"C")) doesn't work.
Is it necessary to use an other function therefor ? Or is there no possibility to do that ?</p>
<p>Thanks !</p>
https://ask.libreoffice.org/en/question/7411/calc-if-then-otherwise-otherwise/?answer=7435#post-id-7435Another way is the VLOOKUP() with a inner array:
**=VLOOKUP(A22;{0|19;40|19,5;45|20;50|20,5;55|21};2;1)**
To use | as column separator change the default in Menu/Tools/Options/Calc/Formula:
(default is comma, very difficult to see in the formulas, at least for me)
![image description](/upfiles/13518961399055261.png)Fri, 02 Nov 2012 23:45:24 +0100https://ask.libreoffice.org/en/question/7411/calc-if-then-otherwise-otherwise/?answer=7435#post-id-7435Answer by ROSt52 for <p>Hi, can anybody tell me, how to interlace an if-clause ?
I.e.: =IF(0<=A1<=80;"A";IF(80<=A1<=95;"B";"C")) doesn't work.
Is it necessary to use an other function therefor ? Or is there no possibility to do that ?</p>
<p>Thanks !</p>
https://ask.libreoffice.org/en/question/7411/calc-if-then-otherwise-otherwise/?answer=7439#post-id-7439"The value of 80 is ambiguous" , this is the important point and this is also a problem in the first formula =IF(0<=A1<=80;"A";IF(80<=A1<=95;"B";"C")) in this formula there are 2 questions, which contradicts each other: ...A1<=80... and ... 80<=A1... both questions include the "=". In one the "=" must be removed.Sat, 03 Nov 2012 04:34:11 +0100https://ask.libreoffice.org/en/question/7411/calc-if-then-otherwise-otherwise/?answer=7439#post-id-7439Comment by cloph for <p>"The value of 80 is ambiguous" , this is the important point and this is also a problem in the first formula =IF(0<=A1<=80;"A";IF(80<=A1<=95;"B";"C")) in this formula there are 2 questions, which contradicts each other: ...A1<=80... and ... 80<=A1... both questions include the "=". In one the "=" must be removed.</p>
https://ask.libreoffice.org/en/question/7411/calc-if-then-otherwise-otherwise/?comment=7446#post-id-7446That the behavior at value 80 is ambiguous has nothing to do with the formula not working. For the computer there is no contradiction. You should make that flowchart you mention in the comment yourself or read my answer. The second if doesn't care about the conditions in the first.Sat, 03 Nov 2012 11:13:47 +0100https://ask.libreoffice.org/en/question/7411/calc-if-then-otherwise-otherwise/?comment=7446#post-id-7446Answer by ROSt52 for <p>Hi, can anybody tell me, how to interlace an if-clause ?
I.e.: =IF(0<=A1<=80;"A";IF(80<=A1<=95;"B";"C")) doesn't work.
Is it necessary to use an other function therefor ? Or is there no possibility to do that ?</p>
<p>Thanks !</p>
https://ask.libreoffice.org/en/question/7411/calc-if-then-otherwise-otherwise/?answer=7451#post-id-7451@cloph
When I looked at your answer, I was carefully thinking if my answer copies what you already stated.
To be on the safe side in terms of avoiding a missunderstanding I even referred to you with my beginning words: " "The value of 80 is ambiguous" , this is the important point ..."
The difference I saw was that you used the AND statement and I made an approach without AND. Thus, I did not see any conflict.
I also did not say that your formula is not working.
Should I have cause an uncomfortable feeling I do apologize.Sat, 03 Nov 2012 14:22:53 +0100https://ask.libreoffice.org/en/question/7411/calc-if-then-otherwise-otherwise/?answer=7451#post-id-7451Answer by w_whalley for <p>Hi, can anybody tell me, how to interlace an if-clause ?
I.e.: =IF(0<=A1<=80;"A";IF(80<=A1<=95;"B";"C")) doesn't work.
Is it necessary to use an other function therefor ? Or is there no possibility to do that ?</p>
<p>Thanks !</p>
https://ask.libreoffice.org/en/question/7411/calc-if-then-otherwise-otherwise/?answer=7414#post-id-7414If I understand correctly you wish
0-80 = "A"
80-95 = "B"
>95 = "C"
Less than 0=undefined. The value of 80 is ambiguous.
Try this formula
=IF(A1>95,"C",IF(AND(A1<=95,80<A1),"B","A"))
Adjust the logic around 80 to give the result you want.Fri, 02 Nov 2012 15:03:53 +0100https://ask.libreoffice.org/en/question/7411/calc-if-then-otherwise-otherwise/?answer=7414#post-id-7414Comment by cloph for <p>If I understand correctly you wish</p>
<pre><code>0-80 = "A"
80-95 = "B"
>95 = "C"
</code></pre>
<p>Less than 0=undefined. The value of 80 is ambiguous.</p>
<p>Try this formula</p>
<pre><code>=IF(A1>95,"C",IF(AND(A1<=95,80<A1),"B","A"))
</code></pre>
<p>Adjust the logic around 80 to give the result you want.</p>
https://ask.libreoffice.org/en/question/7411/calc-if-then-otherwise-otherwise/?comment=7416#post-id-7416also note that an IF statement might notbe the best way to do things, depending on what the actual goal is, http://help.libreoffice.org/Calc/Array_Functions#FREQUENCY have a look at that function for example - esp. when A B C are only helper-output for a Countif...Fri, 02 Nov 2012 15:16:12 +0100https://ask.libreoffice.org/en/question/7411/calc-if-then-otherwise-otherwise/?comment=7416#post-id-7416Answer by ROSt52 for <p>Hi, can anybody tell me, how to interlace an if-clause ?
I.e.: =IF(0<=A1<=80;"A";IF(80<=A1<=95;"B";"C")) doesn't work.
Is it necessary to use an other function therefor ? Or is there no possibility to do that ?</p>
<p>Thanks !</p>
https://ask.libreoffice.org/en/question/7411/calc-if-then-otherwise-otherwise/?answer=7440#post-id-7440additionally to my answer a few minutes ago I modified the original formula
=IF(0<=A1<=80;"A";IF(80<=A1<=95;"B";"C"))
into
=IF(A1<=80,"A",IF(A1<=95,"B","C"))
and get the following results
A1 Result
<0 A
0 A
79 A
80 A
81 B
95 B
96 C
However, I cannot judge if this is what you want to achieve. Most likely you need to work on the "borders" A1<=80 or A1<=95.
Sat, 03 Nov 2012 04:53:19 +0100https://ask.libreoffice.org/en/question/7411/calc-if-then-otherwise-otherwise/?answer=7440#post-id-7440Comment by ROSt52 for <p>additionally to my answer a few minutes ago I modified the original formula
=IF(0<=A1<=80;"A";IF(80<=A1<=95;"B";"C"))
into
=IF(A1<=80,"A",IF(A1<=95,"B","C"))
and get the following results
A1 Result
<0 A
0 A
79 A
80 A
81 B
95 B
96 C
However, I cannot judge if this is what you want to achieve. Most likely you need to work on the "borders" A1<=80 or A1<=95.</p>
https://ask.libreoffice.org/en/question/7411/calc-if-then-otherwise-otherwise/?comment=7441#post-id-7441it is always helpful to draw a small flowchart to understand the decision tree when using formulas containing "if".Sat, 03 Nov 2012 04:54:32 +0100https://ask.libreoffice.org/en/question/7411/calc-if-then-otherwise-otherwise/?comment=7441#post-id-7441Comment by cloph for <p>additionally to my answer a few minutes ago I modified the original formula
=IF(0<=A1<=80;"A";IF(80<=A1<=95;"B";"C"))
into
=IF(A1<=80,"A",IF(A1<=95,"B","C"))
and get the following results
A1 Result
<0 A
0 A
79 A
80 A
81 B
95 B
96 C
However, I cannot judge if this is what you want to achieve. Most likely you need to work on the "borders" A1<=80 or A1<=95.</p>
https://ask.libreoffice.org/en/question/7411/calc-if-then-otherwise-otherwise/?comment=7447#post-id-7447If that answer was written by a different user, I'd downvote that as well.
Don't just write what another answer did already say (see mine). And you also could edit your own answer to add stuff you forgot initially.Sat, 03 Nov 2012 11:16:54 +0100https://ask.libreoffice.org/en/question/7411/calc-if-then-otherwise-otherwise/?comment=7447#post-id-7447Answer by cloph for <p>Hi, can anybody tell me, how to interlace an if-clause ?
I.e.: =IF(0<=A1<=80;"A";IF(80<=A1<=95;"B";"C")) doesn't work.
Is it necessary to use an other function therefor ? Or is there no possibility to do that ?</p>
<p>Thanks !</p>
https://ask.libreoffice.org/en/question/7411/calc-if-then-otherwise-otherwise/?answer=7418#post-id-7418A generic way to specify two conditions in one IF-clause is to use the **AND** function.
In this case w_whalley's solution is more elegant, but also "wrong", as it won't to get exactly the same result as the "intended" original version
=IF(0<=A1<=80;"A";IF(80<=A1<=95;"B";"C"))
that could be written to make it work like this:
=IF(AND(0<=A1;A1<=80);"A";IF(AND(80<A1;A1<=95);"B";"C"))
This one (as well as the original one) will also *return "C" if the value in A1 is negative*.
If negative values should just return A as well, then there's another more simple way to do it - as it is pointless to check what the condition before already checked.
=IF(A1<=80;"A";IF(A1<=95;"B";"C"))
The additional check whether A1 is larger than 80 in the second check is superfluous, as it is already in the else-part of the first if, that did check exactly that.
The reason why it doesn't work as expected without the linking is that the first check will already be evaluated. so 0<=A1 is either true or false, in numeric values this is 0 and 1 - and 1<=80 is always true, same problem in the second if (but that is never triggered because of the first condition is always true).
But also note my comment whether using an IF-construct really is what is needed for the desired final result.Fri, 02 Nov 2012 15:40:07 +0100https://ask.libreoffice.org/en/question/7411/calc-if-then-otherwise-otherwise/?answer=7418#post-id-7418Answer by shadow for <p>Hi, can anybody tell me, how to interlace an if-clause ?
I.e.: =IF(0<=A1<=80;"A";IF(80<=A1<=95;"B";"C")) doesn't work.
Is it necessary to use an other function therefor ? Or is there no possibility to do that ?</p>
<p>Thanks !</p>
https://ask.libreoffice.org/en/question/7411/calc-if-then-otherwise-otherwise/?answer=7417#post-id-7417It works ! Thanks !!!Fri, 02 Nov 2012 15:30:40 +0100https://ask.libreoffice.org/en/question/7411/calc-if-then-otherwise-otherwise/?answer=7417#post-id-7417Comment by qubit for <p>It works ! Thanks !!!</p>
https://ask.libreoffice.org/en/question/7411/calc-if-then-otherwise-otherwise/?comment=11256#post-id-11256Hey @cloph, I'm trying to figure out whose answer worked for @shadow here so I can mark one as 'correct'. Your answer looks like it came in 10min after this post, but does appear to be the most rigorous on the page...Sun, 03 Feb 2013 00:33:10 +0100https://ask.libreoffice.org/en/question/7411/calc-if-then-otherwise-otherwise/?comment=11256#post-id-11256Comment by cloph for <p>It works ! Thanks !!!</p>
https://ask.libreoffice.org/en/question/7411/calc-if-then-otherwise-otherwise/?comment=7448#post-id-7448please use votes to show your gratitude to helpful answers. If you use your votes and also flag a question you like as correct, you'll got your 3 karma points that are needed to write comments in no time :-))Sat, 03 Nov 2012 11:19:35 +0100https://ask.libreoffice.org/en/question/7411/calc-if-then-otherwise-otherwise/?comment=7448#post-id-7448