'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
Calculate a Completion Percentage Excluding N/A in Excel
Question details
The user wants to calculate a task or document completion percentage in Excel, specifically excluding items marked as not required (N/A) from the total count.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking the progress of document submissions or checklists where certain requirements are conditional and marked as N/A.
- Observed behavior
- The user needs a formula that accurately reflects progress by only dividing the completed required items by the total required items, ignoring N/A cells.
Ensure your data range is clearly defined and that any items that are not required are consistently marked with the text "N/A" rather than left blank.
Use COUNTIFS and COUNTIF to Exclude N/A Values
This method calculates the percentage by counting completed items (ignoring 0 and N/A) and dividing that number by the total number of required items (excluding N/A).
To get an accurate completion rate, you must adjust both the numerator (completed tasks) and the denominator (total tasks). By using COUNTIFS for the numerator and COUNTIF for the denominator, you can dynamically exclude any cells containing "N/A".
Click on the cell where you want the completion percentage to appear.
Type the formula: =COUNTIFS(A2:L2,"<>"&"n/a",A2:L2,"<>"&0)/COUNTIF(A2:L2,"<>"&"n/a") into the formula bar. Ensure A2:L2 matches your actual data range.
Press Enter to calculate the result. You can then click and drag the fill handle in the bottom-right corner of the cell to copy the formula down the column for other rows.
Select the formula cells, navigate to the Home tab on the ribbon, and click the Percentage Style icon (%) in the Number group to display the decimal result as a percentage.
Calculate Completion Percentages Seamlessly with WPS Spreadsheet
WPS Spreadsheet offers full support for advanced logical functions like COUNTIF and COUNTIFS, allowing you to track project progress accurately. It provides a familiar interface to format and manage your data efficiently.
- 1. Open your tracker: Launch WPS Spreadsheet and open the document containing your task or document checklist.
- 2. Select the result cell: Click the cell where the completion percentage should be calculated.
- 3. Input the formula: Type the standard =COUNTIFS(...) / COUNTIF(...) formula tailored to your specific row range and press Enter.
- 4. Format the output: Click the '%' icon in the Home tab to instantly format your calculation as a readable percentage.
Frequently Asked Questions
What if my blank cells are counted as incomplete instead of N/A?
Blank cells evaluate as zero in this formula and will be counted as incomplete items. If blanks should also be ignored from the total count, you must add another condition to the COUNTIF denominator to exclude blanks, such as: COUNTIFS(A2:L2,"<>"&"n/a", A2:L2,"<>").
Can I use text like "Exempt" instead of "N/A"?
Yes, you can easily adapt the formula by replacing "n/a" with "Exempt". The logic remains exactly the same, and it will exclude any cells containing that specific text from your completion calculation.
Why does my result show as a decimal like 0.8 instead of 80%?
Spreadsheet software calculates percentages as decimal fractions by default. You need to apply the Percentage number format to the cell by selecting it, navigating to the Home tab, and clicking the percent style button (%).
