I'm new to excel.

I have columns C and D, which have positive numerical values from 1-100.

I also have 2 columns X and Y. the values of both cells could be 'yes' or 'no'. So, the different possible outcomes are

1. X=no, Y=no
2. X=yes, Y=yes
3. X=no, Y=yes
4. X=yes, Y=no

I want to create a query and return it to column E such that

If case 1, then return 1

If case 2, then return C / 100

If case 3, then return D / 100 * (-1)

If case 4, then return -1

I know that I can use IF to return two outputs but don't know how to return 4 different outputs.

Thanks in advance!

CodePudding user response：

Nested IF Functions

In cell E1, you could use

=IF(X1="no",IF(Y1="no",1,-D1/100),IF(Y1="no",-1,C1/100))

To 'account' for errors, you could improve with

=IFERROR(IF(X1="no",IF(Y1="no",1,-D1/100),IF(Y1="no",-1,C1/100)),"")

If you're expecting other values than yes or no, you would need to use

=IF(X1="no",IF(Y1="no",1,IF(Y1="yes",-D1/100,"")),IF(X1="yes",IF(Y1="no",-1,IF(Y1="yes",C1/100,"")),""))