How to Average Every 16th Column in WPS Office Spreadsheets Excluding Blanks and Text but Including Zeros
Question details
The user needs to average every sixteenth column in a spreadsheet range while excluding empty cells and text entries such as NA, but still treating numeric zero values as valid data.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working with a repeating column layout, such as K14:AIG14, where only every sixteenth column contains relevant numeric values, and some cells in those columns are blank or contain text.
- Observed behavior
- The goal is a correct average that ignores blanks and text but includes numeric zeros.
Open the workbook in WPS Office Spreadsheets and confirm the starting cell, ending cell, and column interval for the range you want to average. If you are using an older build, be prepared to enter the formula as an array formula by pressing Ctrl+Shift+Enter.
Use an AVERAGE IF array formula to average every 16th column while excluding blanks, ignoring text, and keeping zeros
Use this approach when you need a single formula to average only the intended columns in a large range and to keep valid zero values in the calculation.
The formula uses MOD(COLUMN(K14:AIG14),16)=11 to select every 16th column beginning with column K. The nonblank test K14:AIG14<>"" excludes empty cells, and AVERAGE ignores text such as NA while still counting numeric zeros.
Click the cell where you want the average to appear, such as an empty cell outside or below the data range.
In the formula bar, type =AVERAGE(IF((MOD(COLUMN(K14:AIG14),16)=11)*(K14:AIG14<>""),K14:AIG14)).
Press Enter. If your version of WPS Office Spreadsheets does not process array formulas automatically, press Ctrl+Shift+Enter instead.
Use WPS Office Spreadsheets to Average Every 16th Column Without Losing Valid Zeros
WPS Office Spreadsheets supports the same AVERAGE, IF, MOD, and COLUMN functions used in Excel, so you can enter the array formula directly, keep zero values in the average, and work with your existing .xlsx files without switching tools.
- 1. Open the workbook: Launch WPS Office Spreadsheets and open the Excel workbook that contains the range K14:AIG14.
- 2. Select the result cell: Click an empty cell where you want the calculated average to appear.
- 3. Enter the formula: Type =AVERAGE(IF((MOD(COLUMN(K14:AIG14),16)=11)*(K14:AIG14<>""),K14:AIG14)) in the formula bar.
- 4. Confirm as an array formula: Press Enter, or press Ctrl+Shift+Enter if your WPS version requires legacy array confirmation.
- 5. Verify the result: Check that blank cells and text entries are ignored while any zero values in the selected columns are counted in the average.
Frequently Asked Questions
Does the AVERAGE(IF(...)) formula include zero values?
Yes. The condition K14:AIG14<>"" removes only empty cells, and the AVERAGE function ignores text such as NA, but numeric zero values are counted normally.
Why does MOD(COLUMN(K14:AIG14),16)=11 select every 16th column?
Column K is the 11th column, so any selected column with a column number that leaves a remainder of 11 when divided by 16 is included. This picks K, then every 16th column after K.
Can I use this formula in WPS Office Spreadsheets?
Yes. WPS Office Spreadsheets supports AVERAGE, IF, MOD, and COLUMN. If needed, enter the formula as an array formula by pressing Ctrl+Shift+Enter.
How do I change the formula for a different starting column or interval?
Replace 16 with your desired column interval and replace 11 with the column number of the first column you want to include, then adjust the range to match your data.
