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

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 =----

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?

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 =----

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 =----