WPS Spreadsheets: Average Excluding Zeros and Blanks While Treating PTO as 9
Question details
The user needs a formula in WPS Spreadsheets that averages the values in D3:J3, excludes zeros and blank cells, and treats the text value PTO as 9 when calculating the average.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- An attendance or scheduling worksheet uses D3:J3 for hours or points, where PTO indicates paid time off and should contribute 9 to the average.
- Observed behavior
- The formula should return an average in which blank cells and zero values are ignored, and each PTO cell contributes 9.
Open your workbook in WPS Spreadsheets and check that D3:J3 contains only numbers, blanks, or the text PTO before entering the array formula.
Use an Array Formula to Exclude Zeros/Blanks and Convert PTO to 9
Use this formula when your D3:J3 range mixes numeric values, blanks, zeros, and PTO entries that must be counted as 9.
The formula creates an internal array for AVERAGE: values greater than zero are kept, zeros and blanks become empty text that AVERAGE ignores, and PTO is replaced with 9. This keeps the calculation within one cell and works in modern WPS Spreadsheets.
Click the blank cell where you want the average to appear, such as K3 or any cell outside D3:J3.
Type =AVERAGE(IF(D3:J3<>"pto",IF(D3:J3>0,D3:J3,""),9)) into the selected cell.
In current WPS Spreadsheets, press Enter. If your version uses legacy array formula behavior, press Ctrl+Shift+Enter instead.
Check the returned value: blank cells and zeros should not affect the average, and each PTO cell should add 9 before the mean is calculated.
Use AVERAGEIF When PTO Does Not Need to Be Converted to 9
Choose this shorter formula if you only need to exclude zeros from the average and do not need PTO to contribute a value of 9.
Calculate Attendance Averages with PTO as 9 Directly in WPS Spreadsheets
WPS Spreadsheets supports the same AVERAGE, IF, and array formula approach used in Excel, so you can complete this average calculation directly without moving to another tool. Your attendance workbook remains compatible with Excel formats, and the familiar formula bar and functions make the switch easy.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office and open your attendance .xlsx or .xls file, or create a new spreadsheet from Menu > New.
- 2. Select the target cell: Click a blank cell such as K3 where the average should appear.
- 3. Enter the formula: Type =AVERAGE(IF(D3:J3<>"pto",IF(D3:J3>0,D3:J3,""),9)) into the formula bar or cell.
- 4. Confirm and save: Press Enter in current WPS Spreadsheets or Ctrl+Shift+Enter in legacy array versions, verify the result, then click Menu > Save or press Ctrl+S.
Frequently Asked Questions
What does the formula =AVERAGE(IF(D3:J3<>"pto",IF(D3:J3>0,D3:J3,""),9)) do?
It builds an array from D3:J3. Numbers greater than zero are kept, zeros and blanks become empty text that AVERAGE ignores, and any PTO entry is replaced with 9 before the average is calculated.
Why do I need to press Ctrl+Shift+Enter after typing this formula?
In older versions of WPS Spreadsheets and Excel, formulas that process a range as an array must be confirmed with Ctrl+Shift+Enter. Newer versions with dynamic arrays often accept a normal Enter press.
Does the formula treat lowercase 'pto' the same as uppercase 'PTO'?
WPS Spreadsheets and Excel generally compare text case-insensitively, so pto and PTO are treated the same. However, extra spaces such as ' PTO' are not the same, so keep the cell text clean.
Can I use AVERAGEIF instead of the array formula?
Yes, if PTO does not need to be treated as 9, enter =AVERAGEIF(D3:J3,"<>0",D3:J3). If blank cells need separate handling, the recommended array formula is more reliable.
What should I check if the formula returns #VALUE! or #NAME?
Check that the formula uses commas and English function names, that PTO cells contain no extra spaces, and that you confirm with Ctrl+Shift+Enter in legacy array formula versions.
