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]

I'm guessing that columns AI:AN are gradually filled from left to right. If so and those columns

- are blank or contain numbers, then try
=IF(A8="","",COUNT(AI8:AN8))

- are blank or may contain text (no formulas) then try
=IF(A8="","",COUNTA(AI8:AN8))

- are blank or may contain text (with formulas) then try
=IF(A8="","",6-COUNTBLANK(AI8:AN8))

If columns AI:AN are not always filled from left to right, then please confirm that if there are any 'gaps' in those columns the result should be ""
Also tell us if there are formulas in AI:AN or whether the values are manually entered.
Is it always numbers in AI:AN, or text or possibly both?

Many thanks for your response Peter. In answer to your questions:

1. AI:AN are filled from left to right one column at a time with no gaps.
2. The values are numbers.
3. The values, taken form other cells in the worksheet, are entered by a VBA code.
4. there are no formulas in the cells AI:AN

Thanks

 

[danjuma]

=IF(A8="","",COUNT(AI8:AN8))

Why didn't I think f that! Sometimes, one tends to over-complicate things. Thanks!

 

[danjuma]

Ok, I am stuck with the usual too many IF's with the formula below. Any assistance would be much appreciated. Many Thanks.

=if(W8="","",
if(and(W8>=0,W8<5),1,
if(and(W8>=5,W8<10),2,
if(and(W8>=10,W8<15),3,
if(and(W8>=15,W8<20),4,
if(and(W8>=20,W8<25),5,
if(and(W8>=25,W8<30),6,
if(and(W8>=30,W8<35),7,
if(W8>=35,W8<40),8,
if(and(W8>=40,W8<45),9,
if(and(W8>=45,W8<50),10,
if(and(W8>=50,W8<55),11,12))))))))))))

 

[GlennUK]

So that's just:

=MIN(ROUNDDOWN(W8/5,0)+1,12)

then, isn't it?

 

[danjuma]

So that's just:

=MIN(ROUNDDOWN(W8/5,0)+1,12)

then, isn't it?

Thanks GlenUK. Just given it a quick test, but does not seem to do what I wanted it to do, but don't think it's your formula that's wrong, it's probably because the values in cell W8 are in the format 00:00 (mm:ss). Will have to think of something else. All I was trying to do is to check what 5-min period cell W8 is in (i.e. if within 00:00 and 04:55, 1, if within 05:00 and 09:55, 2, and so on). Cheers

 

[GlennUK]

=min(rounddown(minute(w8)/5,0)+1,12)


... it would have been simpler if you'd described your requirements properly in the first place.

 

[danjuma]

=min(rounddown(minute(w8)/5,0)+1,12)


... it would have been simpler if you'd described your requirements properly in the first place.

Ok, that works! Many thanks.

Apologies for not describing my requirements properly in the first place. Did not think that the formatting would have made a difference. Cheers.

 

[Peter_SSs]

If I've understood correctly, possibly also this?

=IF(W8="","",MIN(INT(W8*288)+1,12))

 

[danjuma]

If I've understood correctly, possibly also this?

=IF(W8="","",MIN(INT(W8*288)+1,12))

Your code works fine as well Peter. Many thanks.

 

[danjuma]

Problem with too many IF levels with the following formular. I would be very grateful for someone's assistance please. Many thanks

=if(AW5="","",
if(and(AW5>33.9,AN5<21.5),1,
if(and(AW5>25.5,AN5<17.5),1,
if(and(AW5>20.5,AN5<13.5),1,
if(and(AW5>16.5,AN5<10.5),1,
if(and(AW5>14.5,AN5<9.5),1,
if(and(AW5>12.5,AN5<8.5),1,
if(and(AW5>11.5,AN5<7.5),1,
if(and(AW5>10.5,AN5<6.6),1,
if(and(AW5>9.5,AN5<6.2),1,
if(and(AW5>8.5,AN5<5.8),1,
if(and(AW5>7.5,AN5<5.2),1,
if(and(AW5>6.5,AN5<4.6),1,
if(and(AW5>6,AN5<4.4),1,
if(and(AW5>5.5,AN5<3.8),1,
if(and(AW5>5,AN5<3.6),1,
if(and(AW5>4.5,AN5<3.3),1,
if(and(AW5>4.4,AN5<2.9),1,
if(and(AW5>4.3,AN5<2.8),1,
if(and(AW5>3.9,AN5<2.6),1,""))))))))))))))))))))