How to Calculate a Yes and No Response Percentage Excluding N/A in WPS Office Spreadsheets
Question details
The user needs to calculate the percentage of Yes responses in a survey column while excluding N/A responses so that only valid Yes and No answers are counted.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user is analyzing survey response data in a column containing Yes, No, and N/A values and wants to show what proportion of valid Yes/No responses are Yes.
- Observed behavior
- The desired result is a percentage that ignores N/A responses and divides the number of Yes values by the total number of non-N/A responses.
Open the workbook in WPS Office Spreadsheets and identify the exact range that contains the Yes, No, and N/A responses, such as B2:B17.
Calculate the Yes percentage by dividing the Yes count by the non-N/A response count
Use this method when your response range contains Yes, No, and N/A values and you want to ignore N/A entries in the percentage calculation.
The formula uses COUNTIF to count Yes responses in the numerator and the total Yes plus No responses in the denominator. Because N/A is excluded from the denominator, it does not affect the Yes percentage.
Click the empty cell where you want the percentage to appear, such as C2.
Type =COUNTIF(B2:B17,"Yes")/(COUNTIF(B2:B17,"Yes")+COUNTIF(B2:B17,"No")) and press Enter.
With the result cell selected, go to the Home tab, open the Number Format drop-down menu, and choose Percentage, or press Ctrl+Shift+% to apply the percentage format.
Use the not-equal-to N/A formula when the range contains only Yes, No, and N/A
Use this shorter alternative if the response range contains only Yes, No, and N/A values, so every non-Yes/No entry is an N/A response.
Calculate Yes/No response percentages in WPS Office Spreadsheets
Open your workbook in WPS Office Spreadsheets, apply COUNTIF formulas to your survey columns, and format the result as a percentage while ignoring N/A responses.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office and open the .xlsx or .xls file that contains your Yes, No, and N/A response data.
- 2. Enter the COUNTIF percentage formula: Click the result cell and type =COUNTIF(B2:B17,"Yes")/(COUNTIF(B2:B17,"Yes")+COUNTIF(B2:B17,"No")).
- 3. Format the result as a percentage: Select the formula cell, go to the Home tab, open the Number Format menu, and choose Percentage.
- 4. Review the calculated percentage: Check that N/A responses are excluded and the result reflects Yes as a share of all valid Yes/No answers.
Frequently Asked Questions
What formula calculates the percentage of Yes responses while ignoring N/A?
If the responses are in B2:B17, use =COUNTIF(B2:B17,"Yes")/(COUNTIF(B2:B17,"Yes")+COUNTIF(B2:B17,"No")). This divides the Yes count by the total Yes and No count, excluding N/A.
Can I use a shorter formula if my response column has only Yes, No, and N/A?
Yes. If the range contains only Yes, No, and N/A, you can use =COUNTIF(B2:B17,"Yes")/COUNTIF(B2:B17,"<>N/A"). The denominator counts every cell that is not N/A.
Why does my percentage formula return a decimal instead of a percent?
This happens when the result cell is not formatted as a percentage. Select the cell, go to the Home tab, open the Number Format menu, and choose Percentage, or press Ctrl+Shift+%.
Will this Excel formula work in WPS Office?
Yes. WPS Office Spreadsheets supports COUNTIF and common Excel formulas, so you can use the same formula without changing it.
How do I exclude blank cells from the percentage calculation?
Use COUNTIFS with additional criteria to count only non-blank valid responses, or structure your formula so the denominator counts cells that meet Yes, No, or other specified non-blank criteria.
