more levels of nesting than allowed in a formula

[Pursuit2010]

<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?
<TR style="HEIGHT: 12.75pt" height=17></TR><TR style="HEIGHT: 12.75pt" height=17></TR><TR style="HEIGHT: 12.75pt" height=17></TR><TR style="HEIGHT: 12.75pt" height=17></TR><TR style="HEIGHT: 12.75pt" height=17></TR><TR style="HEIGHT: 12.75pt" height=17></TR><TR style="HEIGHT: 12.75pt" height=17></TR><TR style="HEIGHT: 12.75pt" height=17></TR><TR style="HEIGHT: 12.75pt" height=17></TR><TR style="HEIGHT: 12.75pt" height=17></TR><TR style="HEIGHT: 12.75pt" height=17></TR></TBODY></TABLE>

 

[danjuma]

Sir, I need your help once more please. trying to enter this one below as well, but can't really work out how to do it your suggested way - using Lookup and the code thing. My knowledge of excel is still primitive
Many thanks.

=if(AL4="","",
if(AL4=1,$Bc$18,
if(AL4=2,$Bi$18,
if(AL4=3,$Bc$39,
if(AL4=4,$Bi$39,
if(AL4=5,$BC$60,
if(AL4=6,$BC$20,
if(AL4=7,$BI$20,
if(AL4=8,$BC$41,
if(AL4=9,$BI$41,
if(AL4=10,$BC$62,
if(AL4=11,2*$BC$19,
if(AL4=12,2*$BI$19,
if(AL4=13,2*$BC$40,
if(AL4=14,2*$BI$40,
if(AL4=15,2*$BC$61,""))))))))))))))))

Ok, looks like I can use vlookup for this one. Will give it a try. Thanks

 

[Peter_SSs]

Try

=IF(AL4="","",CHOOSE(AL4,$BC$18,$BI$18,$BC$39,$BI$39,$BC$60,$BC$20,$BI$20,$BC$41,$BI$41,$BC$62,2*$BC$19,2*$BI$19,2*$BC$40,2*$BI$40,2*$BC$61))

 

[danjuma]

Try

=IF(AL4="","",CHOOSE(AL4,$BC$18,$BI$18,$BC$39,$BI$39,$BC$60,$BC$20,$BI$20,$BC$41,$BI$41,$BC$62,2*$BC$19,2*$BI$19,2*$BC$40,2*$BI$40,2*$BC$61))

Works absolutely fantastic! Once again, thank you very much sir.

 

[dsteinmetz21]

Hello. I am getting the same error of more nesting levels than allowed when trying the formula below. I'm not sure how to get around it, nor am I sure if I am using the right functions.

I have a table with numerous columns that needs to pull data from a different column based on destination (Vlookup value), and different ranges of the total shipment weight (Vlookup table array). For example, if the shipment weight is between 0 and 100, then the price is pulled from column 23. If the weight is between 100 and 500, then it is pulled from column 24, etc. I have tried several versions of the formula below to no avail. Any help would be greatly appreciated.

For some reason the formula isn't showing correctly below. Insert "0<TotalShipmentWeight,TotalShipmentWeight<100,23" where it says "insert above"


"=VLOOKUP(AG6,C6:AC26,(IF(AND(INSERT<totalshipmentweight,totalshipmentweight<100,23<totalshipmentweight,totalshipmentweight<100,23,
ABOVE <totalshipmentweight,totalshipmentweight<100,23,
<totalshipmentweight,totalshipmentweight<100,23,
IF(AND(100<=TotalShipmentWeight,TotalShipmentWeight<500,24,
IF(AND(500<=TotalShipmentWeight,TotalShipmentWeight<1000,25,
IF(AND(1000<=TotalShipmentWeight,TotalShipmentWeight<5000,26,
IF(TotalShipmentWeight>=5000,27,"N/A")))))),FALSE)"</totalshipmentweight,totalshipmentweight<100,23,
</totalshipmentweight,totalshipmentweight<100,23,
</totalshipmentweight,totalshipmentweight<100,23<totalshipmentweight,totalshipmentweight<100,23,

 

[dsteinmetz21]

So for some reason a portion of my formula refuses to show in my post. Sorry. Any help with the incomplete information provided would be greatly appreciated.

Thanks.

 

[jasonb75]

So for some reason a portion of my formula refuses to show in my post. Sorry. Any help with the incomplete information provided would be greatly appreciated.

Thanks.

Welcome to the board!

From what I can see of your formula there should be a simple fix, it would be easier if we could see your entire formula.

Try inserting single spaces in the formula before and after any < or > symbols, this stops the board software from interpreting parts of it as html code and displaying it incorrectly.

 

[jasonb75]

If I've understood your explaination correctly then it will be something like

=INDEX($Y$6:$AC$26,match($AG6,$C$6:$C$26,0),match(TotalShipmentWeight,{0,100,500,1000,5000}))

What range does "TotalShipmentWeight" refer to?

 

[Peter_SSs]

I'm also guessing a bit about just what you have and where, but just I'm just expanding on Jason's idea a bit. The main difference is that I have used the lower weight limits as headings rather than put them in the formulas.

I know I haven't used the same columns that you have but if this looks any use you could adapt to your columns.

Formula in C2 is copied down.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Destination	Total Weight	Price		Weight (Lower Limit) ->			0	100	500	1000
2	Dest 3	736	11		Destination
3	Dest 1	87.5	1		Dest 1			1	5	9	13
4	Dest 2	1111	14		Dest 2			2	6	10	14
5					Dest 3			3	7	11	15
6					Dest 4			4	8	12	16
7
Lookup Table

 

[dsteinmetz21]

If I've understood your explaination correctly then it will be something like

=INDEX($Y$6:$AC$26,match($AG6,$C$6:$C$26,0),match(TotalShipmentWeight,{0,100,500,1000,5000}))

What range does "TotalShipmentWeight" refer to?

Jason,

This formula works beautifully! Thank you so much! Now, I'd love it if you could explain why/how this works so I am no longer ignorant. Thanks!

What I'm building is a spreadsheet for quoting export freight. TotalShipmentWeight refers to a sum of weights entered for the shipment. The formula that you have given matches a price to the destination chosen. Price is dependent on the range of the weight, along with many other surcharges that are factored in.

The end result I'm looking for is a quick way to quote customers on their export freight that is all inclusive. This looks like it will do the trick!

Thanks again!

 

[jasonb75]

The combination of index and match functions is a good alternative to vlookup, it can do everything that vlookup can, and a lot more that it can't as well.

If you break the formula down into 3 parts, it creates a grid reference, reading down the table, then to the right, to get the result based on the criteria.

=INDEX($Y$6:$AC$26,match($AG6,$C$6:$C$26,0),match(TotalShipmentWeight,{0,100,500,1000,5000}))

The first range, Y6:AC26 is the location of the results to be returned, note the big difference in the range used between this and the vlookup equivilant.

The next part, match($AG6,$C$6:$C$26,0), looks for the matching data in column C to identify the correct row in the results table, much the same as vlookup, but it returns all columns in that row of the table, not just a single column. (the 0 at the end of the match returns an exact match, same as vlookup, 0 = FALSE)

The second match function identifies the correct column in the table, in my suggestion, match(TotalShipmentWeight,{0,100,500,1000,5000})), the array constant {0,100,500,1000,5000} works the same as a range of cells, this time there is no 0 (FALSE) in the formula so the result would be the highest value < or = to TotalShipWeight (peter's suggestion shows the same, with the weights in cells).

Combining all together creates the cross reference point in the table where the result is found.

Hope that makes sense.