Nested If / Question about a logic

[dreams]

Good Day Experts,

I would like to write a if condition which check parameters before filling one cell. If one condition is wrong the check should stop.
In one data record the check will do the following:
1. Check if B3=5 then continue and check if C3= true then continue and check if D3>=31.10.2011 write "pass" in cell G3.
2. Check if B4=5 then continue and check if C4= true then continue and check if D4>=31.10.2011 if not, check if E4>D4 and check if F4>=D4 write "pass" in cell G4. otherwise "fail" and copy the F4 value into D4.
3. Check if B5=1 write "fail" in cell G5.
4. Check if B6=5 then continue and check if C6= true then continue, if not, write "fail" in cell G6.

The sample is attached.

Could you please help me on that way?

Thank you in advance

[mikerickson]

In the attachment, D4 = 6/30/11, which fails the >=10/31/2011 test, yet you write that it should pass.

Assuming that is an error, try this in G3

=IF(AND(B4=5,C4,DATEVALUE("10/31/2011")<=D4),"pass","fail")

[zbor]

Here, try this: =IF(B3*C3=5,IF(MAX(D3,E3-1,F3)>=40847,"Pass","Fail"),"Fail")

or

=IF(B3*C3=5;IF(MAX(D3;E3-1;F3)>=40847;"Pass";"Fail");"Fail")

[dreams]

Hi Mikerickson,

i tried the formula you wrote but it was not working.

Thanks

[dreams]

Hi Zbor,

Thank you for replying.
The formula is working. Could you please chnage the formula so that B3 should have the value 5 to continue with the logic otherwise "fail" and C3 should have the value "true" to get continue otherwise "fail"?
Once the result in the cell G3= pass, coud we check the date in cell F3 and compare with the date in cell D3? If F3>D3, write F3 Date in D3 cell.

Thank you for your help.

[zbor]

Could you please chnage the formula so that B3 should have the value 5 to continue with the logic otherwise "fail" and C3 should have the value "true" to get continue otherwise "fail"?

Hi dreams. Formula already working that way if you have TRUE/FALSE in C column. If not (-10 / -2 will also give you 5) try this:

=IF(B3*(C3=TRUE())=5;IF(MAX(D3;E3-1;F3)>=40847;"Pass";"Fail");"Fail")

Once the result in the cell G3= pass, coud we check the date in cell F3 and compare with the date in cell D3? If F3>D3, write F3 Date in D3 cell.

In this way you will get Circular Reference Warning because G3 check F3 and then F3 change depending on G3 (then again F3 check G3 and so on).
Either add one more column where you would have corrected date or you need to use macro. What solution you prefere?

[dreams]

Hi dreams. Formula already working that way if you have TRUE/FALSE in C column. If not (-10 / -2 will also give you 5) try this:

=IF(B3*(C3=TRUE())=5;IF(MAX(D3;E3-1;F3)>=40847;"Pass";"Fail");"Fail")



In this way you will get Circular Reference Warning because G3 check F3 and then F3 change depending on G3 (then again F3 check G3 and so on).
Either add one more column where you would have corrected date or you need to use macro. What solution you prefere

Thank you.
I prefere to add one new column.

I added additionally 3 columns for 3 more conditions.
Before writing pass or fail in G3, the logic should check additionally if one of the following cells hat the follwing value H3=Dumba, , I3=Diosse, J3=Kake.
if one value exist it is ok.
Thanks

[dreams]

Thank you.
I prefere to add one new column.

I added additionally 3 columns for 3 more conditions.
Before writing pass or fail in G3, the logic should check additionally if one of the following cells hat the follwing value H3=Dumba, , I3=Diosse, J3=Kake.
if one value exist it is ok.
Thanks

From where is the value 40847?

Thanks

[zbor]

Value of a date 10/31/2011