Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nested IF's
Hi
I'm trying to use a Nested IF formula function and am having a few troubles with it, I'm not sure if you can use the "IF(OR)/IF(AND)" function in a formula with other IF statements as well? Example: Column 1 Column2 Column 3 Column 4 Column 5 0.43 795 751 5 What I want to do is say if columns 1&2 are less than 1, and if columns 4 and 5 are blank, then say "invoices not sent" otherwise "ok" For the 1st part I can try: =IF((B3+C4<1),"Invs not sent","Ok") For the 2nd part: =IF(AND(E3="",F3=""),"Inv's not sent","Ok") I would like to combine this, but if say like in the 3 row above there is a number greater than 1 and a number in Column 4 or 5, I would like to say "Ok" Any Suggestions??? Thanks Guys Pete |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nested IF's
Not nested; one level but more than two conditions:
=IF(AND(B3+C3<1,D3="", E3=""),"NG","OK") I don't think you want this here, but you can nest: =IF(AND(OR(B3<1,C3<1),E3="", F3=""),"NG","OK") D-C Dave Stav19 Wrote: I'm trying to use a Nested IF formula function and am having a few troubles with it, I'm not sure if you can use the "IF(OR)/IF(AND)" function in a formula with other IF statements as well? Example: Column 1 Column2 Column 3 Column 4 Column 5 0.43 795 751 5 What I want to do is say if columns 1&2 are less than 1, and if columns 4 and 5 are blank, then say "invoices not sent" otherwise "ok" For the 1st part I can try: =IF((B3+C4<1),"Invs not sent","Ok") For the 2nd part: =IF(AND(E3="",F3=""),"Inv's not sent","Ok") I would like to combine this, but if say like in the 3 row above there is a number greater than 1 and a number in Column 4 or 5, I would like to say "Ok" ----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==---- http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups ----= East and West-Coast Server Farms - Total Privacy via Encryption =---- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nested IF's
On 6 Nov, 21:24, Dave D-C wrote:
Not nested; one level but more than two conditions: =IF(AND(B3+C3<1,D3="", E3=""),"NG","OK") I don't think you want this here, but you can nest: =IF(AND(OR(B3<1,C3<1),E3="", F3=""),"NG","OK") D-C Dave Stav19 Wrote: I'm trying to use a Nested IF formula function and am having a few troubles with it, I'm not sure if you can use the "IF(OR)/IF(AND)" function in a formula with other IF statements as well? Example: Column 1 Column2 Column 3 Column 4 Column 5 0.43 795 751 5 What I want to do is say if columns 1&2 are less than 1, and if columns 4 and 5 are blank, then say "invoices not sent" otherwise "ok" For the 1st part I can try: =IF((B3+C4<1),"Invs not sent","Ok") For the 2nd part: =IF(AND(E3="",F3=""),"Inv's not sent","Ok") I would like to combine this, but if say like in the 3 row above there is a number greater than 1 and a number in Column 4 or 5, I would like to say "Ok" ----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----http://www.newsfeeds.comThe #1 Newsgroup Service in the World! 120,000+ Newsgroups ----= East and West-Coast Server Farms - Total Privacy via Encryption =---- Cheers Dave, I'll give that a go! Just out of interest, can you use "IF(AND)/(OR)" In the same formula as a a normal if statement? I've tried before, but it never really seemed to work. Could it work on the above as an example? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nested IF's
Stav19 wrote:
Just out of interest, can you use "IF(AND)/(OR)" In the same formula as a a normal if statement? I've tried before, but it never really seemed to work. Could it work on the above as an example? XL will nest ANDs, ORs, and NOTs as deep as I want to. But I don't like very deep nesting. My last line (a normal IF statement?) has an AND and an OR. Dave D-C wrote: Not nested; one level, but more than two conditions: =IF(AND(B3+C3<1,D3="", E3=""),"NG","OK") I don't think you want this here, but you can nest: =IF(AND(OR(B3<1,C3<1),E3="", F3=""),"NG","OK") ----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==---- http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups ----= East and West-Coast Server Farms - Total Privacy via Encryption =---- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nested IF's
<XL will nest ANDs, ORs, and NOTs as deep as I want to
You can have many functions in one formula, but they can only be nested 7 levels deep, till Excel2007. In Excel2007 they can be nested 64 levels. Both limits apply to *all* functions and mixtures thereof, not just IF/AND/OR -- Kind regards, Niek Otten Microsoft MVP - Excel "Dave D-C" wrote in message ... | Stav19 wrote: | Just out of interest, can you use "IF(AND)/(OR)" In the same formula | as a a normal if statement? I've tried before, but it never really | seemed to work. Could it work on the above as an example? | | XL will nest ANDs, ORs, and NOTs as deep as I want to. | But I don't like very deep nesting. | My last line (a normal IF statement?) has an AND and an OR. | | Dave D-C wrote: | Not nested; one level, but more than two conditions: | =IF(AND(B3+C3<1,D3="", E3=""),"NG","OK") | | I don't think you want this here, but you can nest: | =IF(AND(OR(B3<1,C3<1),E3="", F3=""),"NG","OK") | | | ----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==---- | http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups | ----= East and West-Coast Server Farms - Total Privacy via Encryption =---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested If's | Excel Worksheet Functions | |||
Nested if's in vba | Excel Programming | |||
Nested If's | Excel Worksheet Functions | |||
How many nested IF's??? | Excel Discussion (Misc queries) | |||
NESTED IF's too many | Excel Programming |