Formula

[Evie76]

I have 2 formulas that do what i need them to do separately

=IF(COUNTIF(C3:C20, "Critical") = COUNTA(C3:C20), 0, IF(OR(COUNTIF(C3:C20, "N/A") > 0, COUNTIF(C3:C20, "") > 0), 0.79, 0.79 - (COUNTIF(C3:C20, "Critical") - 1) * 0.0465))

=IF(COUNTIF(C3:C20, "Critical") = COUNTA(C3:C20), 0, IF(OR(COUNTIF(C3:C20, "N/A") > 0, COUNTIF(C3:C20, "") > 0), 0.79, 1 - (COUNTIF(C3:C20, "No") / 18) * 0.2))

I've tried multiple ways of trying to put them together but it is just not working

I have a score sheet with 18 questions with answers yes, no, critical, and N/A answers.

If all answers are yes then the percentage needs to be 100%
if all the answers are no it needs to show 80%,
N/A no change in the percentage.
If all are answered as critical the percentage is 0%.
If only one question is answered as critical then the percentage drops to 79% and keeps dropping for every critical answer there is with the last one reaching 0%

can anyone help

 

[awoohaw]

Thank you so much for all your help, the first one works perfectly.

My pleasure, this was one of the more enjoyable excel challenges I have had in a while. I thought my formula in post #6 was pretty nifty, but it didn't suit your needs.

And regarding the last two, I thought the last one would have been the best solution. The one in post #18 doesn't give any credit for the first yes, I think.
(please test all your scenarios.)

Regardless, I am very pleased to have helped you find a solution here.

Best Wishes!

 

[Evie76]

I have another challenge if you are up for it.
Now that the I’m able to calculate the percentage (with the formula you gave me) for each call assessed call which there are 6 of them. I need a formula to work out the following.
if all 6 calls are at 100% = 100%
If all 6 calls are between 80% and 99% = 80%
if 5 calls are between 80% and 99% and 1 call at 100% = 84%
if 4 calls are between 80% and 99% and 2 call at 100% = 88%
if 3 calls are between 80% and 99% and 3 call at 100% = 92%
if 2 calls are between 80% and 99% and 4 call at 100% = 95%
if 1 call is between 80% and 99% and 5 call at 100% = 99%
and
If all 6 calls are between 79% and 0% = 0%
If 5 calls are between 79% and 0% = 15%
If 5 calls are between 79% and 0% = 30%
If 5 calls are between 79% and 0% = 45%
If 5 calls are between 79% and 0% = 60%
If 5 calls are between 79% and 0% = 79%

the percentages are on C21:H21

I hope it makes sense, it's early hours of the morning and I have been trying to work it out since I got the other formula, so I hope it makes sense.

 

[awoohaw]

How do you determine the % to use in the last 5 of the 2nd group? Is it supposed to be 5, 4, 3, 2, 1?

 

[awoohaw]

Does this look like what you want:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		Call 1	Call 2	Call 3	Call 4	Call 5	Call 6					Hi	Med	Lo
2	Scenario 1	100.00%	100.00%	100.00%	100.00%	100.00%	100.00%		100.00%			6	0	0		100.00%
3	Scenario 2	86.00%	87.00%	88.00%	89.00%	90.00%	91.00%		80.00%			0	6	0		80.00%
4	Scenario 3	100.00%	86.00%	87.00%	88.00%	89.00%	90.00%		84.00%			1	5	0		84.00%
5	Scenario 4	100.00%	100.00%	86.00%	87.00%	88.00%	89.00%		88.00%			2	4	0		88.00%
6	Scenario 5	100.00%	100.00%	100.00%	86.00%	87.00%	88.00%		92.00%			3	3	0		92.00%
7	Scenario 6	100.00%	100.00%	100.00%	100.00%	86.00%	87.00%		95.00%			4	2	0		95.00%
8	Scenario 7	100.00%	100.00%	100.00%	100.00%	100.00%	89.00%		99.00%			5	1	0		99.00%
9	Scenario 8	18.72%	53.88%	30.64%	38.81%	73.84%	23.34%		0.00%			0	0	6		0.00%
10	Scenario 9	10.19%	44.18%	39.36%	54.44%	14.51%	81.00%		15.00%			0	1	5		15.00%
11	Scenario 10	0.72%	27.50%	53.73%	71.80%	82.00%	95.00%		30.00%			0	2	4		30.00%
12	Scenario 11	7.84%	51.97%	27.41%	84.00%	93.00%	95.00%		45.00%			0	3	3		45.00%
13	Scenario 12	16.45%	51.52%	83.00%	89.00%	98.00%	100.00%		60.00%			1	3	2		60.00%
14	Scenario 13	2.47%	89.00%	88.00%	92.00%	96.00%	84.00%		79.00%			0	5	1		79.00%
Sheet12

Cell Formulas
Range	Formula
L2	L2	=LET( calls,$B2:$G2, CountHi,COUNTIF(calls,1), CountMed,COUNTIFS(calls,"<"&1,calls,">="&0.8), CountLo,COUNTIF(calls,"<"&0.8), CountHi)
M2	M2	=LET( calls,$B2:$G2, CountHi,COUNTIF(calls,1), CountMed,COUNTIFS(calls,"<"&1,calls,">="&0.8), CountLo,COUNTIF(calls,"<"&0.8), CountMed)
N2:N14	N2	=LET( calls,$B2:$G2, CountHi,COUNTIF(calls,1), CountMed,COUNTIFS(calls,"<"&1,calls,">="&0.8), CountLo,COUNTIF(calls,"<"&0.8), CountLo)
L3:L14	L3	=LET( calls,$B3:$G3, CountHi,COUNTIF(calls,1), CountMed,COUNTIFS(calls,"<"&1,calls,">="&0.8), CountLo,COUNTIF(calls,""<0.8), CountHi)
M3:M14	M3	=LET( calls,$B3:$G3, CountHi,COUNTIF(calls,1), CountMed,COUNTIFS(calls,"<"&1,calls,">="&0.8), CountLo,COUNTIF(calls,""<0.8), CountMed)
P2:P14	P2	=LET( calls,$B2:$G2, CountHi,COUNTIF(calls,1), CountMed,COUNTIFS(calls,"<"&1,calls,">="&0.8), CountLo,COUNTIF(calls,"<"&0.8), Score, IF(CountLo>0,ROUND(0.79-((CountLo-1)*0.15)+(IF(CountLo<>1,-0.04,0)),4), IF(CountMed>0,0.8+(CountHi*0.04)+IF(CountHi>3,-0.01,0), 1)), Score)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:G14	Expression	=B2<0.8	text	NO
B2:G14	Expression	=AND(B2>=0.8,B2<1)	text	NO
B2:G14	Expression	=B2=1	text	NO

 

[Evie76]

This is perfect, thank you so much

 

[awoohaw]

My pleasure.