• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Nested IF function also using OR

J

Janine McLean

New Member
I am trying to use a formula to find out if a person has passed a unit. In order to pass they must have column AP as "completed" first. The other columns must either have "P" or "PP". I have tried the following formula but it is failing to work and I can't get my head round it. Any help will be much appreciated.

=IF(AP4="completed",IF(OR(I4="p",I4="pp"),IF(OR(M4="p",M4="pp"),IF(OR(O4="p",O4="PP",IF(OR(Q4="p",Q4="PP"),"Pass",""),""),""),""),""))
 
try this simple formula -

=IF(AND(AP4="completed",LEN(SUBSTITUTE(CONCATENATE(I4,M4,O4,Q4),"P",""))=0),"Pass","Fail")

Janine McLean said:
I am trying to use a formula to find out if a person has passed a unit. In order to pass they must have column AP as "completed" first. The other columns must either have "P" or "PP". I have tried the following formula but it is failing to work and I can't get my head round it. Any help will be much appreciated.

=IF(AP4="completed",IF(OR(I4="p",I4="pp"),IF(OR(M4="p",M4="pp"),IF(OR(O4="p",O4="PP",IF(OR(Q4="p",Q4="PP"),"Pass",""),""),""),""),""))
Click to expand...
 
your formula, revised version

=IF(AND(AP4="completed",IF(OR(I4="PP",I4="p"),IF(OR(M4="PP",M4="P"),IF(OR(O4="PP",O4="P"),IF(OR(Q4="PP",Q4="P"),TRUE,FALSE))))),"Pass","Fail")

Janine McLean said:
I am trying to use a formula to find out if a person has passed a unit. In order to pass they must have column AP as "completed" first. The other columns must either have "P" or "PP". I have tried the following formula but it is failing to work and I can't get my head round it. Any help will be much appreciated.

=IF(AP4="completed",IF(OR(I4="p",I4="pp"),IF(OR(M4="p",M4="pp"),IF(OR(O4="p",O4="PP",IF(OR(Q4="p",Q4="PP"),"Pass",""),""),""),""),""))
Click to expand...
 
xlstime said:
your formula, revised version

=IF(AND(AP4="completed",IF(OR(I4="PP",I4="p"),IF(OR(M4="PP",M4="P"),IF(OR(O4="PP",O4="P"),IF(OR(Q4="PP",Q4="P"),TRUE,FALSE))))),"Pass","Fail")
Click to expand...

Thanks - this actually makes more sense to me. I almost had it right but not quite!!
 
Actually, I have just tested it and if i4, m4, o4 and q4 are blank then the cell is still coming up as a "pass". If they haven't any entries in these cells then the end result needs to be blank.
 
Try.....

In S4, formula copy down and the other column

=IF(($AP4="Completed")*(LEN(I4&K4&M4&O4&Q4)>0)*ISERR(FIND("F",I4&K4&M4&O4&Q4)),"Pass","")

Regards
Bosco
 
You must log in or register to reply here.
Back
Top