IF Statements
 

I have the following IF Statements. Can some one please help me add them all
together or enlighten me to a simpler way.

I basically want to say...
If A=High and B=+Major put YY,
if B=+moderate put YY,
if B=+minor put Y,
if B=negligible put O,
if B=-minor put N,
if B=-moderate put NN and
if B=-major put NN.....
BUT IF A=Medium and B=+major put YY,
if B=+moderate put Y,
if B=+minor put O,
if B=negligible put O,
if B=-minor put O,
if B=-moderate put N and
if B=-major put NN.....
BUT IF A=Low and B=+major put Y,
if B=+moderate put O,
if B=+minor put O,
if B=negligible put O,
if B=-minor put O,
if B=-moderate put O and
if B=-major put N

=IF($B$3="High",IF(B$9="+Major","YY",IF(B$9="+Mode rate","YY",IF(B$9="+Minor","Y",IF(B$9="Negligible" ,"0",IF(B$9="-
Minor","N",IF(B$9="- Moderate","NN",IF(B$9="- Major","NN","0"))))))))

=IF($B$3="Medium",IF(B$9="+Major","YY",IF(B$9="+Mo derate","Y",IF(B$9="+Minor","O",IF(B$9="Negligible ","0",IF(B$9="-
Minor","O",IF(B$9="- Moderate","N",IF(B$9="- Major","NN","0"))))))))

=IF($B$3="Low",IF(B$9="+Major","Y",IF(B$9="+Modera te","O",IF(B$9="+Minor","O",IF(B$9="Negligible","0 ",IF(B$9="-
Minor","O",IF(B$9="- Moderate","O",IF(B$9="- Major","N","0"))))))))

Appreciate the help in advance
Rebecca

IF Statements
 

Rebecca

Realistically you have a couple of choices

1) Write a UDF (User defined function). This is not tricky, but you will
need to know VBA
2) Set up a table on another sheet to lookup on.

Taking the second route and presuming the table takes up the range A1:B21 on
a sheet called sheet2, enter into the range A1:A21 in the new table, both
criteria rolled into one, e.g

High+Major
High+moderate
.........

Alongside this in B1:B21 enter the result, e.g

YY
YY
Y
.....

Now for your formula alongside the current data (Say in C1)

=VLOOKUP(A1&B1,Sheet2!$A$1:$B$21,2,FALSE)

This concatenates the two parts of the criteria, looks them up in the new
table on Sheet2 and then returns the result it finds in the second column of
the new table alongside the match

Hope that makes sense

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Rebecca" wrote in message
...
I have the following IF Statements. Can some one please help me add them
all
together or enlighten me to a simpler way.

I basically want to say...
If A=High and B=+Major put YY,
if B=+moderate put YY,
if B=+minor put Y,
if B=negligible put O,
if B=-minor put N,
if B=-moderate put NN and
if B=-major put NN.....
BUT IF A=Medium and B=+major put YY,
if B=+moderate put Y,
if B=+minor put O,
if B=negligible put O,
if B=-minor put O,
if B=-moderate put N and
if B=-major put NN.....
BUT IF A=Low and B=+major put Y,
if B=+moderate put O,
if B=+minor put O,
if B=negligible put O,
if B=-minor put O,
if B=-moderate put O and
if B=-major put N

=IF($B$3="High",IF(B$9="+Major","YY",IF(B$9="+Mode rate","YY",IF(B$9="+Minor","Y",IF(B$9="Negligible" ,"0",IF(B$9="-
Minor","N",IF(B$9="- Moderate","NN",IF(B$9="- Major","NN","0"))))))))

=IF($B$3="Medium",IF(B$9="+Major","YY",IF(B$9="+Mo derate","Y",IF(B$9="+Minor","O",IF(B$9="Negligible ","0",IF(B$9="-
Minor","O",IF(B$9="- Moderate","N",IF(B$9="- Major","NN","0"))))))))

=IF($B$3="Low",IF(B$9="+Major","Y",IF(B$9="+Modera te","O",IF(B$9="+Minor","O",IF(B$9="Negligible","0 ",IF(B$9="-
Minor","O",IF(B$9="- Moderate","O",IF(B$9="- Major","N","0"))))))))

Appreciate the help in advance
Rebecca