We will be migrating from Ask to Discourse on the first week of August, read the details here

# IF function with multiple nested function?

Hello!

I am trying to put together a IF function that uses 2 conditions, which results in 5 combinations. I hope somebody can help me.

This is how its supposed to work:

if C4=yes and C2>3 return "buy"

if C4=yes and C2<2 return "sell"

if C4=no and C2>2 return "buy"

if C4=no and C2<1 return "sell"

otherwise "keep"

I tried multiple ways to nest the conditions in one function but have failed miserably so far. Does anyone know how to do it?

Sophia

edit retag close merge delete

Sort by » oldest newest most voted

One of many possible solutions

=IF(OR(C2<1;C2>3;AND(C4<>"no";C4<>"yes"));"Input correct data";CHOOSE((C4="yes")+MIN(C2;3);"keep";"buy";"buy";"sell";"keep";"buy"))


The best solution is to create a service table in which you list (and, if necessary, edit) all possible recommendations.

In this case, the formula will be much simpler - it will not contain the values ​​of the decisions, but only the addresses of the cells involved in the decision.

=VLOOKUP($C$2;Recommendations;($C$4="yes")+2;1)


An example of such a solution in the attached file - C:\fakepath\Multiple choice.ods

more

One of many possible solutions

Hmm, the formula discards cases where C2 < 1 and C2 > 3, while the description states them as valid and producing "buy"/"sell"...

( 2020-05-20 10:37:59 +0200 )edit
1

Right! This once again demonstrates how difficult it is to write a long formula without errors. Long live the short formulas! :-)

( 2020-05-20 10:42:11 +0200 )edit

Hey John! Thanks so much for the effort.

( 2020-05-22 04:18:37 +0200 )edit

Possibly like this:

=IFS(OR(C2>3;AND(C2>2;C4="no"));"buy";OR(C2<1;AND(C2<2;C4="yes"));"sell";1;"keep")


... but your screenshot is inconsistent with the description: the latter states "keep" for "C2=2", while the screenshot shows "buy" for that case.

more

THANK YOU Mike!

( 2020-05-22 04:17:27 +0200 )edit

It works great. :)

( 2020-05-22 04:17:41 +0200 )edit