Logic Statement

[Tracy Sanders]

I have been working with an Excel formula and seem to be having a problem. The first formula displayed below works fine, but as soon as I add an extra sub logic statement demonstrated in the second formula below, I get an error. Can anyone tell me what I am doing wrong or is Excel limited to only eight sub logic statements? I need 11 for what I am doing. Thank you in advance for any help

=IF(D16>23.99,".07",IF(D16>18.49,".065",IF(D16>17.99,".06",IF(D16>17.49,".055",IF(D16>16.99,".05",IF(D16>16.49,".045",IF(D16>15.99,".04",IF(D16>15.49,".035","F")))))))

=IF(D16>23.99,".07",IF(D16>18.49,".065",IF(D16>17.99,".06",IF(D16>17.49,".055",IF(D16>16.99,".05",IF(D16>16.49,".045",IF(D16>15.99,".04",IF(D16>15.49,".035",IF(D16>14.99,".03",IF(D16>14.49,".025",IF(D16>13.99,".02",IF(D16>1,".00","F")))))))))))

 

[Tom Ogilvy]

It is limited to 7 nested levels. You can use a lookup table to do this.

--
Regards,
Tom Ogilvy

I have been working with an Excel formula and seem to be having a problem.

The first formula displayed below works fine, but as soon as I add an extra
sub logic statement demonstrated in the second formula below, I get an
error. Can anyone tell me what I am doing wrong or is Excel limited to only
eight sub logic statements? I need 11 for what I am doing. Thank you in
advance for any help.

 

[rvExcelTip]

Formulas are also limited to 256 characters which you may hit eve
before the nesting limit.

In general when you have that many levels it is more appropriate t
write a little user function in VBA to do the same job. It certainl
will render future (inevitable) maintenance easie

 

[Frank Kabel]

Hi
you're limited to a maximum of seven nested functions. Try creating a
lookup table like the following
A B
0 F
15.49 0.35
15.99 0.4
16.49 0.5
.....

Now you can use VLOOOKUP:
=VLOOKUP(D16,A1:B10,2,1)


--
Regards
Frank Kabel
Frankfurt, Germany

I have been working with an Excel formula and seem to be having a
problem. The first formula displayed below works fine, but as soon
as I add an extra sub logic statement demonstrated in the second
formula below, I get an error. Can anyone tell me what I am doing
wrong or is Excel limited to only eight sub logic statements? I need
11 for what I am doing. Thank you in advance for any help.


=IF(D16>23.99,".07",IF(D16>18.49,".065",IF(D16>17.99,".06",IF(D16>17.49
,".055",IF(D16>16.99,".05",IF(D16>16.49,".045",IF(D16>15.99,".04",IF(D1
6>15.49,".035","F"))))))))

=IF(D16>23.99,".07",IF(D16>18.49,".065",IF(D16>17.99,".06",IF(D16>17.49
,".055",IF(D16>16.99,".05",IF(D16>16.49,".045",IF(D16>15.99,".04",IF(D1
6>15.49,".035",IF(D16>14.99,".03",IF(D16>14.49,".025",IF(D16>13.99,".02
",IF(D16>1,".00","F"))))))))))))

 

[Frank Kabel]

Hi
just a small point
AFAIK 1024 characters are allowed for formulas

 

[A.W.J. Ales]

Hi,

Another small point.
It seems that in ExcelXP for some reason the maximum depth is increased to
8.
=IF(A1=1,1,IF(A1=2,2,IF(A1=3,3,IF(A1=4,4,IF(A1=5,5,IF(A1=6,6,IF(A1=7,7,IF(A1
=8,8,100)))))))) is accepted and works properly.

Not that I recommend it, but it does work.
One level deeper was "blocked"

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[Ron Rosenfeld]

Hi,

Another small point.
It seems that in ExcelXP for some reason the maximum depth is increased to
8.
=IF(A1=1,1,IF(A1=2,2,IF(A1=3,3,IF(A1=4,4,IF(A1=5,5,IF(A1=6,6,IF(A1=7,7,IF(A1
=8,8,100)))))))) is accepted and works properly.

Not that I recommend it, but it does work.
One level deeper was "blocked"

Just a small nit -- your formula only shows 7 nesting levels. The "outer
wrapper" is not considered to be "nested".


--ron

 

[A.W.J. Ales]

Ron,

Oke. If you look at it that way.
But I seem to remember that before with Excel97 I didn't come further than 1
level less deep.
As soon as i get to a Excel97 version again I'll give it a try, but I'll
accept the point anyhow.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[Ron Rosenfeld]

Oke. If you look at it that way.
But I seem to remember that before with Excel97 I didn't come further than 1
level less deep.
As soon as i get to a Excel97 version again I'll give it a try, but I'll
accept the point anyhow.

I'd be curious as to your results in '97. My recollection is that the
documentation indicated a maximum of seven nesting levels, same as the
documentation for current versions. Of course, documentation does not always
match reality <g>.


--ron

 

[A.W.J. Ales]

Oke Ron,

I'll let you know, most likely in this thread.
But will take a few days i'm afraid before I get to XL97.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[Bob Phillips]

Guys,

Just tried it on XL97, and it didn't complain until I tried to add IF test
#9, that is one outer and 7 nested IFs worked fine.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

The formula posted
=IF(A1=1,1,IF(A1=2,2,IF(A1=3,3,IF(A1=4,4,IF(A1=5,5,IF(A1=6,6,IF(A1=7,7,IF(A1
=8,8,100))))))))

Works fine in Excel 97 SR-2, US English, Windows 98 SE.

 

[A.W.J. Ales]

Ron, Bob, Tom,

Oke. So i'm having a lousy memory
Really thought that I had one level less deep in XL97.

Pff I'm getting to old for this.
But : I surrender.

In the meanwhile : Tracy (the OP thinks) : What is this all about <g>


--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[rvExcelTip]

The nesting limit can be circumvented by Naming parts of the test using
Define Name

Suppose we want to generate a nested IF for the following test:

If A4 = 1 Then 11
ElseIf ALS A4 = 2 Then 22
ElseIf ALS A4 = 3 Then 33
ElseIf ALS A4 = 4 Then 44
...
ElseIf A4 = 13 Then 130 Else "Not Found"

We first define a partial formula Named OneToSix
=IF( $A$4=1,11,IF( $A$4=2,22,IF( $A$4=3,33,IF( $A$4=4,44,IF(
$A$4=5,55,IF( $A$4=4,44,IF( $A$4=5,55,IF( $A$4=6,66,FALSE ) ) ) ) ) ) )
)

And another formula Named SevenToThirteen
=IF( $A$4=7,77,IF( $A$4=8,88,IF( $A$4=9,99,IF( $A$4=10,100,IF(
$A$4=11,110,IF( $A$4=12,120,IF( $A$4=13,130,"Not Found" ) ) ) ) ) ) )

Finally we type the Following "real" formula into the cell
=IF(OneToSix,OneToSix,SevenToThirteen)

With this formula, the nesting limit has been circumvented because
neither the final nor the partial (named) formulas exceed that limit

I did not test if the partial formulas themselves can be composed of
still deeper Named partial tests. As said before if you have to go
that deep, better use a VBA formula

I translated this suggestion from the original website at:
http://users.pandora.be/ingrid/excel/namen.htm#nest