Weighted scores with some weightings the full 100%

[Ghost141]

Hello!

Im trying to work out a formula to calculate weightings based on scores, with some metrics being critical and worth the full 100%.

Essentially, the metrics worth 100% would create an immediate fail, where as the ones worth 20% would have to have 5 failings for 100%, otherwise 2 = 40% ETC.

Book1
	A	B	C
1	Metric	Failings	Weighting
2	a	0	20%
3	b	0	20%
4	c	0	20%
5	d	0	20%
6	e	0	100%
7	f	0	20%
8	g	5	20%
9	h	0	20%
10	i		20%
11	j		20%
12	k	0	100%
13	l	0	100%
14	m	0	20%
15	n	0	20%
Sheet1

Thanks heaps!

 

[etaf]

not following
are you trying to put a formula in column C
based on the value in column B
OR column B based on C
really not following sorry

can you provide a sample = with ALL expected results and some notes to say how arrived at

IN C
=B2/5

Book2
	A	B	C
1	metric	fails	weighting
2		1	20%
3		0	0%
4		2	40%
5		3	60%
6		4	80%
7		5	100%
8		1	20%
9		0	0%
Sheet2

Cell Formulas
Range	Formula
C2:C9	C2	=B2/5

 

[Ghost141]

Sorry the formula would be in cell B16, this would be the score based on the number of errors found * their weighting, but keeping 100% the max, so even if they had 3 failings worth 20% each, and 2 failings worth 100% each, the total score would be 100% (fail).

 

[etaf]

=MIN(SUM(B2:B9)/5,1)

Book2
	A	B	C
1	metric	fails	weighting
2		1	20%
3		0	0%
4		1	20%
5		2	40%
6		0	0%
7		0	0%
8		0	0%
9		0	0%
10
11		4	80%
Sheet2

Cell Formulas
Range	Formula
C2:C9	C2	=B2/5
B11	B11	=SUM(B2:B9)
C11	C11	=MIN(SUM(B2:B9)/5,1)

Book2
	A	B	C
1	metric	fails	weighting
2		1	20%
3		0	0%
4		1	20%
5		2	40%
6		5	100%
7		5	100%
8		5	100%
9		5	100%
10
11		24	100%
Sheet2

Cell Formulas
Range	Formula
C2:C9	C2	=B2/5
B11	B11	=SUM(B2:B9)
C11	C11	=MIN(SUM(B2:B9)/5,1)

 

[Ghost141]

thats great but only works if all the metrics are worth 20%. Heres some examples of the outcomes expected:
Please note the occasions where some metrics are blank as they may be not applicable (unsure if thats going to influence the formula)

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Metric	Failings	Weighting	Metric	Failings	Weighting	Metric	Failings	Weighting	Metric	Failings	Weighting
2	a	0	20%	a	0	20%	a	0	20%	a	0	20%
3	b	0	20%	b	0	20%	b		20%	b		20%
4	c	0	20%	c	2	20%	c	0	20%	c		20%
5	d	0	20%	d	0	20%	d	1	20%	d		20%
6	e	1	100%	e	0	100%	e	0	100%	e	0	100%
7	f	0	20%	f	0	20%	f	0	20%	f	0	20%
8	g	0	20%	g	0	20%	g		20%	g	0	20%
9	h	0	20%	h	1	20%	h	0	20%	h	0	20%
10	i		20%	i	0	20%	i	1	20%	i	0	20%
11	j		20%	j	0	20%	j	4	20%	j	0	20%
12	k	0	100%	k	0	100%	k	1	100%	k	0	100%
13	l	0	100%	l	0	100%	l	0	100%	l	0	100%
14	m	0	20%	m	0	20%	m	0	20%	m	0	20%
15	n	0	20%	n	0	20%	n	0	20%	n	1	20%
16	Score	100%	1 failing worth 100%	Score	60%	3 failings worth 20% each	Score	100%	5 failings worth 20% each and 1 failing worth 100%, but max score is 100%)	Score	20%	1 failing worth 20%
Sheet1

 

[etaf]

how about
=MIN(SUMPRODUCT(B2:B15,C2:C15),1)

Book2
	A	B	C	D	E	F	G	H	I	J	K	L
1	Metric	Failings	Weighting	Metric	Failings	Weighting	Metric	Failings	Weighting	Metric	Failings	Weighting
2	a	0	0.2	a	0	0.2	a	0	0.2	a	0	0.2
3	b	0	0.2	b	0	0.2	b		0.2	b		0.2
4	c	0	0.2	c	2	0.2	c	0	0.2	c		0.2
5	d	0	0.2	d	0	0.2	d	1	0.2	d		0.2
6	e	1	1	e	0	1	e	0	1	e	0	1
7	f	0	0.2	f	0	0.2	f	0	0.2	f	0	0.2
8	g	0	0.2	g	0	0.2	g		0.2	g	0	0.2
9	h	0	0.2	h	1	0.2	h	0	0.2	h	0	0.2
10	i		0.2	i	0	0.2	i	1	0.2	i	0	0.2
11	j		0.2	j	0	0.2	j	4	0.2	j	0	0.2
12	k	0	1	k	0	1	k	1	1	k	0	1
13	l	0	1	l	0	1	l	0	1	l	0	1
14	m	0	0.2	m	0	0.2	m	0	0.2	m	0	0.2
15	n	0	0.2	n	0	0.2	n	0	0.2	n	1	0.2
16	Score	1	1 failing worth 100%	Score	0.6	3 failings worth 20% each	Score	1	5 failings worth 20% each and 1 failing worth 100%, but max score is 100%)	Score	0.2	1 failing worth 20%
17
18
19		100%			60%			100%			20%
Sheet3

Cell Formulas
Range	Formula
B19,K19,H19,E19	B19	=MIN(SUMPRODUCT(B2:B15,C2:C15),1)

 

[Ghost141]

AMAZING! Thanks so much!

 

[etaf]

you are welcome