# 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

@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

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

"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

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.

( 2012-11-03 11:13:47 +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

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

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