Forum Discussion
Weighted scores based on text answers formula
Hi Everyone!
I have a site inspection template, whereby there are 44 questions. This is a typical scenario the same as when you take an exam, where the overall score is given based on answers.
However, with each answer being able to achieve 5 different levels of compliance rather than just pass or fail, and those answers to each question are given as a text result, not a number.
Each question can have an answer of
- Fully compliant
- Minor non compliance
- Partial Compliance
- Minimal Compliance
- No Compliance
I need each answer to give a result of full marks for that question, 3 quarters, half, quarter or zero.
Then at the end it needs to sum a total percentage of pass rate.
I think i need a v look up for explaining what value each answer has? And then a simple sum in the percentage box but can't figure out the vlookup formula!
Any help greatly received.
2 Replies
It's not clear to me what you want, exactly, but hopefully this will get you on your way.
Enter the following formula in G1:
=IFERROR(VLOOKUP(E1,'Sections '!$A$4:$B$8,2,FALSE),"")
Fill down to G143 (or perhaps G162).
In (for example) G164, enter the formula
=SUM(G1:G162)
This is the total score.
If you need more help, please provide a more detailed explanation of what you want.
Can't say I understood what you'd like to calculate, better if you illustrated that by manually added desired result - for people who has no idea about exam scores, total percent's, etc.
As a comment:
- data validation list better to generate on the same data you use for weights. Typing list manually is the cause of possible errors.
- to take weight for some answer
=INDEX('Sections '!$B$4:$B$8, MATCH(E15,'Sections '!$A$4:$A$8,0))to calculate the sum of weights for the section
=SUMPRODUCT(COUNTIF(E15:E18,'Sections '!$A$4:$A$8)*'Sections '!$B$4:$B$8)Above formulas are for this section
