Help!!

[SarahBear]

I've been looking at this all week but whatever I try seems to come up with an error. I asked a question around the same form a couple of weeks but in true management style they now want it to do something different!!

There are two sets of questions on the form. On one side I have a list of 15 questions, all of which are mandatory so if any of them are marked as a 'No' the overall score will be a fail.

On the other side I have a list of 4 questions. These are technical and soft skill questions. If one of the questions is marked as a 'No' but the mandatory side is a pass then the score would be 'Requires Attention'. If two are marked as a 'No' but the mandatory side is a pass then the score would be 'Requires Development'. If 3 or more are marked as a 'No' with the mandatory score as a pass then the overall score would be a fail. As mentioned if there are any questions marked as a 'No' on the mandatory side it would be a fail regardless of whether all the technical/soft skills were a pass.

I've got this on the mandatory side - =IF(F30=15,"Pass","Fail") and this on the technical/soft skill side - =IF(M17<2,"Fail",IF(M17=3,"Requires Attention",IF(M17=2,"Requires Development","Pass"))) but am completely stuck on how to bring them both together to get an overall result.

Any help greatly appreciated!

I've tried =IF(AND..... but just seem to get an error every single time and it's driving me mad as I know that there must be a way of doing it!

 

[NeonRedSharpie]

[COLOR=#333333]=IF(F30=15,"Pass","Fail")[/COLOR]

This is in cell A1 for my example

[COLOR=#333333]=IF(M17<2,"Fail",IF(M17=3,"Requires Attention",IF(M17=2,"Requires Development","Pass")))[/COLOR]

This is in cell A2 for my example

=IF(A1 = "Pass",A2, A1)

 

[shg]

Maybe, if F30 and M17 both count Yes answers, ...

=IF(F30<15, "Fail", LOOKUP(M17, {0,1,2,3,4}, {"Fail","Requires Development","Requires Attention","Pass"}))

 

[SarahBear]

[COLOR=#333333]=IF(F30=15,"Pass","Fail")[/COLOR]

This is in cell A1 for my example

[COLOR=#333333]=IF(M17<2,"Fail",IF(M17=3,"Requires Attention",IF(M17=2,"Requires Development","Pass")))[/COLOR]

This is in cell A2 for my example

=IF(A1 = "Pass",A2, A1)

Ooooh, this works perfectly, thank you! It didn't even occur to me to reference the cells that the other formulas were sitting in.

Thank you!

 

[SarahBear]

Maybe, if F30 and M17 both count Yes answers, ...

=IF(F30<15, "Fail", LOOKUP(M17, {0,1,2,3,4}, {"Fail","Requires Development","Requires Attention","Pass"}))

NeonRedSharpie's one works perfectly but I'll give this a try as well - always good to have options! Thank you!

 

[shg]

You're welcome.