If and/or

[Legacy 385836]

Hi

I have a spreadsheet to check compliance. I have 3 columns, 2 of which are yes/no and the third is yes/no/N/A.
I need the final column to display No if any of the previous 3 columns show No, and to display yes if al the columns are yes or n/a.

I have tried to use an IF statement and nested IF statements with no luck.

Any help Would be much appreciated.

Thanks.

 

[Gerald Higgins]

Perhaps something like this
=if(A1&B1&C1="","",if(countif(A1:C1,"NO")>0,"NO","YES"))

 

[mrpudley]

Howdy,

I have something similar and I can't figure out what I am doing wrong. My formula returns a "> 25 days" when the field is actually blank. I do have "0" values which I need to report on, however blanks should return a "Not Printed" string.

=IF(F2<5,F2,IF(F2>25, "> 25 days",IF(AND(F2>4,F2<10),"5 to 10 days",IF(AND(F2>10,F2<16),"10 to 15 days","15 to 25 days"))))

Any thoughts?

Thanks,

Brent

 

[Gerald Higgins]

If your target cell really is totally blank, then the value is NOT greater than 25.
HOWEVER, if the target cell APPEARS blank but is actually - for example - a space character, or double quotes, Excel evaluates this as being greater than 25 (or any other number).

Try something like this
=if(F2="","Not Printed",IF(F2<5,F2,IF(F2>25, "> 25 days",IF(AND(F2>4,F2<10),"5 to 10 days",IF(AND(F2>10,F2<16),"10 to 15 days","15 to 25 days")))))

 

[mrpudley]

That's great Gerald thansk!

Now going by that example I tried to modify is slightly to:

=if(F2="","Not Printed",IF(F2=0, "0 days", F2=1, "1 day", f2= 2; "2 days", f2=3, "3 days", f2=4, "4 days", f2=5, "5 days", IF(F2>25, "> 25 days", IF(AND(F2>4,F2<10),"5 to 10 days",IF(AND(F2>10,F2<16),"10 to 15 days","15 to 25 days")))))

However that doesn't work. Is there an obvious error in my formula?

Thanks again!

Brent

 

[Gerald Higgins]

Yes, the bits where it says F2=1, F2=2 and so on, all need their own IF statements.

Having said that, why not try something like this
=if(F2="","Not Printed",if(F2>25,"> 25 days",if(F2>15,"15 to 25 days",if(F2>10,"10 to 15 days",if(F2>5,"5 to 10 days",F2&" days")))))

Also, be careful with your " > " statements.
Consider also using " >= ", or make sure that the way this formula deals with the break points, is actually what you want.

 

[mrpudley]

Thanks Gerald!

That worked perfectly.

Cheers!