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]

What is the percentage prior to seeing a N/A, what do you mean by "no change in the percentage"?
What if there is an N/A and at least one critical? Does the Percentage stay at 79%? Or if 17 Critical and 1 N/A, it is 79% * ((18-17)/18).
You also have "" as values in the formula, do you want those included in something?

 

[Evie76]

If there is 1 critical and 17 n/a's the percentage would stay at 79%. If there were 17 critical and 1 N/A then the percentage would be 5%. every time there is a critical the percentage would keep dropping

 

[awoohaw]

I understand about the percentage continuing to drop for Critical, but I don't understand what to do with the N/A and the "" records? Are you saying that if there is an
N/A or a "" that it does nothing to affect the final total?

 

[Evie76]

Sorry, yeah exactly that

 

[awoohaw]

what about if there are mixed Yeses and Nos, with no Critical?
Does each yes get 100/18, and each no 80/18?

And what about mixed critical with yes and no? is each critical 79/18?

is this what you want (Cell F3):

Book1
	A	B	C	D	E	F
1
2
3			N/A	0		54.62
4			N/A	0
5			No	4.444444444
6			Yes	5.555555556
7			Critical	0.043888889
8			Critical	0.043888889
9			N/A	0
10			No	4.444444444
11			Critical	0.043888889
12			Yes	5.555555556
13			Critical	0.043888889
14			Yes	5.555555556
15			No	4.444444444
16			No	4.444444444
17			Yes	5.555555556
18			Yes	5.555555556
19			No	4.444444444
20			No	4.444444444
21				54.62
Sheet3

Cell Formulas
Range	Formula
F3	F3	=LET(IndEntry,$C3,Entry,$C$3:$C$20,EntryCount,COUNTA(Entry),Txt,TEXTSPLIT(TEXTJOIN(",",,"Yes","No","Critical","N/A"),","),Points,TEXTSPLIT(TEXTJOIN(",",,100/EntryCount,80/EntryCount,0.79/EntryCount,0/EntryCount),","),SUM((Entry=Txt)*Points))
D3:D20	D3	=LET(IndEntry,$C3,Entry,$C$3:$C$20,EntryCount,COUNTA(Entry),Txt,TEXTSPLIT(TEXTJOIN(",",,"Yes","No","Critical","N/A"),","),Points,TEXTSPLIT(TEXTJOIN(",",,100/EntryCount,80/EntryCount,0.79/EntryCount,0/EntryCount),","),entryscore,XLOOKUP(IndEntry,Txt,Points),entryscore*1)
D21	D21	=SUM(D3:D20)

 

[Evie76]

Sorry if i'm not being very forthcoming, maybe it would help it i told you it all. I have to mark compliance officer calls. there are 18 questions on the sheet. if one question is yes and the rest are no's then the score would be 80%, if there are 2 yeses and 16 no's the score would go up if all are yes then it would be 100%. If there is 1 no and 17 yeses the score would be 99%ish and will go down each time there is a no, if all are no then it needs to only go to 80% (as this is still a passed call and the pass rate is 80%). Criticals are what we call fails, which is 79% and below. the yes and no don't need to be taken into account if there is a critical

 

[awoohaw]

So there is a priority of what the answer is.
1. If all yesses, then 100%
2. If any number of critical then 79%, and decrease by 79%/17 with each additional critical? (what if there are Yeses and Nos... i'm reading they don't matter even if only one critical?).
3. If at least one No and zero criticals then start at 80% and increase by 20%/18 for each Yes.
4. N/A and "" don't mean anything.

 

[Evie76]

yeah thats it
The yeses and no dont matter even if there is only 1 critical as its still a failed call.

Its been a very long week, so i apologise for not explaining properly

 

[awoohaw]

Okay, am i doing the division right for the additional criticals and yeses (17 and 18)?
And the formula I'm thinking of will by dynamic if the total number of responses changes, I THINK.