Excel Formula

[jleasure457]

I'm trying to create a spreadsheet that takes multiple, "Yes", "No", and "N/A" responses and translates them into overall percentage. There are 4 columns, all of which will have a Yes, No, or N/A option as a drop down. I want the spreadsheet to be able to read the these 4 fields and give a percentage. For example is all 4 columns are "Yes", it would be 100%. If 3 are "Yes" and 1 is "No", it would be 75%. If any of the columns are N/A, it doesn't take that column into consideration when calculating the percentage. So, if Column 1 and 2 are "Yes", Column 3 is "No", and Column 4 is "N/A". It would be 66.66%.

Thanks in advance for your help.

 

[jtakw]

Hi,

Format result cells as "percentage":

Book1
	A	B	C	D	E
1	Yes	Yes	Yes	Yes	100.00%
2	Yes	No	Yes	Yes	75.00%
3	Yes	Yes	N/A	No	66.67%
Sheet427

Cell Formulas
Range	Formula
E1	=COUNTIF(A1:D1,"Yes")/(4-COUNTIF(A1:D1,"N/A"))

 

[Michael M]

try

=(COUNTIF(A1:D1,"no")+COUNTIF(A1:D1,"yes"))/COUNTA(A1:D1)

 

[jleasure457]

Hi,

Format result cells as "percentage":

	A	B	C	D	E
Yes	Yes	Yes	Yes
Yes	No	Yes	Yes
Yes	Yes	N/A	No

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]100.00%[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]75.00%[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]66.67%[/TD]

</tbody>

Sheet427​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E1[/TH]
[TD="align: left"]=COUNTIF(A1:D1,"Yes")/(4-COUNTIF(A1:D1,"N/A"))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you. This works perfectly!

 

[jtakw]

You're welcome, welcome to the forum.