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

Ask Your Question
0

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

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.

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?

Sophia

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
1

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.

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

MultiRecommendations.png

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

edit flag offensive delete link more

Comments

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
1

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
0

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

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.

edit flag offensive delete link more

Comments

THANK YOU Mike!

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

Stats

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

Seen: 240 times

Last updated: Sep 26 '20