Auto Scoring Matrix

[nparsons75]

Hi, I need to create a score matrix to help grade people by score. So for example we have a number of disciplines which I will call machines. We also categorise these machines by difficulty to learn. The idea being, the more points you score, the more pay you will earn. How can this be recreated in excel to provide an automatic scoring.

Machines, we have...

Machine / Discipline	Difficulty Band
Machine 1	A
Machine 2	B
Machine 3	B
Machine 4	B
Machine 5	A
Machine 6	C
Machine 7	B
Machine 8	B

The scorecard matrix will be like this.

	A	B	C
1	1	2	3
2	4	5	6
3	7	8	9

The scoring will work as follows.....

Person 1 = 1 x B = 2 (pay grade 2)
Person 2 = 1 x A & 1 x B = 3 (Pay grade 3)
Person 3 = 2 x C = 6 (Pay grade 6)

Note: The max number of machines they can use is 3 and the max skill is C. The lowest score or pay grade = 1 and the highest score / pay grade is 9.

I need to create this in excel where I have a table containing peoples names down the left, across the top I have the machine name. Something like the below...

	A	B	B	C	C
	Machine 1	Machine 2	Machine 3	Machine 4	Machine 5	Score
Dave	1					1
Peter		1		1		5
Susan	1				1	4

The table above is how I would like to score, I would like to populate the names and enter a 1 in each machine they can use, the score will be in the far right column.

Hopefully someone understands what I am trying to say.....

Thanks in advance....

 

[Eric W]

If I understand correctly:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	A	B	C				A	B	B	C	C
2	1	2	3				Machine 1	Machine 2	Machine 3	Machine 4	Machine 5	Score
3	4	5	6			Dave	1					1
4	7	8	9			Peter		1		1		5
5						Susan	1				1	4
Sheet4

Cell Formulas
Range	Formula
L3:L5	L3	=SUM(INDEX($A$1:$C$4,MIN(COUNTIFS($G$1:$K$1,"A",G3:K3,1),3)+1,1),INDEX($A$1:$C$4,MIN(COUNTIFS($G$1:$K$1,"B",G3:K3,1),3)+1,2),INDEX($A$1:$C$4,MIN(COUNTIFS($G$1:$K$1,"C",G3:K3,1),3)+1,3))

I'd think there should be something easier, but it's late, maybe I'll try again tomorrow.

 

[nparsons75]

If I understand correctly:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	A	B	C				A	B	B	C	C
2	1	2	3				Machine 1	Machine 2	Machine 3	Machine 4	Machine 5	Score
3	4	5	6			Dave	1					1
4	7	8	9			Peter		1		1		5
5						Susan	1				1	4
Sheet4

Cell Formulas
Range	Formula
L3:L5	L3	=SUM(INDEX($A$1:$C$4,MIN(COUNTIFS($G$1:$K$1,"A",G3:K3,1),3)+1,1),INDEX($A$1:$C$4,MIN(COUNTIFS($G$1:$K$1,"B",G3:K3,1),3)+1,2),INDEX($A$1:$C$4,MIN(COUNTIFS($G$1:$K$1,"C",G3:K3,1),3)+1,3))

I'd think there should be something easier, but it's late, maybe I'll try again tomorrow.

Brilliant thank Eric. From what I can see it works perfectly.... Il run through fully tomorrow and set it up as needed to give it a full test. Thank you so much for your time

 

[nparsons75]

Brilliant thank Eric. From what I can see it works perfectly.... Il run through fully tomorrow and set it up as needed to give it a full test. Thank you so much for your time

Hi Eric, can you please confirm what this part of the formula does, I seem to have an issue where if there are multiple selections of 'B' it does not score all of them...

"A",G3:K3,1),3)+1,1)

 

[Eric W]

Can you provide an example where the formula doesn't work, and an explanation of how it should be calculated? I was not sure that I got the algorithm right the first time. I got the answer you did, but I wasn't sure it made sense. Please check that the results of your first example are correct too.