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

 

[Evie76]

Thank you so much for helping me

 

[awoohaw]

Here is what I have come up with:

Book1
	A	B	C
1			52.67%
2
3			N/A
4			N/A
5			No
6			Yes
7			Critical
8			Critical
9			N/A
10			No
11			Critical
12			Yes
13			Critical
14			Yes
15			No
16			No
17			Yes
18			Yes
19			No
20			No
Sheet3

Cell Formulas
Range	Formula
C1	C1	=LET( Entries,$C$3:$C$20, CountYES,COUNTIF(Entries,"Yes"), CountCrit,COUNTIF(Entries,"Critical"), CountNO,COUNTIF(Entries,"No"), IF(CountCrit > 0,0.79 - (0.79/(CountCrit-1)), IF(CountN0>0,0.8+(0.2/CountYES),1)))

 

[Evie76]

Hi, i tried that but comes up with #Name?

 

[awoohaw]

Hi, i tried that but comes up with #Name?

yeah, I don't know why. But I think I am on the right track. Can you look at the logic and tell me if I am. I do have a potential DIV/0 error in there too.

 

[Fluff]

yeah, I don't know why

Have a look at the countno

 

[Evie76]

from what i can tell the logic seems fine

 

[awoohaw]

Have a look at the countno

zero vs oh oh
Thanks @Fluff,
but I still have a DIV/0 error that I need to work around.

 

[awoohaw]

OKay @Evie76 ,
The only thing that could be wrong is if there is 1 No and All Yesses. If that is wrong then the same adjustment check needs to be done to the No Condition Check

I think this gets it:

Book1
	A	B	C	D	E
1
2
3			Yes	81.11%
4			no
5			Yes
6			no
7			no
8			no
9			no
10			no
11			no
12			no
13			no
14			no
15			no
16			no
17			no
18			no
19			no
20			no
Sheet3

Cell Formulas
Range	Formula
D3	D3	=LET( Entries,$C$3:$C$20, CountEntries,COUNTA($C$3:$C$20), CountYES,COUNTIF(Entries,"Yes"), CountCritical,COUNTIF(Entries,"Critical"), CountNo,COUNTIF(Entries,"No"), CritAdj, 0.79 * IF(CountCritical>1,(CountCritical-1)/(CountEntries-1),0), IF(CountCritical > 0,0.79 - CritAdj, IF(CountNo>0,0.8+(IF(CountYES>1,0.2*((CountYES-1)/CountEntries))),1)))

 

[awoohaw]

if the formula above doesn't work for the Yes/No, then try this one:

=LET(
Entries,$C$3:$C$20,
CountEntries,COUNTA($C$3:$C$20),
CountYES,COUNTIF(Entries,"Yes"),
CountCritical,COUNTIF(Entries,"Critical"),
CountNo,COUNTIF(Entries,"No"),
CritAdj, 0.79 * IF(CountCritical>1,(CountCritical-1)/(CountEntries-1),0),
IF(CountCritical > 0,0.79 - CritAdj,
IF(CountNo>0,0.8+(0.2*(CountYES/CountEntries)),
1)))

 

[Evie76]

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