Nested IF statement

  • Thread starter Thread starter Gouldeelocks
  • Start date Start date
G

Gouldeelocks

How many nested IF statements can I use at once? I am
trying to use one that is 4 IF statements deep. I recall
there being a limit back in the day of Office 95/97.
 
How many nested IF statements can I use at once? I am
trying to use one that is 4 IF statements deep. I recall
there being a limit back in the day of Office 95/97.
Click to expand...

The nesting limit for any type of function in Excel is seven.

If you seem to be exceeding that, there are better solutions to your problem.


--ron
 
How many nested IF statements can I use at once? I am
trying to use one that is 4 IF statements deep. I recall
there being a limit back in the day of Office 95/97.
Click to expand...

Same limit still: 7. Possible you could replace your nested IF calls with a
single VLOOKUP or CHOOSE call.
 
I am now trying to do an IF Then macro....it's been a
while since my C class! I should just figure out my error
with the 4 layered IF function! Thanks.
 
This is what I am looking at....

=IF(ISBLANK(G281),(IF(F281<Calendar!$M$4,((H281/365)*
(DAYS360(Calendar!$M$4,Calendar!$N$4,FALSE))),(IF
(F281>Calendar!$N$4,0,((H281/365)*(DAYS360(Calendar!
$M$4,F281,FALSE)))),(IF(G281<Calendar!$M$4,0,((H281/365)*
(DAYS360(Calendar!M$4,G281,FALSE)))))))))

I admit, it is confusing. I am trying to calculate
incremental costs based on a hire date or a term date.
 
This is what I am looking at....

=IF(ISBLANK(G281),(IF(F281<Calendar!$M$4,((H281/365)*
(DAYS360(Calendar!$M$4,Calendar!$N$4,FALSE))),(IF
(F281>Calendar!$N$4,0,((H281/365)*(DAYS360(Calendar!
$M$4,F281,FALSE)))),(IF(G281<Calendar!$M$4,0,((H281/365)*
(DAYS360(Calendar!M$4,G281,FALSE)))))))))
Click to expand...
...

Your formula is syntactically similar to the reformatted


=IF(
___1,
___IF(
_____0,
_____10,
_____(
_______IF(1,0,20),
_______IF(0,0,30)
_____)
___)
_)

[replace the underscores with spaces.]


Excel, at least XL97 SR-2, accepts this formula, even though it *SHOULD* be a
syntax error - the 3rd argument to your second IF call is

(IF(F281>Calendar!$N$4,0,((H281/365)*(DAYS360(Calendar!$M$4,F281,FALSE)))),
(IF(G281<Calendar!$M$4,0,((H281/365)*(DAYS360(Calendar!M$4,G281,FALSE))))))

which shouldn't be accepted. You're using unnecessary parentheses, and you've
just demonstrated that doing so can cause Excel problems. I believe you meant to
use


=IF(
___ISBLANK(G281),
___IF(
_____F281<Calendar!$M$4,
_____(H281/365)*DAYS360(Calendar!$M$4,Calendar!$N$4,FALSE),
_____IF(
_______F281>Calendar!$N$4,
_______0,
_______(H281/365)*DAYS360(Calendar!$M$4,F281,FALSE)
_____)
___),
___IF(
_____G281<Calendar!$M$4,
_____0,
_____(H281/365)*DAYS360(Calendar!M$4,G281,FALSE)
___)
_)


If so, this could be reduced to


=(H281/365)
_*IF(
___ISBLANK(G281),
___DAYS360(Calendar!$M$4,LOOKUP(F281,Calendar!$L$4:$N$4*{0,1,1},
_____Calendar!$N$4*{1,0,0}+F281*{0,1,0}+Calendar!$M$4*{0,0,1}),FALSE),
___DAYS360(Calendar!M$4,MAX(G281,Calendar!$M$4),FALSE)
_)
 
I am now trying to do an IF Then macro....it's been a
while since my C class! I should just figure out my error
with the 4 layered IF function! Thanks.
Click to expand...

The limit of seven nested functions applies to worksheet functions (i.e. the
name of *this* newsgroup).

If you are writing a VBA routine, than that limit does not apply.


--ron
 
I'm not sure I can find the exact spot of the error, seems
like you have a ton of unnecesary parethesis... looks like
this is what your trying to do, hope it helps...

=IF(ISBLANK(G281),IF(F281<Calendar!$M$4,H281/365*DAYS360
(Calendar!$M$4,Calendar!$N$4,FALSE),IF(F281>Calendar!
$N$4,0,H281/365*DAYS360(Calendar!$M$4,F281,FALSE))),IF
(G281<Calendar!$M$4,0,H281/365*DAYS360(Calendar!
$M$4,G281,FALSE)))

Cheers
Juan
 
Back
Top