Nested IF Formula

[MeganWoodBSA]

Hi, 

I am trying to put together a competency matrix to assess each employee against a number of roles which each have a number criteria with the overall out come either being 'Competent', 'Working Towards' or 'Not Competent'.

There are a number of criteria and although it is already complicated I now need to nest a nested IF formula, I have the following:

=IF(AND($B$3="P",$B$4>=$B$3),"C",IF(AND($B$3="M",$B$4="P"),"C",IF(AND($B$3="M",$B$4="R"),"","0")))

=IF(AND($C$3="P",$C$4>=$C$3),"C",IF(AND($C$3="M",$C$4="P"),"C",IF(AND($C$3="M",$C$4="R"),"","0")))

These both assess 2 different criteria and need to try to join them together along with a number of others.

I have managed to nest them as follows:

=IF(AND($B$3="P",$B$4>=$B$3),"C",IF(AND($B$3="M",$B$4="P"),"C",IF(AND($B$3="M",$B$4="R"),"",IF(AND($C$3="P",$C$4>=$C$3),"C",IF(AND($C$3="M",$C$4="P"),"C",IF(AND($C$3="M",$C$4="R"),"","0"))))))

Which doesn't throw up any errors but when I test it the results are not correct.

I have exhausted my excel knowledge and googled until my eye were watering and cannot figure out how to fix the issue.

Any ideas??

Thanks 

[#2]

With only three possible outcomes, you should not need more than two nested IF functions in the formula. So the general structure whould be:

=IF(TEST1,"Competent",IF(TEST2,"Working Towards","Not Competent"))

TEST1 is a formula which ONLY results in TRUE when all conditions are met to flag someone as being competent.
TEST2 only results in TRUE if all conditions for someone being "Working towards" are met.
I assumed TEST1 to be more restrictive than TEST2. If both TEST1 and TEST2 yield FALSE, the person must be not competent.