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
1	Yes	Yes	Yes	Yes	100.00%
2	Yes	No	Yes	Yes	75.00%
3	Yes	Yes	N/A	No	66.67%

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet427​

Worksheet Formulas Cell Formula E1 =COUNTIF(A1:D1,"Yes")/(4-COUNTIF(A1:D1,"N/A")) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Thank you. This works perfectly!

 

[jtakw]

You're welcome, welcome to the forum.