Weighted scores question

[craigo298er]

I have an assessment that I've made to determine knowledge gaps with my team and I want to use it to determine developmental classes. What I have are questions with Beginner, Intermediate, Advanced, and Expert level answers; not all questions have each level. I would like to see a score by answer level (Employee 1 answered question 1 with 50% beginner, 33% intermediate, 0% advanced), but also show incorrect answers. I tried playing around with weighted scores and putting in 1 or -1 for a total score, but can't really figure out how to do the % by level.

In question 1, A,B,D were the answers selected by the employee, but the correct answers under the level headers.

Assessment Results test2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
2	Question	Employee Answers	a	b	c	d	e	f	g	h	Beginner	Intermediate	Advanced	Expert
3	1	A,B,D									A, E	A, E, F	A,B,E,F	A,B,C,E,F
4	2										B,D,E	B,C,D,E,G	B,C,D,E,G	A,B,C,D,E,F,G
5	3										D	-	-	-
6	4										A, E	A,C	A,C,F
7	5										E	-	-	-
8	6										B, F	B, F, H	B, F, G, H	B, E,F,G,H
9	7										D	-	-	-
10	8										G	G,B	G,B,A	-
11	9										E,F,G	B,C,E,F,G	A,B,C,D,E,F,G	-
12	10										C,D	C,D,F	B,C,D,F	-
13	11										A	A,C,D	A,B,C,D	-
14	12										E,G	E,F,G	A,E,F,G	-
15	13										B,E	B,E,G	B,E,G,H	-
16	14										F	B,F	B,F,C	-
17	15										B,F	-	-	-
18	16										F	B,F	-	-
19	17										D	D,F	C,D,F	-
20	18										D	D,F	C,D,F	-
21	19										A	A,C	A,B,C	A,B,C,F
22	20										E	-	E,B
Assessment grade