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>

 

[pst1187]

Hi everyone.
Need some help with this:
=IF(H47="1A", "Narcisa", IF(H47="1B", "Georgeta", IF(H47="2A", "Alis",IF(H47="2B", "Ana-Maria", IF(H47="3A", "Narcisa", IF(H47="3B", "Maria", IF(H47="4A", "Denisa", IF(H47="4B", "Anamaria", IF(H47="5A", "Simona", IF(H47="5B", "Catalin",IF(H47="7A", "Narcisa", IF(H47="7B", "Catalin", IF(H47="8A", "Cristina", IF(H47="8B", "Cleopatra", IF(H47="9A", "Adela", IF(H47="9B", "Ioana",IF(H47="10A", "Mihaela", IF(H47="10B", "Camelia", IF(H47="11A", "Maria", IF(H47="11B", "Elena", IF(H47="12A", "Monica", IF(H47="12B", "Simona", IF(H47="13A", "Daria",IF(H47="13B", "Elena", IF(H47="14A", "Mihaela", IF(H47="14b", "Madalina", IF(H47="15A", "Adela", IF(H47="15B", "NA", IF(H47="16A", "Roxana", IF(H47="16B", "Paula",IF(H47="17A", "NA", IF(H47="17B", "NA", IF(H47="18A", "NA", IF(H47="18B", "NA", ""))))))))))))))))

 

[Gerald Higgins]

Hi, welcome to the board.

Have you read the earlier posts in this thread that talk about a LOOKUP table ?

That's what you need.

 

[JaseAHQ]

Hi gents, I have a nesting error after moving a spreadsheet from Excel 2016 to Excel 2003...don't ask... Please could someone help me to get the two formulae below to work on the 2003 version?

Thanks guys - seriously appreciate it. Jase.


=IF(O6=6,"Packet 10",IF(O6=12,"Packet 9",IF(O6=14,"Packet 8",IF(O6=16,"Packet 7",IF(O6=18,"Packet 6",IF(O6=24,"Packet 5",IF(O6=26,"Packet 4",IF(O6=28,"Packet 3",IF(O6=30,"Packet 2",IF(O6=36,"Packet 1"))))))))))


=IF(AND(Q6>DATA!$M$3,Q6<DATA!$N$3),"ABC 14",IF(AND(Q6>DATA!$M$4,Q6< DATA!$N$4),"ABC 15",IF(AND(Q6>DATA!$M$5,Q6< DATA!$N$5),"ABC 16",IF(AND(Q6>DATA!$M$6,Q6< DATA!$N$6),"ABC 17",IF(AND(Q6>DATA!$M$7,Q6< DATA!$N$7),"ABC 18",IF(AND(Q6>DATA!$M$8,Q6< DATA!$N$8),"ABC 19",IF(AND(Q6>DATA!$M$9,Q6< DATA!$N$9),"ABC 20",IF(AND(Q6>DATA!$M$10,Q6< DATA!$N$10),"ABC 21"))))))))

 

[markmzz]

Hi!

Try this for the first formula:

="Packet "&MATCH(O6,{36;30;28;26;24;18;16;14;12;6},0)

Markmzz

 

[JaseAHQ]

MarkMzz - Thank you mate; it works a treat - you have helped me out big time! With my best wishes and thanks again.Jase

 

[markmzz]

MarkMzz - Thank you mate; it works a treat - you have helped me out big time! With my best wishes and thanks again.Jase

Hi Jase,

You are welcome and thanks for the return.

By the way, for the second, try this:

="ABC "&13+MATCH(1,INDEX((Q6 > DATA!$M$3:$M$10)*(Q6 < DATA!$N$3:$N$10),),0)<strike></strike>

Markmzz

 

[JaseAHQ]

MarkMzz,Thank you again mate - i'm hugely grateful. I went for a VLOOKUP in the end as it was driving me mad:=VLOOKUP(Q6,DATA!$M$18:$N$173,2,0)Will try yours now out of curiosity!Best wishes mate,Jase

 

[markmzz]

MarkMzz,Thank you again mate - i'm hugely grateful. I went for a VLOOKUP in the end as it was driving me mad:=VLOOKUP(Q6,DATA!$M$18:$N$173,2,0)Will try yours now out of curiosity!Best wishes mate,Jase

I'm glad to help and thanks for the return.

Markmzz

 

[Jinda]

Hi all,

Would anybody be able to offer me some advice to convert this formula into a long form single cell?

=ROUND
	IF(A1 >= 500000 , 21330 + 0.055 * (A1 - 500000),
	IF(A1 >= 300000 , 11330 + 0.05 * (A1 - 300000),
	IF(A1 >= 250000 , 8955 + 0.0475 * (A1 - 250000),
	IF(A1 >= 200000 , 6830 + 0.0425 * (A1 - 200000),
	IF(A1 >= 100000 , 2830 + 0.04 * (A1 - 100000),
	IF(A1 >= 50000 , 1080 + 0.035 * (A1 - 50000),
	IF(A1 >= 30000 , 480 + 0.03 * (A1 - 30000),
	IF(A1 >= 12000 , 120 + 0.02 * (A1 - 12000),
	0.01 * A1)
	)))))))

<tbody>
</tbody>

I have no idea of how to incorporate the look up function... and would appreciate any help.

Best

 

[Gerald Higgins]

...to convert this formula into a long form single cell

It looks like that's what you've already got.

If you actually want that exact formula in a single cell, just type in all the elements, OR, cut and paste them in.
The formula should work.

But you've also referred to a look up function, which is something different.

Here's one way . . . there are others . . .

Step 1. Set up a lookup table holding your reference data, something like this . . .

0.............0..........0.01
12000......120.......0.02
30000......480.......0.03

and so on.
It's important that they are in this sequence, starting with 0, then 12000, then 30000, and so on.

Let's say you put this table in the range B1:D9.

Step 2) Your lookup formula can then be

=VLOOKUP(A1,B1:D9,2,TRUE)+(VLOOKUP(A1,B1:D9,3,TRUE)*(A1-VLOOKUP(A1,B1:D9,1,TRUE)))

I've left off the ROUND element, you can add that if you like.

One big advantage of the lookup option is that it makes it much easier to change the lookup values, and add new levels or delete existing levels.