# Calc - if then otherwise otherwise [closed]

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 !

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2015-10-18 22:29:57.525196

Sort by » oldest newest most voted

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.

more

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.

more

it is always helpful to draw a small flowchart to understand the decision tree when using formulas containing "if".

( 2012-11-03 04:54:32 +0200 )edit

If that answer was written by a different user, I'd downvote that as well.

( 2012-11-03 11:16:54 +0200 )edit

It works ! Thanks !!!

more

1

( 2012-11-03 11:19:35 +0200 )edit

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

( 2013-02-03 00:33:10 +0200 )edit

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

more

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.

more

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

( 2012-11-02 15:16:12 +0200 )edit

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)

more

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

more