Logic Statement

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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")))))))))))
 
It is limited to 7 nested levels. You can use a lookup table to do this.

--
Regards,
Tom Ogilvy

Tracy Sanders said:
I have been working with an Excel formula and seem to be having a problem.
Click to expand...
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.
 
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
 
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

Tracy said:
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"))))))))
Click to expand...
=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"))))))))))))
 
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 *
 
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"
Click to expand...

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


--ron
 
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 *
 
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.
Click to expand...

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
 
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 *
 
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)
 
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.
 
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 *
 
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
 
Back
Top