How to write a complicated nested if formula

[Dan-IA]

Hello all,
I'm trying to write a nested if formula for a decision rule. It combines two or three variables, and on the basis of the the combined values for each, a decision rule is generated. There are 7 conditions (which I believe nested if can handle). When I type in the formula, it works up the three conditions, then it stops. Here are the formulas I'm trying to combine:

1. =IF((F101>5)*(F40>7)*(E53>0),"Poor Prognosis: Discharge Recommended",

2. IF((F101>5)*(F40>7)*(E53=0),"Poor Prognosis: Noninvasive Tx recommended",

3. IF((F101<6)*(F40>7),”Fair Prognosis: Motivation/Compliance Measures Recommended”,

4. if((f101<6)*(f40<8)*(E53=1),”Fair Prognosis: Motivation/Compliance Measures Recommmended”,

5. if(F101<6)*(F40<8)*(F53=0),”Good Prognosis: Post OP Psych Consult Recommended",

6. IF(f101>5)*(f40<8), “Good Prognosis: Post OP Psych Consult Recommended”,

7. if(f40<4),”Good Prognosis: Proceed with Surgery”,

I've stringed them all together in a nested if statement, and It doesn't work. Also notice that some conditions require values for all three variables, some from two, and one condition from only one variable.

I'd appreciate any help you can give me,

Thanks!
Dan

 

[West Man]

The first 2 conditions appear to be identical.

 

[LufiaMaxim]

The first 2 conditions appear to be identical.

Case one has E53 > 0

Case two has E53 = 0

That's the differnce between the first two.

As for the original question, that's out of my range of knowledge. I could take a stab at it, but I'm afraid I wouldn't be of much help.

 

[West Man]

Also condition 5 tests F53 while others test E53. Is this intended or a typo?

 

[Dan-IA]

Yes, sorry about that
In condition five, F53 is a typo, it should be E53.
Thanks for pointing it out.
Dan

 

[markmzz]

Try this:

Note: I used a helper range (E1:E9).

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: center">F40</TD><TD style="TEXT-ALIGN: center">F101</TD><TD style="TEXT-ALIGN: center">E53</TD><TD style="TEXT-ALIGN: right"></TD><TD style="FONT-WEIGHT: bold">Prognosis</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Code</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: center"><4</TD><TD style="TEXT-ALIGN: center">-</TD><TD style="TEXT-ALIGN: center">-</TD><TD style="TEXT-ALIGN: right"></TD><TD>Good Prognosis: Proceed with Surgery</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: center"><8</TD><TD style="TEXT-ALIGN: center">>5</TD><TD style="TEXT-ALIGN: center">-</TD><TD style="TEXT-ALIGN: right"></TD><TD>Good Prognosis: Post OP Psych Consult Recommended</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: center"><8</TD><TD style="TEXT-ALIGN: center"><6</TD><TD style="TEXT-ALIGN: center">=0</TD><TD style="TEXT-ALIGN: right"></TD><TD>Good Prognosis: Post OP Psych Consult Recommended</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: center"><8</TD><TD style="TEXT-ALIGN: center"><6</TD><TD style="TEXT-ALIGN: center">=1</TD><TD style="TEXT-ALIGN: right"></TD><TD>Fair Prognosis: Motivation/Compliance Measures Recommmended</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: center">>7</TD><TD style="TEXT-ALIGN: center"><6</TD><TD style="TEXT-ALIGN: center">-</TD><TD style="TEXT-ALIGN: right"></TD><TD>Fair Prognosis: Motivation/Compliance Measures Recommended</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: center">>7</TD><TD style="TEXT-ALIGN: center">>5</TD><TD style="TEXT-ALIGN: center">=0</TD><TD style="TEXT-ALIGN: right"></TD><TD>Poor Prognosis: Noninvasive Tx recommended</TD><TD style="TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: center">>7</TD><TD style="TEXT-ALIGN: center">>5</TD><TD style="TEXT-ALIGN: center">>0</TD><TD style="TEXT-ALIGN: right"></TD><TD>Poor Prognosis: Discharge Recommended</TD><TD style="TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>Invalid</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">Result</TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">Good Prognosis: Proceed with Surgery</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">39</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">F40</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">40</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">0</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">41</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">52</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">E53</TD><TD style="TEXT-ALIGN: center">F53</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">53</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">1</TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">54</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">100</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">F101</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">101</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">8</TD></TR></TBODY></TABLE>Plan2


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>E10</TH><TD style="TEXT-ALIGN: left">=INDEX($E$2:$E$9,IF(G40<4,1,IF(G40<8,IF(G101>5,2,IF(E53=0,3,IF(E53=1,4,8))),IF(G101<6,5,IF(E53=0,6,IF(E53>0,7,8))))))</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>G40</TH><TD style="TEXT-ALIGN: left">=RANDBETWEEN(0,10)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>E53</TH><TD style="TEXT-ALIGN: left">=RANDBETWEEN(0,2)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>F53</TH><TD style="TEXT-ALIGN: left">=RANDBETWEEN(0,2)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>G101</TH><TD style="TEXT-ALIGN: left">=RANDBETWEEN(0,10)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Markmzz