IF function with multiple nested function?


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

image description

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?


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.



An example of such a solution in the attached file - Multiple choice.ods

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”…

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

Hey John! Thanks so much for the effort.

Possibly like this:


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


It works great. :slight_smile: