Nested IF's

[Stav19]

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

 

[Dave D-C]

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]

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

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?

 

[Dave D-C]

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.

 

[Niek Otten]

<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

| 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")
|
|
|
|
|