Formula not returning correct outcome

[dkflameboy]

Hi,

I need help with a formula that is not returning the correct outcome. I work for an awarding body and to pass one of our qualifications you need to achieve a minimum of 60% and at least score a 1 in all 12 questions answered. If you score 60% but score a zero in one question, you are still deemed not yet competent.

The problem I have is that a candidate has only achieved 53% but scored in all 12 questions and the formula I have below is deeming that a pass, which is incorrect as they haven't achieved 60%, can anyone help rectify this please.

=IF(COUNT(B13:O13)<>12, "Not Yet Competent", IF(MIN(B13:O13)<=0,"Not Yet Competent",IF(P14>=59%,"Pass","Not Yet
Competent")))

Many thanks

 

[jmacleary]

Hi there. Reading your code it looks like it should work, but you are referring to row 13 throughout, except in the last if. Try changing P14 to P13 to see if thats the problem.

 

[jmacleary]

Also, you are counting 14 columns, is this correct?

 

[steve the fish]

B13:013 is not 12 cells?

 

[dkflameboy]

Also, you are counting 14 columns, is this correct?

Yes, the exam paper is made up of 14 questions and the candidate has to answer a maximum of 12 from those 14.

 

[dkflameboy]

Thank you for all your help, I think I've cracked it now that @jmacleary validated the formula.

The formula refers to 59% but the cell P14 is formatted as a standard number cell, this is because the % sign was throwing the system we feed these in to out of whack! thank you all for your help.