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

Ask Your Question

IF function with multiple nested function?

asked 2020-05-20 09:05:11 +0200

cybersurfer5000 gravatar image

updated 2020-09-26 11:53:57 +0200

Alex Kemp gravatar image


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?


edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2020-05-20 09:48:20 +0200

JohnSUN gravatar image

updated 2020-05-20 10:23:09 +0200

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 - C:\fakepath\Multiple choice.ods

edit flag offensive delete link 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"...

Mike Kaganski gravatar imageMike Kaganski ( 2020-05-20 10:37:59 +0200 )edit

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

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

Hey John! Thanks so much for the effort.

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

answered 2020-05-20 10:35:27 +0200

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.

edit flag offensive delete link more



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

It works great. :)

cybersurfer5000 gravatar imagecybersurfer5000 ( 2020-05-22 04:17:41 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-05-20 09:05:11 +0200

Seen: 240 times

Last updated: Sep 26 '20