Nested IF, AND function with Dynamic cell input

[OttoMan]

Hi,

In the screen shot given below, there are more than 7 nested IF functions and my nested IF formula doe not work due to that.
I tried VLOOKUP but did not work because the lookup value (% sales achieved compared to target) can be nothing ("") to 0% or can go upto 150%. (maybe there is a way I don't know how to),....
Hope someone can help me on this,.. I am using Excel 2003 and I do not want to use any VBA,...Any help is greatly appreciated ,...!!! (I am trying to solve this for a couple of days now) !!!

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD><TD>P</TD><TD>Q</TD><TD>R</TD><TD>S</TD><TD>T</TD><TD>U</TD><TD>V</TD><TD>W</TD><TD>X</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center" colSpan=13>Commission % Table</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD> </TD><TD> </TD><TD> </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center" colSpan=5>% Sales Achieved compared to Targets</TD><TD> </TD><TD style="TEXT-ALIGN: center">Sales targets</TD><TD style="TEXT-ALIGN: center">95.0%</TD><TD style="TEXT-ALIGN: center">97.5%</TD><TD style="TEXT-ALIGN: center">100.0%</TD><TD style="TEXT-ALIGN: center">102.5%</TD><TD style="TEXT-ALIGN: center">105.0%</TD><TD style="TEXT-ALIGN: center">107.5%</TD><TD style="TEXT-ALIGN: center">110.0%</TD><TD style="TEXT-ALIGN: center">112.5%</TD><TD style="TEXT-ALIGN: center">115.0%</TD><TD style="TEXT-ALIGN: center">117.5%</TD><TD style="TEXT-ALIGN: center">120.0%</TD><TD style="TEXT-ALIGN: center">122.5%</TD><TD style="TEXT-ALIGN: center">125.0%</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Names</TD><TD style="FONT-WEIGHT: bold">Area</TD><TD style="FONT-WEIGHT: bold">Product Group</TD><TD>Product A</TD><TD>Product B</TD><TD>Product C</TD><TD>Product D</TD><TD>Product E</TD><TD> </TD><TD>Commission----></TD><TD style="TEXT-ALIGN: right">1.0%</TD><TD style="TEXT-ALIGN: right">3.5%</TD><TD style="TEXT-ALIGN: right">6.0%</TD><TD style="TEXT-ALIGN: right">7.5%</TD><TD style="TEXT-ALIGN: right">9.0%</TD><TD style="TEXT-ALIGN: right">10.5%</TD><TD style="TEXT-ALIGN: right">12.0%</TD><TD style="TEXT-ALIGN: right">13.5%</TD><TD style="TEXT-ALIGN: right">15.0%</TD><TD style="TEXT-ALIGN: right">16.5%</TD><TD style="TEXT-ALIGN: right">18.0%</TD><TD style="TEXT-ALIGN: right">19.5%</TD><TD style="TEXT-ALIGN: right">21.0%</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>Mrs.A</TD><TD>A</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">113.5%</TD><TD style="TEXT-ALIGN: right">85%</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>Mrs.A</TD><TD>A</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">92%</TD><TD style="TEXT-ALIGN: right">100%</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>Mrs.A</TD><TD>A</TD><TD style="TEXT-ALIGN: right">3</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">125%</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD> </TD><TD> </TD><TD> </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center" colSpan=5>Applicable Commission %</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Names</TD><TD style="FONT-WEIGHT: bold">Area</TD><TD style="FONT-WEIGHT: bold">Product Group</TD><TD>Product A</TD><TD>Product B</TD><TD>Product C</TD><TD>Product D</TD><TD>Product E</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD>Mrs.A</TD><TD>A</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">15.0%</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">0%</TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD>Mrs.A</TD><TD>A</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">0%</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">6%</TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">21%</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD>Mrs.A</TD><TD>A</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="FONT-WEIGHT: bold; TEXT-DECORATION: underline">Notes</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: left" colSpan=7>% sales achieved compared to target will change for each person (dynamic)</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: left" colSpan=9>Depending on the % to target achieved, that % must be found in the commission % table</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: left" colSpan=10>If the Sales target achived % cell is blank means that the person is not selling the product,..therefore no vales ( "" )</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: left" colSpan=14>if someone achieves <95% then 0% commission, if sales were >=95% but <97.5 then 1%, if sales were >=97.5% but <100% then 3.5% commission so on,…</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: left" colSpan=6>The answer I am looking for is in the "Applicable Commission %" Table</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>

 

[gardnertoo]

Here's your E11 formula, copy it down and to the right to fill the rest of the yellow cells:

=IF(E4=0,"",IF(ISERROR(HLOOKUP(E4,$L$2:$X$3,2,TRUE)),0,HLOOKUP(E4,$L$2:$X$3,2,TRUE)))

 

[OttoMan]

Hi gardnertoo, Thank you for your reply. I was actually trying to get an answer to (say) cell G12 (cells coloured in Yellow). My current formula in that cell is given below, (which is very long (more than 7 nested IF arugments)and due to this I can't make it to work !!)

Cell G12 answer should be 13.5% not 15% as indicated in the table above.

IF(G5="","",IF(G5<$L$3,0,IF(G5<$M$3,$L$4,IF(G5<m3,$l$4,if(g5><$N$3,$M$4,if(G5<$O$3,$N$4,
if(G5<$P$3,$O$4,if($G$5<$Q$3,$P$4,if(G5<$R$3,$Q$4,if(G5<$S$3,$R$4,
if(G5<$T$3,$S$4,IF(G5<$U$3,$T$4,IF(G5<$V$3,$U$4,IF(G5<$W$3,$V$4,
IF(G5<$X$3,W4,$X$4))))))))))))))</m3,$l$4,if(g5>

 

[DonkeyOte]

OttoMan, I believe garderntoo has given you a solution though you must adjust the references by 1 row to reflect your setup, ie:

E12:
=IF(E5=0,"",IF(ISERROR(HLOOKUP(E5,$L$3:$X$4,2,TRUE)),0,HLOOKUP(E5,$L$4:$X$4,2,TRUE)))
copy the above across your results matrix
note: i believe your sample results are incorrect in so far as per your logic G12 should be 13.5% rather than 15%

 

[OttoMan]

Hi gardnertoo and DonkeyOte,...
Thank you sooo much,..it works !!! I always thought that I have to do a nested IF formula,... and I couldn't find any posting which actually did what I wanted with IF, Concatenate, named formulas,..etc,..
Thanks again,...