Home |
Search |
Today's Posts |
#1
|
|||
|
|||
"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 |
#2
|
|||
|
|||
"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 |
#3
|
|||
|
|||
"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 |
#4
|
|||
|
|||
"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 |
#5
|
|||
|
|||
"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 |
#6
|
|||
|
|||
"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 |
#7
|
|||
|
|||
"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)))))))) |
#8
|
|||
|
|||
"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))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Several function questions (nested functions) | Excel Worksheet Functions | |||
Round a number in nested function | Excel Worksheet Functions | |||
I NEED HELP with the SPELLNUMBER Function | Excel Worksheet Functions | |||
clock | Excel Worksheet Functions | |||
Combining SUM Function with Nested If Statement | Excel Discussion (Misc queries) |