Limited to 7 IF Statements

  • Thread starter Thread starter Omer
  • Start date Start date
O

Omer

Hi ,

My data is in 4 column's. (B,D,E,F).

Problem: Excell will not let you embed more than 7 IF
statements in one IF statement. How can I include options
like "IF(AND((B10-B9)>0,(D9)<0,(E9)>0,(F9)>0", "IF(AND
((B10-B9)>0,(D9)>0,(E9)>0,(F9)<0" and so forth.

Can I write 2 IF statements and somehow join them (Each
having 7 IF statements withing each other)

Thanks
omer

My current IF STATEMENT IS =IF(AND((B10-B9)<0,(D9)<0,(E9)
<0,(F9)<0),"ALL MKT -VE / LOST MONEY",IF(AND((B10-B9)<0,
(D9)<0,(E9)<0,(F9)>0),"J / lost money",IF(AND((B10-B9)<0,
(D9)<0,(E9)>0,(F9)<0),"B / lost money",IF(AND((B10-B9)<0,
(D9)>0,(E9)<0,(F9)<0),"G / Lost Money",IF(AND((B10-B9)>0,
(D9)>0,(E9)<0,(F9)<0),"G / Made Money",IF(AND((B10-B9)>0,
(D9)<0,(E9)>0,(F9)<0),"B / Made Money",IF(AND((B10-B9)>0,
(D9)<0,(E9)<0,(F9)>0),"J / Made Money")))))))


Data:

B C D E
58.63 -36.04 -13.6 16.84
58.27 49.73 10.9 -59.87
58.66 -46.89 -22.3 5.26
59.21 72.47 46.5 100.47
59.73 -29.83 -21.7 32.7
 
If each condition is unique you can have as many IF statements as you need by NOT nesting them

For Numeric returns
=IF(condition1,true1,0)+IF(condition2,true2,0)+IF(condition3,true3,0)+....

For Alpha returns
=IF(condition1,true1,"")&IF(condition2,true2,"")&IF(condition3,true3,"")&....

You need to formulate your IF statements, using ORs and ANDs, to be sure no input could make more then one IF statement true, unless you want to get more then one return

=IF(AND((B10-B9)<0,(D9)<0,(E9)<0,(F9)<0),"ALL MKT -VE / LOST MONEY","")
IF(AND((B10-B9)<0,(D9)<0,(E9)<0,(F9)>0),"J / lost money","")
IF(AND((B10-B9)<0,(D9)<0,(E9)>0,(F9)<0),"B / lost money","")
IF(AND((B10-B9)<0,(D9)>0,(E9)<0,(F9)<0),"G / Lost Money","")
IF(AND((B10-B9)>0,(D9)>0,(E9)<0,(F9)<0),"G / Made Money","")
IF(AND((B10-B9)>0,(D9)<0,(E9)>0,(F9)<0),"B / Made Money","")
IF(AND((B10-B9)>0,(D9)<0,(E9)<0,(F9)>0),"J / Made Money"

Good Luck
Mark Graesse
(e-mail address removed)


----- Omer wrote: ----

Hi

My data is in 4 column's. (B,D,E,F)

Problem: Excell will not let you embed more than 7 IF
statements in one IF statement. How can I include options
like "IF(AND((B10-B9)>0,(D9)<0,(E9)>0,(F9)>0", "IF(AN
((B10-B9)>0,(D9)>0,(E9)>0,(F9)<0" and so forth

Can I write 2 IF statements and somehow join them (Each
having 7 IF statements withing each other

Thank
ome

My current IF STATEMENT IS =IF(AND((B10-B9)<0,(D9)<0,(E9
<0,(F9)<0),"ALL MKT -VE / LOST MONEY",IF(AND((B10-B9)<0
(D9)<0,(E9)<0,(F9)>0),"J / lost money",IF(AND((B10-B9)<0
(D9)<0,(E9)>0,(F9)<0),"B / lost money",IF(AND((B10-B9)<0
(D9)>0,(E9)<0,(F9)<0),"G / Lost Money",IF(AND((B10-B9)>0
(D9)>0,(E9)<0,(F9)<0),"G / Made Money",IF(AND((B10-B9)>0
(D9)<0,(E9)>0,(F9)<0),"B / Made Money",IF(AND((B10-B9)>0
(D9)<0,(E9)<0,(F9)>0),"J / Made Money"))))))


Data

B C D
58.63 -36.04 -13.6 16.8
58.27 49.73 10.9 -59.8
58.66 -46.89 -22.3 5.2
59.21 72.47 46.5 100.4
59.73 -29.83 -21.7 32.
 
Omer,

One way to do this would be to create a results table like

FFFF ALL MKT -VE / LOST MONEY
FFFT J / LOST MONEY
FFTF B / LOST MONEY
FTFF G / LOST MONEY
TFFT J / MADE MONEY
TFTF B / MADE MONEY
TTFF G / MADE MONEY

Make this table somewhere in your workbook. I chose to make
it on a second sheet.

The F's and T's represent the results of IF statements.
The results "FFFF" come from this statement
=IF(B10-B9>0, "T", "F") & IF(D10>0, "T", "F") & IF(E10>0, "T", "F") & IF(F10>0,"T", "F")

Then on your original sheet you can use the above formula and
a vlookup to get your result

=VLOOKUP(IF(B10-B9>0, "T", "F")&IF(D10>0, "T", "F")&IF(E10>0, "T", "F")&IF(F10>0,"T", "F"),Sheet2!A$2:B$8,2,FALSE)

Dan E
 
Back
Top