How to Average Every 12th Row Starting at Row 6 in WPS Office Excel
Question details
The user needs a formula that averages values in column C at rows 6, 18, 30, 42, and every subsequent 12th row while ignoring blank cells.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has data in column C and wants to calculate the average of only the values located at 12-row intervals beginning at row 6.
- Observed behavior
- The goal is to return a single average value from rows 6, 18, 30, 42, and later matching rows without counting empty cells.
Open the workbook in WPS Office Spreadsheets and identify the column that contains the values to average. If the data may extend beyond row 1000, plan to update the range references before entering the formula.
Use AVERAGE with MOD and ROW to Average Every 12th Row Starting at Row 6
Use this formula when you need one average from rows 6, 18, 30, 42, and every 12th row after that while excluding blank cells.
The formula checks row numbers with MOD(ROW(C6:C1000),12)=6 and includes only nonblank cells from column C. It works in current WPS Office Spreadsheets as a normal formula and can also be entered as an array formula in older spreadsheet engines.
Click cell J6, or any empty cell where you want the average result to appear.
Type =AVERAGE(IF((MOD(ROW(C6:C1000),12)=6)*(C6:C1000<>""),C6:C1000)) into the formula bar.
Press Enter. In older Excel or WPS versions that do not support dynamic arrays, press Ctrl+Shift+Enter instead to confirm it as an array formula.
If your data goes beyond row 1000, change both C6:C1000 references to a larger range such as C6:C5000.
Use a Helper Column and AVERAGEIF to Average the Same Rows
Use this alternative if you prefer a non-array formula or want to visibly mark which rows are included in the calculation.
Use WPS Office Spreadsheets to Average Every 12th Row from Row 6
WPS Office Spreadsheets supports the same AVERAGE, MOD, ROW, and IF functions, so you can calculate the targeted average directly in a familiar Excel-compatible interface without extra purchases or macros.
- 1. Open WPS Office Spreadsheets: Launch WPS Office, open the workbook, and go to the sheet that contains the values in column C.
- 2. Select the result cell: Click cell J6 or any empty cell where you want the average to appear.
- 3. Enter the formula: Type =AVERAGE(IF((MOD(ROW(C6:C1000),12)=6)*(C6:C1000<>""),C6:C1000)) and press Enter, or Ctrl+Shift+Enter in older versions that require array confirmation.
- 4. Verify the result: Check that the returned average matches rows 6, 18, 30, 42, and subsequent 12-row intervals in your data.
Frequently Asked Questions
Will this formula work in WPS Office Spreadsheets?
Yes. WPS Office Spreadsheets supports the AVERAGE, IF, MOD, and ROW functions. In current versions, you can enter the formula normally; in older versions, confirm it with Ctrl+Shift+Enter.
How do I change the formula if my data extends past row 1000?
Replace both C6:C1000 references with a larger range such as C6:C5000 or C6:C10000, making sure both ranges are identical.
Why does the formula use MOD(ROW(C6:C1000),12)=6?
It includes rows whose row number leaves a remainder of 6 when divided by 12. Row 6, 18, 30, 42, and every 12th row after that all satisfy this condition.
Does the formula ignore blank cells in the selected rows?
Yes. The condition C6:C1000<>"" excludes blanks so blank cells are not counted in the average.
Can I average every 12th row in a different column?
Yes. Change the column letter in C6:C1000 to the column that contains your values, such as D6:D1000, in all parts of the formula.
