'%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 Completion Percentage When Cells Contain N/A in Excel
Question details
The user needs to calculate an average percentage across multiple columns where text values of "n/a" are explicitly treated as 100%, and avoid divide-by-zero errors for rows containing only "n/a".
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Calculating row-wise completion rates where missing data or exceptions are marked as "n/a" and should be considered fully complete.
- Observed behavior
- Standard AVERAGE or SUM/COUNT formulas ignore text cells, which skews the percentage or results in a #DIV/0! error when the entire row consists of "n/a" values.
Ensure your percentage columns are clearly defined and verify the exact text string (like "n/a", "N/A", or "Not Applicable") used in your dataset.
Use an Array Formula with IFERROR and AVERAGE
This method converts "n/a" values to 1 (100%) during the average calculation and catches divide-by-zero errors to correctly output 100%.
By combining IFERROR, AVERAGE, and IF, you can force the spreadsheet to evaluate text strings as numeric values for the calculation. This prevents the formula from ignoring text cells and handles instances where the entire dataset is text.
Click on the cell where you want the final completion percentage to appear.
Type the formula =IFERROR(AVERAGE(IF(F6:L6="n/a",1,F6:L6)),1). Replace 'F6:L6' with your actual cell range.
If you are using an older version of Excel or Spreadsheet, press Ctrl + Shift + Enter to evaluate it as an array formula. Curly brackets {} will appear around the formula.
Right-click the result cell, select 'Format Cells', choose the 'Percentage' category, and click OK to display the result properly.
Calculate Using SUM and COUNTIF
An alternative approach is to sum the percentage values, add the count of "n/a" cells, and divide by the fixed total number of columns.
Calculate Complex Percentages Seamlessly with WPS Spreadsheet
WPS Spreadsheet supports advanced array formulas, IFERROR, and AVERAGE functions natively, making it easy to process completion tracking and data analysis without hassle.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your percentage data.
- 2. Enter the formula: Click on the target cell and type =IFERROR(AVERAGE(IF(F6:L6="n/a",1,F6:L6)),1).
- 3. Evaluate the array: Press Ctrl + Shift + Enter to confirm the array formula.
- 4. Apply Percentage Formatting: Click the '%' icon on the Home tab to instantly format the cell as a percentage.
Frequently Asked Questions
Why do I get a #DIV/0! error when averaging cells with N/A?
The standard AVERAGE function ignores text values. If a row contains only text strings like "n/a", the function has a divisor of zero, resulting in a #DIV/0! divide-by-zero error. Wrapping the formula in IFERROR handles this exception gracefully.
Does the text case matter when using the IF function for 'n/a'?
No, the basic IF function is not case-sensitive. Both "n/a" and "N/A" will be evaluated as equal when performing logical tests in the spreadsheet.
How do I format the formula result as a whole number instead of a decimal?
You can either change the cell format to "Percentage" with zero decimal places, or multiply your entire formula by 100. For example: =IFERROR(AVERAGE(IF(F6:L6="n/a",1,F6:L6)),1)*100.
