more levels of nesting than allowed in a formula

Pursuit2010 · Oct 20, 2010

<TABLE style="WIDTH: 581pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=773><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2779" width=76><COL style="WIDTH: 40pt; mso-width-source: userset; mso-width-alt: 1938" width=53><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><COL style="WIDTH: 100pt; mso-width-source: userset; mso-width-alt: 4864" width=133><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 581pt; HEIGHT: 153pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" id=td_post_2481960 class=xl69 height=204 rowSpan=12 width=773 colSpan=10>IF(OR(AND($K8=0,($L8>=13)*($L8<=19),($N8>=25.0)*($N8<=30.9),AND($K8=1,($L8>=13)*($L8<=19),($N8>=35.0)*($N8<38.3),AND($K8=0,($L8>=20)*($L8<=29),($N8>=23.6)*($N8<=28.9),AND($K8=1,($L8>=20)*($L8<=29),($N8>=33)*($N8<=36.4),AND($K8=0,($L8>=30)*($L8<=39),($N8>=22.8)*($N8<=26.9),AND($K8=1,($L8>=30)*($L8<=39),($N8>=31.5)*($N8<=35.4),AND($K8=0,($L8>=40)*($L8<=49),($N8>=21.0)*($N8<=24.4),AND($K8=1,($L8>=13)*($L8<=49),($N8>=30.2)*($N8<=33.5),AND($K8=0,($L8>=50)*($L8<=59),($N8>=20.2)*($N8<=22.7),AND($K8=1,($L8>=13)*($L8<=59),($N8>=26.1)*($N8<=30.9),AND($K8=0,($L8>=60),($N8>=17.5)*($N8<=20.1),AND($K8=1,($L8>=60),($N8>=20.5)*($N8<=26.0)),"1","0")

</TD></TR>

Below is the formula I need to use but it tells me there are more levels of nesting than allowed.
What do I do to make it work?

Peter_SSs · Jan 19, 2012

Is there any chance that you could post that sample data again, but ..

a) Most of the potential helpers here don't much like typing sample data so could you use one of the methods suggested in my signature block to post it directly in your post so that it can be copied/pasted in our worksheets to test?

b) Include (manually entered) the expected results so we can be sure we are aiming for the correct solution.

Also, the image you have posted seems to bear no relationship to the data or cells referred to in your attempted formula in post #48. Do we need to know about "AMEG", "CEI" etc?

pacedinc · Mar 20, 2012

Hi,

I'm trying to obtain a value based on two criteria (Age Band and Plan#). There are 11 Age Bands(K21) and 6 Plan #s(AK21). I've tried this but it returns the more nesting than allowed error:

 =IF(K21<25,+AK21*40200,
IF(AND(K21>24,K21<30),+AK21*36000,
IF(AND(K21>29,K21<35,+AK21*32100,
IF(AND(K21>34,K21<40,+AK21*27900,
IF(AND(K21>39,K21<45,+AK21*22200,
IF(AND(K21>44,K21<50,+AK21*14400,
IF(AND(K21>49,K21<55,+AK21*12960,
IF(AND(K21>54,K21<60,+AK21*11880,
IF(AND(K21>59,K21<65,+AK21*9720,
IF(AND(K21>64,K21<70,+AK21*6320,
IF(K21>69,+AK21*3160)))))))

The table looks like this and is multiplied by a value in AK21 or I could put the entire chart which would have 5 more columns and lookup the proper value?

[FONT=&quot]Under 25[/FONT]

</td> <td style="width:126.2pt;border-top:double windowtext 4.5pt; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:double windowtext 4.5pt;mso-border-bottom-alt:solid windowtext .5pt; mso-border-right-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt; height:.2in" nowrap="nowrap" width="168">

[FONT=&quot]$40,200.00[/FONT]

</td> </tr> <tr style="mso-yfti-irow:1;height:13.8pt"> <td style="width:168.85pt;border-top:none;border-left:solid windowtext 1.5pt; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-left-alt:solid windowtext 1.5pt;mso-border-bottom-alt:solid windowtext .5pt; mso-border-right-alt:solid windowtext .5pt;background:silver;padding:0in 5.4pt 0in 5.4pt; height:13.8pt" nowrap="nowrap" width="225">

[FONT=&quot]25-29[/FONT]

</td> <td style="width:126.2pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; background:silver;padding:0in 5.4pt 0in 5.4pt;height:13.8pt" nowrap="nowrap" width="168">

[FONT=&quot]$36,000.00[/FONT]

</td> </tr> <tr style="mso-yfti-irow:2;height:13.8pt"> <td style="width:168.85pt;border-top:none;border-left:solid windowtext 1.5pt; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-left-alt:solid windowtext 1.5pt;mso-border-bottom-alt:solid windowtext .5pt; mso-border-right-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt; height:13.8pt" nowrap="nowrap" width="225">

[FONT=&quot]30-34[/FONT]

</td> <td style="width:126.2pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; padding:0in 5.4pt 0in 5.4pt;height:13.8pt" nowrap="nowrap" width="168">

[FONT=&quot]$32,100.00[/FONT]

</td> </tr> <tr style="mso-yfti-irow:3;height:13.8pt"> <td style="width:168.85pt;border-top:none;border-left:solid windowtext 1.5pt; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-left-alt:solid windowtext 1.5pt;mso-border-bottom-alt:solid windowtext .5pt; mso-border-right-alt:solid windowtext .5pt;background:silver;padding:0in 5.4pt 0in 5.4pt; height:13.8pt" nowrap="nowrap" width="225">

[FONT=&quot]35-39[/FONT]

</td> <td style="width:126.2pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; background:silver;padding:0in 5.4pt 0in 5.4pt;height:13.8pt" nowrap="nowrap" width="168">

[FONT=&quot]$27,900.00[/FONT]

</td> </tr> <tr style="mso-yfti-irow:4;height:13.8pt"> <td style="width:168.85pt;border-top:none;border-left:solid windowtext 1.5pt; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-left-alt:solid windowtext 1.5pt;mso-border-bottom-alt:solid windowtext .5pt; mso-border-right-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt; height:13.8pt" nowrap="nowrap" width="225">

[FONT=&quot]40-44[/FONT]

</td> <td style="width:126.2pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; padding:0in 5.4pt 0in 5.4pt;height:13.8pt" nowrap="nowrap" width="168">

[FONT=&quot]$22,200.00[/FONT]

</td> </tr> <tr style="mso-yfti-irow:5;height:13.8pt"> <td style="width:168.85pt;border-top:none;border-left:solid windowtext 1.5pt; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-left-alt:solid windowtext 1.5pt;mso-border-bottom-alt:solid windowtext .5pt; mso-border-right-alt:solid windowtext .5pt;background:silver;padding:0in 5.4pt 0in 5.4pt; height:13.8pt" nowrap="nowrap" width="225">

[FONT=&quot]45-49[/FONT]

</td> <td style="width:126.2pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; background:silver;padding:0in 5.4pt 0in 5.4pt;height:13.8pt" nowrap="nowrap" width="168">

[FONT=&quot]$14,400.00[/FONT]

</td> </tr> <tr style="mso-yfti-irow:6;height:13.8pt"> <td style="width:168.85pt;border-top:none;border-left:solid windowtext 1.5pt; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-left-alt:solid windowtext 1.5pt;mso-border-bottom-alt:solid windowtext .5pt; mso-border-right-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt; height:13.8pt" nowrap="nowrap" width="225">

[FONT=&quot]50-54[/FONT]

</td> <td style="width:126.2pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; padding:0in 5.4pt 0in 5.4pt;height:13.8pt" nowrap="nowrap" width="168">

[FONT=&quot]$12,960.00[/FONT]

</td> </tr> <tr style="mso-yfti-irow:7;height:13.8pt"> <td style="width:168.85pt;border-top:none;border-left:solid windowtext 1.5pt; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-left-alt:solid windowtext 1.5pt;mso-border-bottom-alt:solid windowtext .5pt; mso-border-right-alt:solid windowtext .5pt;background:silver;padding:0in 5.4pt 0in 5.4pt; height:13.8pt" nowrap="nowrap" width="225">

[FONT=&quot]55-59[/FONT]

</td> <td style="width:126.2pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; background:silver;padding:0in 5.4pt 0in 5.4pt;height:13.8pt" nowrap="nowrap" width="168">

[FONT=&quot]$11,880.00[/FONT]

</td> </tr> <tr style="mso-yfti-irow:8;height:13.8pt"> <td style="width:168.85pt;border-top:none;border-left:solid windowtext 1.5pt; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-left-alt:solid windowtext 1.5pt;mso-border-bottom-alt:solid windowtext .5pt; mso-border-right-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt; height:13.8pt" nowrap="nowrap" width="225">

[FONT=&quot]60-64[/FONT]

</td> <td style="width:126.2pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; padding:0in 5.4pt 0in 5.4pt;height:13.8pt" nowrap="nowrap" width="168">

[FONT=&quot]$9,720.00[/FONT]

</td> </tr> <tr style="mso-yfti-irow:9;height:13.8pt"> <td style="width:168.85pt;border-top:none;border-left:solid windowtext 1.5pt; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-left-alt:solid windowtext 1.5pt;mso-border-bottom-alt:solid windowtext .5pt; mso-border-right-alt:solid windowtext .5pt;background:silver;padding:0in 5.4pt 0in 5.4pt; height:13.8pt" nowrap="nowrap" width="225">

[FONT=&quot]65-69[/FONT]

</td> <td style="width:126.2pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; background:silver;padding:0in 5.4pt 0in 5.4pt;height:13.8pt" nowrap="nowrap" width="168">

[FONT=&quot]$6,320.00[/FONT]

</td> </tr> <tr style="mso-yfti-irow:10;mso-yfti-lastrow:yes;height:.2in"> <td style="width:168.85pt;border-top:none;border-left:solid windowtext 1.5pt; border-bottom:solid windowtext 1.5pt;border-right:solid windowtext 1.0pt; mso-border-left-alt:solid windowtext 1.5pt;mso-border-bottom-alt:solid windowtext 1.5pt; mso-border-right-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt; height:.2in" nowrap="nowrap" width="225">

[FONT=&quot]70 & over[/FONT]

</td> <td style="width:126.2pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.5pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext 1.5pt;mso-border-right-alt:solid windowtext .5pt; padding:0in 5.4pt 0in 5.4pt;height:.2in" nowrap="nowrap" width="168">

[FONT=&quot]$3,160.00[/FONT]

</td> </tr> </tbody></table>

Any Help is greatly appreciated. Thx

jasonb75 · Mar 20, 2012

Welcome to the board!

If you change your table slightly to

<TABLE style="WIDTH: 99pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=132><COLGROUP><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" width=61><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 46pt; HEIGHT: 15.75pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl63 height=21 width=61>0

</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #efefef; WIDTH: 53pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl68 width=71>40,200.00</TD></TR><TR style="HEIGHT: 15.75pt; mso-yfti-irow: 1" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: silver; HEIGHT: 15.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl64 height=21>25</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: silver; BORDER-TOP: #ece9d8; BORDER-RIGHT: #3867a6 1pt solid" class=xl69>36,000.00</TD></TR><TR style="HEIGHT: 15.75pt; mso-yfti-irow: 2" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; HEIGHT: 15.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl65 height=21>30</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #efefef; BORDER-TOP: #ece9d8; BORDER-RIGHT: #3867a6 1pt solid" class=xl70>32,100.00</TD></TR><TR style="HEIGHT: 15.75pt; mso-yfti-irow: 3" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: silver; HEIGHT: 15.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl64 height=21>35</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: silver; BORDER-TOP: #ece9d8; BORDER-RIGHT: #3867a6 1pt solid" class=xl69>27,900.00</TD></TR><TR style="HEIGHT: 15.75pt; mso-yfti-irow: 4" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; HEIGHT: 15.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl65 height=21>40</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #efefef; BORDER-TOP: #ece9d8; BORDER-RIGHT: #3867a6 1pt solid" class=xl70>22,200.00</TD></TR><TR style="HEIGHT: 15.75pt; mso-yfti-irow: 5" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: silver; HEIGHT: 15.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl64 height=21>45</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: silver; BORDER-TOP: #ece9d8; BORDER-RIGHT: #3867a6 1pt solid" class=xl69>14,400.00</TD></TR><TR style="HEIGHT: 15.75pt; mso-yfti-irow: 6" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; HEIGHT: 15.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl65 height=21>50</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #efefef; BORDER-TOP: #ece9d8; BORDER-RIGHT: #3867a6 1pt solid" class=xl70>12,960.00</TD></TR><TR style="HEIGHT: 15.75pt; mso-yfti-irow: 7" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: silver; HEIGHT: 15.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl64 height=21>55</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: silver; BORDER-TOP: #ece9d8; BORDER-RIGHT: #3867a6 1pt solid" class=xl69>11,880.00</TD></TR><TR style="HEIGHT: 15.75pt; mso-yfti-irow: 8" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; HEIGHT: 15.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl65 height=21>60</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #efefef; BORDER-TOP: #ece9d8; BORDER-RIGHT: #3867a6 1pt solid" class=xl70>9,720.00</TD></TR><TR style="HEIGHT: 15.75pt; mso-yfti-irow: 9" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: silver; HEIGHT: 15.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl64 height=21>65</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: silver; BORDER-TOP: #ece9d8; BORDER-RIGHT: #3867a6 1pt solid" class=xl69>6,320.00</TD></TR><TR style="HEIGHT: 15.75pt; mso-yfti-irow: 10; mso-yfti-lastrow: yes" height=21><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; HEIGHT: 15.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl66 height=21>70</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #efefef; BORDER-TOP: #ece9d8; BORDER-RIGHT: #3867a6 1pt solid" class=xl67>3,160.00

</TD></TR></TBODY></TABLE>

Then you could use VLOOKUP, assuming your tabe in A2:B12

=VLOOKUP(K21,$A$2:$B$12,2)*AK21

pacedinc · Mar 20, 2012

Awesome! Thx

skubo · May 6, 2012

Hi I am trying to find a way to shorten this formula because I have exceeded the nested amount. Any help would be GREATLY appreciated!!

One set is checking if the employee is Part-time and making sure that G3<160. and the second part is if G3 >= 160.

=IF(AND(O1="Part-time",D24>85,D24<110,G3<160),(500*(G3/160)),
IF(AND(O1="Part-time",D24<=85,G3<160),(400*(G3/160)),
IF(AND(O1="Part-time",D24>=110,G3<160),(700*(G3/160)),
IF(AND(O1="Part-time",D24>85,D24<110),(500),
IF(AND(O1="Part-time",D24<=85),(400),
IF(AND(O1="Part-time",D24>=110),(700),
IF(AND(D24>85,D24<110),(500),
IF(D24<=85,400,
IF(D24>=110,700,0))))))

Thank you!

jasonb75 · May 7, 2012

skubo said:
Hi I am trying to find a way to shorten this formula because I have exceeded the nested amount. Any help would be GREATLY appreciated!!

One set is checking if the employee is Part-time and making sure that G3<160. and the second part is if G3 >= 160.

=IF(AND(O1="Part-time",D24>85,D24<110,G3<160),(500*(G3/160)),
IF(AND(O1="Part-time",D24<=85,G3<160),(400*(G3/160)),
IF(AND(O1="Part-time",D24>=110,G3<160),(700*(G3/160)),
IF(AND(O1="Part-time",D24>85,D24<110),(500),
IF(AND(O1="Part-time",D24<=85),(400),
IF(AND(O1="Part-time",D24>=110),(700),
IF(AND(D24>85,D24<110),(500),
IF(D24<=85,400,
IF(D24>=110,700,0))))))

Thank you!

Welcome to the board!

Try

=IF(O3="Part-time",LOOKUP(D24,{0,85,110},{400,500,700}),0)*MIN(1,G3/160)

skubo · May 7, 2012

Hi Jason! Thank you very much for that! It definitely points me in the right direction.
I am now trying to figure out how to make it work with a range of numbers.
D24 is a number that changes every month. I want the formula to set the cell with this data.

if D24 is between 35-85 = 400
if D24 is between 86-110 = 500
if D24 is over 100 = 700

Thanks again!

jasonb75 said:
Welcome to the board!

Try

=IF(O3="Part-time",LOOKUP(D24,{0,85,110},{400,500,700}),0)*MIN(1,G3/160)

skubo · May 7, 2012

Sorry one of the cells were referenced wrong when I inserted it. It works out perfectly! You are the best! Thank you very much!!

skubo said:
Hi Jason! Thank you very much for that! It definitely points me in the right direction.
I am now trying to figure out how to make it work with a range of numbers.
D24 is a number that changes every month. I want the formula to set the cell with this data.

if D24 is between 35-85 = 400
if D24 is between 86-110 = 500
if D24 is over 100 = 700

Thanks again!

jasonb75 · May 8, 2012

skubo said:
Hi Jason! Thank you very much for that! It definitely points me in the right direction.
I am now trying to figure out how to make it work with a range of numbers.
D24 is a number that changes every month. I want the formula to set the cell with this data.

if D24 is between 35-85 = 400
if D24 is between 86-110 = 500
if D24 is over 100 = 700

Thanks again!

The formula does need a slight change, assuming anything less than 35 should return 0

=IF(O3="Part-time",LOOKUP(D24,{0,35,85.001,110.001},{0,400,500,700}),0)*MIN(1,G3/160)

basaue2 · May 31, 2012

I need to enter this formula and I have no idea how to make it smaller so it will fit.

=IF(D3="none",1,IF(D3="fine",2,IF(D3="fine, gross, hand eye",3,IF(DS="fine, critical thinking",4,IF(D3="fine, gross",5,IF(D3="gross, hand eye",6,IF(D3="fine, hand eye",7,IF(D3="fine, critical thinking, hand eye",8,IF(D3="fine, gross, critical thinking",9,IF(D3="gross",10,IF(D3="fine, gross, critical thinking, hand eye",11,IF(D3="gross, critical thinking",12,IF(D3="gross, critical thinking, hand eye",13,0)))))))))))))<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com

ffice

ffice" /><o

></o

>

more levels of nesting than allowed in a formula

Pursuit2010

New Member

Peter_SSs

MrExcel MVP, Moderator

Excel Facts

pacedinc

New Member

jasonb75

Well-known Member

pacedinc

New Member

skubo

New Member

jasonb75

Well-known Member

skubo

New Member

skubo

New Member

jasonb75

Well-known Member

basaue2

New Member

Similar threads

Forum statistics

Share this page

more levels of nesting than allowed in a formula

New Member

MrExcel MVP, Moderator

Excel Facts

New Member

Well-known Member

New Member

New Member

Well-known Member

New Member

New Member

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock