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 !
A 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.
If 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.
also 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…
It works ! Thanks !!!
please 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 :-))
Hey @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…
Another 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)
“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.
That 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.
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.
it is always helpful to draw a small flowchart to understand the decision tree when using formulas containing “if”.
If 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.
@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.