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>

 

[Peter_SSs]

Not sure exactly what sort of values might be allowed in columns AN and AW, but setting up a lookup table and formula like this might give you what you want.

Formula in AY5 is copied down.

Excel Workbook
	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB
1														0	0
2														3.9	2.6
3														4.3	2.8
4														4.4	2.9
5	2.2									32		1		4.5	3.3
6	18.5									47.83		1		5	3.6
7	18.93									9.93				5.5	3.8
8	20.44									16.52				6	4.4
9	19.3									39.94		1		6.5	4.6
10	4.1									24.44		1		7.5	5.2
11	22.87									3.47				8.5	5.8
12	25.14									28.18				9.5	6.2
13	16.41									40.56		1		10.5	6.6
14	12.89									28.27		1		11.5	7.5
15	2.91									37.48		1		12.5	8.5
16	18.52									2.25				14.5	9.5
17	26.51									2.58				16.5	10.5
18	18.67									1.98				20.5	13.5
19	19.1									49.49		1		25.5	17.5
20	11.13									29.21		1		33.9	21.5
21	12.84									45.22		1
Replace nested IFs

 

[jasonb75]

Using Peter's layout

=IF(AN5<VLOOKUP(AW5,$BA$1:$BB$20,2),1,"")

 

[Peter_SSs]

Using Peter's layout

=IF(AN5<VLOOKUP(AW5,$BA$1:$BB$20,2),1,"")

Jason

I see 1 possible problem and one definite problem with your solution.

1. Possible problem (easily fixed with an extra IF) is that the OP's original formula starts with
=if(AW5="","",
which makes me think that AW5 is possibly populated with a formula that could return "". In this case your formula errors.

2. Definite problem (assuming I have understood correctly) is that if AW5 is exactly equal to one of the values in column BA and AN5 is just below the corresponding column BB value then your formula returns a false "1". For example, the combination of AW5 = 20.5 and AN5 = 12 does not meet any of the listed criteria yet your formula returns a "1".

I initially didn't have the 0 values in the lookup table, and still don't know if that is suitable or whether negative values might turn up in the data. After adding the zeros to the table I forgot to remove a couple of the tests in my AND() function.

In any case perhaps one of these versions of your formula would be better than mine.

=IF(AN5< VLOOKUP(AW5-1/1000000,$BA$1:$BB$20,2),1,"")

=IF(AW5="","",IF(AN5< VLOOKUP(AW5-1/1000000,$BA$1:$BB$20,2),1,""))

Or perhaps you can see another better way again?

 

[danjuma]

Peter, thank you most kindly, I am most grateful. Also, thank you for your suggestion Jason, most appreciated.

@Peter,

Just to clarify your assumptions:

1. You are correct, AW5 gets its value from a vlookup formula in the cell. I.E. Cell AW5 contains the formular
=IF(BD5="","",VLOOKUP(BD5,Data!$C$5:$F$500,3,FALSE))

The if(AW5="","",
comes about if BD5 is "", then AW5 will be "".

2. For AW5, no negative values or zero would be returned.
For AN5, the values are got from data scrapped from a website. It will either return a value or blank, but no negative value or zero would be returned.

Many thanks
Dan

 

[danjuma]

2. For AW5, no negative values or zero would be returned.
For AN5, the values are got from data scrapped from a website. It will either return a value or blank, but no negative value or zero would be returned.

CORRECTION to the statement above. Should read:

2. For AW5, no negative values or zero would be returned.
For AN5, the values are got from another cell AH5, which gets its value from data scrapped from a website. It will either return a value or blank, but no negative value or zero would be returned.

Cheers
Dan

 

[Peter_SSs]

For AN5, the values are got from another cell AH5, which gets its value from data scrapped from a website. It will either return a value or blank, but no negative value or zero would be returned.

I guess that means the formula we are working on should also check for blanks in AN5. Perhaps

=IF(COUNT(AN5,AW5)=2,IF(AN5< VLOOKUP(AW5-1/1000000,$BA$1:$BB$20,2),1,""),"")

 

[danjuma]

I guess that means the formula we are working on should also check for blanks in AN5. Perhaps

=IF(COUNT(AN5,AW5)=2,IF(AN5< VLOOKUP(AW5-1/1000000,$BA$1:$BB$20,2),1,""),"")

Thank you most kindly. Much obliged.

 

[BLDupree]

So I'm working on aging receivables and I'm trying to place invoices in buckets based on the terms we've established with the client, the invoice sent date and the current date. I have to drop them in buckets based on a week, 2 weeks,...45 days, and 60 days. My equation looks like this so far but I can't add any more arguments because the error this post is about.

=IF(AND($F4=15,$H4<=30),$K4,IF(AND($F4=30,$H4<=30),$K4,IF(AND($F4="AMEG",$H4<=30),$K4,IF(AND($F4="CEI",$H4<=30),$K4,IF(AND($F4="FRC",$H4<=30),$K4,IF(AND($F4="GLEN",$H4<=30),$K4,IF(AND($F4="MLLC",$H4<=30),$K4,IF(AND($F4="MLLC",$H4<=30),$K4,))))))))

I need to add more terms but well I'm sure you get it. Please help me out if you can.

 

[Peter_SSs]

=IF(AND($F4=15,$H4<=30),$K4,IF(AND($F4=30,$H4<=30),$K4,IF(AND($F4="AMEG",$H4<=30),$K4,IF(AND($F4="CEI",$H4<=30),$K4,IF(AND($F4="FRC",$H4<=30),$K4,IF(AND($F4="GLEN",$H4<=30),$K4,IF(AND($F4="MLLC",$H4<=30),$K4,IF(AND($F4="MLLC",$H4<=30),$K4,))))))))

I need to add more terms but well I'm sure you get it. Please help me out if you can.

Welcome to the MrExcel board!

Without knowing what 'more terms' you want to add, it is hard to know what the best approach is.

However, I can make a couple of comments/suggestions about your formula so far.

The two red bits seem identical so you shouldn't need both of those.

Your formula so far has a whole lot of conditions that ..
a) have $H4<=30 as one of them, and
b) all return the value in $K4
.. so the formula so far could be replaced with this ..

=IF(AND(OR($F4={15,30,"AMEG","CEI","FRC","GLEN","MLLC"}),$H4<=30),$K4,"??")

If you could describe more fully what you have, where and what the formula is trying to achieve, it may bge that a better method can be suggested.

Perhaps you could show us a small screen shot or two to help explain. See my signature block for suggestions.

 

[BLDupree]

I need to age receivables based on each terms grace period from the date their invoice was sent.

Example: I would need to make sure the balance in row 3 goes into the 1-8 column because they have a 30 day grace period but it's been 38 days since we mailed their invoice.

I also don't know how to deal with row 24 because they pay the following month from the bill shipping date on the 30th of that following month.

Let me know if I was clear enough in my explanation.