Creating an evaluation form to calculate 3 different scores in Excel

Hello everybody. I have a question. I want to create an evaluation form with specific requirements.
There are 3 questions and the 2 first are critical, which means that if it is a "No", it's a Fail for the whole evaluation.
However for the 3rd question, I want to have "Yes", "No", and "Partial". This means that with a "Yes" it will be 100%, with a "No" 0, and with "Partial" I want to have any score below 80%. That way, we can evaluate the effort if it is "Partially" met, instead of giving another 0. I hope this is clear enough, thank you!

So in the sample. let's say that the Test 3 marking will have "Yes, No, Partial". I want to have 100% with "Yes", 0% with "No" and for example 50% for "Partial". The 2nd formulas with the OR function, are to ignore N/A's.

The goal is to have AI below 80% if the Test 2 question is "Partial" and the other 2 "Yes". Also, it needs to be "0" when Test 2 has "No".
I hope it is clear now. Thank you!

There are instructions at the top of the page explaining how to attach your sample workbook.

Below formula independent from AD:AI, it is counting Yes=1, No=0, Partial=0.5, then sum up and divide by 3

Please Login or Register  to view this content.

Are AD:AI columns are helpers?
My suggestion is placed in AA3 and ignore those columns.

No, AD: AI is calculating weights for A3, C3, and D3 accordingly. I need to have A and D3 as critical attributes, so they will give "0" if the marking is "No".
AG:AI, is to ignore N/A in A3, C3 and D3.

To clarify a bit more, if Test1 and Test 3 is "Yes", and Test 2 is "Partial", the overall score should be below 80%(so far your formula works fine).
But if Test 1, or Test 3 is "No", the score should be "0". The tricky part is that if Tes1 and Test 3 are "Yes", but Test 2 is "No", then the score should also be "0".
Thank you again.

Administrative Note:

@All Participants

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.

You mean if one of the three cells is "NO", score 0, else, Yes=1, No=0, partial=0.5 then sum
up?

=IF(OR(A3="NO",C3="NO",D3="NO"),0,((A3="YES")+(C3="YES")+(D3="YES")+(D3="PARTIAL")*0.5)/3)

Try this:

=IFNA(IF(OR(A3="no",D3="no",C3="no"),0,(A3="YES")+(C3="YES")+(D3="YES")+(D3="PARTIAL")*0.5)/3,"")

Wrag the formula with IFERROR
=IFFERROR(Formula,"")

I'm sorry but no. N/A is still affecting the overall score.

Is there a way to add "IF(OR(A3="yes",A3="no"),100,0)" in the formula(also for C3 and D3?

@Quang - please stop quoting unnecessarily! Thanks.

@Serpan75

Please provide a fresh workbook showing the issue.

Try this:

=COUNTIFS(Table1[#Headers],"Test*",$A3:$Z3,"Yes")/COUNTIFS(Table1[#Headers],"Test*",$A3:$Z3,"<>N/A")

OK, then this:

=IF(COUNTIFS(Table1[#Headers],"Test*",$A3:$Z3,"No")>0,0,COUNTIFS(Table1[#Headers],"Test*",$A3:$Z3,"Yes")/COUNTIFS(Table1[#Headers],"Test*",$A3:$Z3,"<>N/A"))

One acronym: LOL!!!

Glad to have got you there.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.