if three values true then OK else missing value

[Banjara]

I have scenerio in excel

Job Nature, in Column T which have values (W), (F) (M)
Days to check in Column S which have values "No. of Days" & Paid
W/Card in Column N which have values True , False

The Thing I want to check is, if Job Nature is (W) and No. of Days is <= 365 and the W/Card is Ture then "OK" else write the any missing value.

Moving On check if Job Nature is (F) and No. of Days <= 30 and the W/Card is True then "OK" else write the missing value

Same is the case with (M) as (F)

 

[pjmorris]

Hi Banjara,

Firstly, welcome to the forum. If I've understood your requirement then try this:


=IF(AND(B2 <= 30+IF(A2="W",335,0),C2=TRUE,A2 <> "",B2 <> ""), "OK" , "Problem with: " & IF(AND(A2 <> "W",A2 <> "F",A2 <> "M"),"'Job Nature' ","") & IF(B2 > 30+IF(A2="W",335,0),"'Days' ","") & IF(NOT(C2=TRUE),"'W/Card'","") & ".")


Regards

 

[Banjara]

Hi pjmorris,

Thank you so very much,

it went flawless as required, though as a newbie, I didn't understand the formula fully,but will figure it out,

the other thing is , if its "PAID" in days, W/Card="FALSE" then jobnature doesn't matter , it should say PAID or OK

Thanks again for prompt and full guidance

 

[pjmorris]

Hi Banjara,

The formula has three parts:

The first: AND(B2 <= 30+IF(A2="W",335,0),C2=TRUE,A2 <> "",B2 <> "") tests the conditions you mentioned in your original e-mail.
the second "OK" simply returns ok if the first part is true.
The final section, everything after the OK compiles the statement of what is missing if the OK is not the right response.

To see it working, in Excel 2003 and earlier, select the cell containing the formula and press F9, you should then see intermediate results as the formula is evaluated. In Excel 2007 and later choose 'Evaluate Formula' from the Formulas ribbon, this gives a good breakdown of the function execution.

With regard to your new requirement, about 'PAID' in the Days column, does it matter if W/Card = TRUE in that circumstance and if so what?

Regards

 

[Banjara]

Thanks for making it simple for me

No, its not necessary if W/C is true or not, If its paid then its paid. W/Card is necessary in case if its Under Warranty i.e. the mentioned days.

 

[pjmorris]

Hi Banjara

Try this: =IF(B2="PAID", "PAID", IF(AND(B2<=30+IF(A2="W",335,0),C2=TRUE,A2<>"",B2<>""),"OK","Problem with: " & IF(AND(A2<>"W",A2<>"F",A2<>"M"),"'Job Nature' ","") & IF(OR(B2>30+IF(A2="W",335,0), B2=""),"'Days' ","") & IF(NOT(C2=TRUE),"'W/Card'","") & "."))

Hope this helps - if you study the structure of the formula then you should be able to make future amendments yourself.

I'm off on Christmas break now, so may not be able to reply immediately.

All the best.

 

[Banjara]

Thank you so very much for all the help

I ll try to understand it now, So nice of you