"IF" function - 7 nested limit
 

Hi
Sometimes when I nest 8 "IF" functions the formula executes properly.
I am wondering, will such a formula always execute properly, or could
different conditions cause it to not execute?

In short, would the following be true?
If a formula works, it will always work so, go ahead and use it.

Thankyou
Ed

"IF" function - 7 nested limit
 

Hi Ed,

Please post your formula, inputs and result. I bet it isn't 8 *nested* IFs,
although it may be 8 IFs.
As to your question, I would never count on exceeding Excel's
specifications. Only in this case I don't think they have been exceeded.
In general, Excel will warn you if you try to let it do something can't.

--
Kind regards,

Niek Otten

"Ed" wrote in message
...
Hi
Sometimes when I nest 8 "IF" functions the formula executes properly.
I am wondering, will such a formula always execute properly, or could
different conditions cause it to not execute?

In short, would the following be true?
If a formula works, it will always work so, go ahead and use it.

Thankyou
Ed

"IF" function - 7 nested limit
 

Hi

You can nest IF's on 7 levels - no more. It means you can have max 8 nested
conditions.
NB! I speak about 7 nesting levels, not about 7 IF's in formula. To
understand the difference, compare formulas
=IF(a=b,x,IF(a<b,y,z))
(2 levels)
=IF(a=b,IF(b=c,v,W),IF(a<b,y,z))
(2 levels also)
=IF(a=b,w,x)+IF(b=c,y,z)
(1 level)


Arvi Laanemets



"Ed" wrote in message
...
Hi
Sometimes when I nest 8 "IF" functions the formula executes properly.
I am wondering, will such a formula always execute properly, or could
different conditions cause it to not execute?

In short, would the following be true?
If a formula works, it will always work so, go ahead and use it.

Thankyou
Ed

"IF" function - 7 nested limit
 

Good , Thanks for the help.
I see that 7 nested does mean 7 ifs plus the original if.
When my 8 ifs did not work and then did work after removing 1 of them, the
problem must have been something else.
Ed

"Niek Otten" wrote:

Hi Ed,

Please post your formula, inputs and result. I bet it isn't 8 *nested* IFs,
although it may be 8 IFs.
As to your question, I would never count on exceeding Excel's
specifications. Only in this case I don't think they have been exceeded.
In general, Excel will warn you if you try to let it do something can't.

--
Kind regards,

Niek Otten

"Ed" wrote in message
...
Hi
Sometimes when I nest 8 "IF" functions the formula executes properly.
I am wondering, will such a formula always execute properly, or could
different conditions cause it to not execute?

In short, would the following be true?
If a formula works, it will always work so, go ahead and use it.

Thankyou
Ed

"IF" function - 7 nested limit
 

Hi


"Ed" wrote in message
...
Good , Thanks for the help.
I see that 7 nested does mean 7 ifs plus the original if.

Not so! There can be no more than 7 directly nested if's, which can return 8
different results (a condition of seven filled+no condition filled).

=IF(cond1,resp1,resp2)
=IF(cond1,resp1,IF(cond2,resp2,resp3))
....
=IF(cond1,resp1,IF(cond2,resp2,IF(cond3,resp3,IF(c ond4,resp4,IF(cond5,resp5,
IF(cond6,resp6,IF(cond7,resp7,resp8)))))))

To have more if's in formula you need to 'split' nestings.


Arvi Laanemets



When my 8 ifs did not work and then did work after removing 1 of them, the
problem must have been something else.
Ed

"Niek Otten" wrote:

Hi Ed,

Please post your formula, inputs and result. I bet it isn't 8 *nested*
IFs,
although it may be 8 IFs.
As to your question, I would never count on exceeding Excel's
specifications. Only in this case I don't think they have been exceeded.
In general, Excel will warn you if you try to let it do something can't.

--
Kind regards,

Niek Otten

"Ed" wrote in message
...
Hi
Sometimes when I nest 8 "IF" functions the formula executes properly.
I am wondering, will such a formula always execute properly, or could
different conditions cause it to not execute?

In short, would the following be true?
If a formula works, it will always work so, go ahead and use it.

Thankyou
Ed

"IF" function - 7 nested limit
 

Hi Arvi
=IF(BP!S19=25,Cards!D25,IF(BP!S19=26,Cards!D26,IF( BP!S19=27,Cards!J1,IF(BP!S19=28,Cards!J2,IF(BP!S19 =29,Cards!J3,IF(BP!S19=30,Cards!J4,IF(BP!S19=31,Ca rds!J5,IF(BP!S19=32,Cards!J6,0))))))))
The above is a formula right out of my spreadsheet,
It has 8 ifs.
It is working fine.
If I understand you correctly, you are saying this formula should not work.
If so, even though it has been working, am I taking the risk that it might
not always work? (like on a different computer or different conditions during
execution on this same computer.)
Ed

"Arvi Laanemets" wrote:

Hi


"Ed" wrote in message
...
Good , Thanks for the help.
I see that 7 nested does mean 7 ifs plus the original if.

Not so! There can be no more than 7 directly nested if's, which can return 8
different results (a condition of seven filled+no condition filled).

=IF(cond1,resp1,resp2)
=IF(cond1,resp1,IF(cond2,resp2,resp3))
....
=IF(cond1,resp1,IF(cond2,resp2,IF(cond3,resp3,IF(c ond4,resp4,IF(cond5,resp5,
IF(cond6,resp6,IF(cond7,resp7,resp8)))))))

To have more if's in formula you need to 'split' nestings.


Arvi Laanemets



When my 8 ifs did not work and then did work after removing 1 of them, the
problem must have been something else.
Ed

"Niek Otten" wrote:

Hi Ed,

Please post your formula, inputs and result. I bet it isn't 8 *nested*
IFs,
although it may be 8 IFs.
As to your question, I would never count on exceeding Excel's
specifications. Only in this case I don't think they have been exceeded.
In general, Excel will warn you if you try to let it do something can't.

--
Kind regards,

Niek Otten

"Ed" wrote in message
...
Hi
Sometimes when I nest 8 "IF" functions the formula executes properly.
I am wondering, will such a formula always execute properly, or could
different conditions cause it to not execute?

In short, would the following be true?
If a formula works, it will always work so, go ahead and use it.

Thankyou
Ed

"IF" function - 7 nested limit
 

Ed wrote...
=IF(BP!S19=25,Cards!D25,
IF(BP!S19=26,Cards!D26,
IF(BP!S19=27,Cards!J1,
IF(BP!S19=28,Cards!J2,
IF(BP!S19=29,Cards!J3,
IF(BP!S19=30,Cards!J4,
IF(BP!S19=31,Cards!J5,
IF(BP!S19=32,Cards!J6,0))))))))

The above is a formula right out of my spreadsheet,
It has 8 ifs.
It is working fine.
....

The formula above has only 7 levels of nested function calls, so it
should always work in any version of Excel that can run on Win32
systems. That said, it could be shortened to

=IF(BP!S19=25,Cards!D25,
IF(BP!S19=26,Cards!D26,
IF(OR(BP!S19={27,28,29,30,31,32}),INDEX(Cards!J1:J 6,BP!S19-26),0))))))))

"IF" function - 7 nested limit
 

Harlan Grove wrote...
....
=IF(BP!S19=25,Cards!D25,
IF(BP!S19=26,Cards!D26,
IF(OR(BP!S19={27,28,29,30,31,32}),INDEX(Cards!J1: J6,BP!S19-26),0))))))))

I should know better than just to cut & paste. Too many right
parentheses above. Make it

=IF(BP!S19=25,Cards!D25,
IF(BP!S19=26,Cards!D26,
IF(OR(BP!S19={27,28,29,30,31,32}),INDEX(Cards!J1:J 6,BP!S19-26),0)))