Ask Your Question
1

Calc - if then otherwise otherwise [closed]

asked 2012-11-02 13:54:59 +0200

shadow gravatar image

updated 2015-10-26 22:12:14 +0200

Alex Kemp gravatar image

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 flag offensive 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

7 Answers

Sort by » oldest newest most voted
3

answered 2012-11-02 15:40:07 +0200

cloph gravatar image

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.

edit flag offensive delete link more
0

answered 2012-11-03 04:53:19 +0200

ROSt52 gravatar image

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.

edit flag offensive delete link more

Comments

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

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

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 gravatar imagecloph ( 2012-11-03 11:16:54 +0200 )edit
0

answered 2012-11-02 15:30:40 +0200

shadow gravatar image

It works ! Thanks !!!

edit flag offensive delete link more

Comments

1

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 :-))

cloph gravatar imagecloph ( 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...

qubit gravatar imagequbit ( 2013-02-03 00:33:10 +0200 )edit
0

answered 2012-11-03 14:22:53 +0200

ROSt52 gravatar image

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

edit flag offensive delete link more
0

answered 2012-11-02 15:03:53 +0200

w_whalley gravatar image

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.

edit flag offensive delete link more

Comments

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

cloph gravatar imagecloph ( 2012-11-02 15:16:12 +0200 )edit
0

answered 2012-11-02 23:45:24 +0200

m.a.riosv gravatar image

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)

image description

edit flag offensive delete link more
-1

answered 2012-11-03 04:34:11 +0200

ROSt52 gravatar image

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

edit flag offensive delete link more

Comments

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.

cloph gravatar imagecloph ( 2012-11-03 11:13:47 +0200 )edit

Question Tools

Stats

Asked: 2012-11-02 13:54:59 +0200

Seen: 40,454 times

Last updated: Nov 03 '12