Scoring weighted permutations

[dmj120]

I am spinning.... I'm trying to create a "confidence score" based upon several factors: dm lbr, dm parts, sm lbr, sm parts are either 0 or >0, and mixed in this are 'conditions' is a PM required, are PM parts needed, and finally are the dm lbr and sm lbr hours (from another workbook) yes or no, respectively.

Any advise?? By "spinning" I mean I have made several different attempts....below is where I'm currently at.

I found a niffty permutations formula to start with all combinations:
LET(A,B4:I5,B,ROWS(A),C,COLUMNS(A),D,B^C,E,UNIQUE(MAKEARRAY(D,C,LAMBDA(rw,cl,INDEX(IF(A="","",A),MOD(CEILING(rw/(D/(B^cl)),1)-1,B)+1,cl)))),FILTER(E,MMULT(--(E<>""),SEQUENCE(C,,,0))=C))

Generate Permutations.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2		FSWP						device/mdl compr
3		dm lbr	sm lbr	dm prts	sm parts	PM Req'd	PM prts	DM Lbr	SM Lbr
4		>0	>0	>0	>0	no	no	no	no
5		0	0	0	0	yes	yes	yes	yes
6
7		Multipliers
8		4	2	3	1	if 'no' 3*0.5	if 'no' 1*0.5	if 'no' 4*0.5	if 'no' 4*0.5
9
10		X6	Y6	Z6	AA6	D6	E6	BF6	BG6		O6
11		dm lbr	dm prts	sm lbr	sm parts	PM Req'd	PM prts needed	DM Lbr between dvc hi/lo	SM Lbr between dvc hi/lo	score	confidence	hmmm	dm lbr	dm prts	sm lbr	sm parts	Weighted score
12		>0	>0	>0	>0	no	no	no	no	16	yes	16	16	8	12	4	48
13		>0	>0	>0	>0	no	no	no	yes	16	yes	16	16	8	12	4	48
14		>0	>0	>0	>0	no	no	yes	no	16	yes	16	16	8	12	4	48
15		>0	>0	>0	>0	no	no	yes	yes	16	yes	16	16	8	12	4	48
16		>0	>0	>0	>0	no	yes	no	no	14	yes	14	16	8	12	4	48
17		>0	>0	>0	>0	no	yes	no	yes	14	yes	14	16	8	12	4	48
18		>0	>0	>0	>0	no	yes	yes	no	14	yes	14	16	8	12	4	48
19		>0	>0	>0	>0	no	yes	yes	yes	14	yes	14	16	8	12	4	48
20		>0	>0	>0	>0	yes	no	no	no	14	yes	14	16	8	12	4	48
21		>0	>0	>0	>0	yes	no	no	yes	14	yes	14	16	8	12	4	48
22		>0	>0	>0	>0	yes	no	yes	no	14	yes	14	16	8	12	4	48
23		>0	>0	>0	>0	yes	no	yes	yes	14	yes	14	16	8	12	4	48
24		>0	>0	>0	>0	yes	yes	no	no	14	yes	14	16	8	12	4	48
25		>0	>0	>0	>0	yes	yes	no	yes	14	yes	14	16	8	12	4	48
26		>0	>0	>0	>0	yes	yes	yes	no	14	yes	14	16	8	12	4	48
27		>0	>0	>0	>0	yes	yes	yes	yes	14	yes	14	16	8	12	4	48
28		>0	>0	>0	0	no	no	no	no	16	yes	16	16	8	12	-2	48
FSWP

Cell Formulas
Range	Formula
J12:J28	J12	=IF(AND(F12="no",G12="no"),SWITCH(B12,">0",4,-12)+SWITCH(C12,">0",4,-4)+SWITCH(D12,">0",4,4)+SWITCH(E12,">0",4,4), IF(AND(F12="yes",G12="yes"),SWITCH(B12,">0",4,-4)+SWITCH(C12,">0",4,-4)+SWITCH(D12,">0",4,0)+SWITCH(E12,">0",2,0), IF(AND(F12="yes",G12="no"),SWITCH(B12,">0",4,-4)+SWITCH(C12,">0",4,-4)+SWITCH(D12,">0",4,0)+SWITCH(E12,">0",2,2), IF(AND(F12="no",G12="yes"),SWITCH(B12,">0",4,-4)+SWITCH(C12,">0",4,-4)+SWITCH(D12,">0",4,4)+SWITCH(A12,">0",2,2) ))))
K12:K28	K12	=IF(J12>13,"yes",IF(J12<5,"no","partial"))
L12:L28	L12	=IF(AND(F12="no",G12="no"),SWITCH(B12,">0",4,-12)+SWITCH(C12,">0",4,-4)+SWITCH(D12,">0",4,4)+SWITCH(E12,">0",4,4), IF(AND(F12="yes",G12="yes"),SWITCH(B12,">0",4,-4)+SWITCH(C12,">0",4,-4)+SWITCH(D12,">0",4,0)+SWITCH(E12,">0",2,0), IF(AND(F12="yes",G12="no"),SWITCH(B12,">0",4,-4)+SWITCH(C12,">0",4,-4)+SWITCH(D12,">0",4,0)+SWITCH(E12,">0",2,2), IF(AND(F12="no",G12="yes"),SWITCH(B12,">0",4,-4)+SWITCH(C12,">0",4,-4)+SWITCH(D12,">0",4,4)+SWITCH(A12,">0",2,2) ))))
M12:M28	M12	=SWITCH(B12,">0",(1*4)*$B$8,(0.5*-12)*$B$8)
N12:N28	N12	=SWITCH(C12,">0",(1*4)*$C$8,(0.5*-4)*$C$8)
O12:O28	O12	=SWITCH(D12,">0",(1*4)*$D$8,(0.5*-4)*$D$8)
P12:P28	P12	=SWITCH(E12,">0",(1*4)*$E$8,(0.5*-4)*$E$8)
Q12:Q28	Q12	=SUM(SWITCH(B12,">0",(1*4)*$B$8,(0.5*-12)*$B$8),SWITCH(C12,">0",(1*4)*$C$8,(0.5*-4)*$C$8),SWITCH(D12,">0",(1*4)*$D$8,(0.5*-4)*$D$8),SWITCH(D12,">0",(1*4)*$D$8,(0.5*-4)*$D$8))

 

[StephenCrump]

You haven't said what your question is?

But given your heading refers to weighted scores, I am guessing that you are wondering why the results in column Q are as they are?

First observation is that Q12 has: =SUM(SWITCH(B12,">0",(1*4)*$B$8,(0.5*-12)*$B$8),SWITCH(C12,">0",(1*4)*$C$8,(0.5*-4)*$C$8),SWITCH(D12,">0",(1*4)*$D$8,(0.5*-4)*$D$8),SWITCH(D12,">0",(1*4)*$D$8,(0.5*-4)*$D$8))

You have the sum of four terms, the last two of which are duplicates. Presumably the last term should refer to column E, not D, with weight 1, rather than 3?

But let's go with the incorrect formulae just to illustrate how weighting works. All the rows you've shown have the same weighted score (which is 4, not 48) so I have changed Row 13 to illustrate better.

	FSWP						device/mdl compr
	dm lbr	sm lbr	dm prts	sm parts	PM Req'd	PM prts	DM Lbr	SM Lbr
	>0	>0	>0	>0	no	no	no	no
	0	0	0	0	yes	yes	yes	yes
	Multipliers
	4	2	3	1	if 'no' 3*0.5	if 'no' 1*0.5	if 'no' 4*0.5	if 'no' 4*0.5
	X6	Y6	Z6	AA6	D6	E6	BF6	BG6		O6
	dm lbr	dm prts	sm lbr	sm parts	PM Req'd	PM prts needed	DM Lbr between dvc hi/lo	SM Lbr between dvc hi/lo	score	confidence	hmmm	dm lbr	dm prts	sm lbr	sm parts	Weighted score
	>0	>0	>0	>0	no	no	no	no	16	yes	16	16	8	12	4	48
	0	0	>0	>0	no	no	no	yes	-8	no	-8	-24	-4	12	4	-4

In Row 12, each of the four score components are equal to 4, therefore the weighted score will be 4, regardless of the weights.
In Row 13 (as modified) the four score components are equal to -6, -2, 4 and 4.

For a weighted score, you need to divide the calculation by the sum of the weights, like this:

Arrests.xlsx
	A	B	C	D	E	F
1		Score 1	Score 2	Score 3	Score 4	Weighted score
2	Weight	4	2	3	3
3	Row 12 scores	4	4	4	4	4.00
4	Row 13 scores	-6	-2	4	4	-0.33
Sheet2

Cell Formulas
Range	Formula
F3:F4	F3	=SUMPRODUCT(B$2:E$2,B3:E3)/SUM(B$2:E$2)