How to Calculate Empty or Zero-Cell Periods in a WPS Office Excel Row
Question details
Calculate the lengths of consecutive empty or zero-value periods between populated cells in an Excel row, and determine the average period length and number of periods.

- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- The user wants to analyze a row of data and quantify gaps consisting of empty or zero-value cells between populated or non-zero cells.
- Observed behavior
- The spreadsheet should return the average length and number of consecutive empty or zero-value periods based on the selected row.
Make sure the data range is a single row, such as A2:T2, and decide whether blank cells are truly empty or formulas returning 0. In older spreadsheet versions, prepare to confirm array formulas with Ctrl+Shift+Enter instead of Enter.
Use a FREQUENCY Array Formula to Calculate the Average Zero-Cell Period
Best for a formula-only approach that updates automatically when row values change; returns the average length of consecutive empty or zero-value periods in the row.
This method uses the FREQUENCY function to group consecutive zero or empty cells into periods and then AVERAGE to calculate the mean period length. Populated cells act as separators.
Click the first result cell, such as U2, beside the row you want to analyze.
Type =AVERAGE(IF(FREQUENCY(IF(A2:T2=0,COLUMN(A2:T2)),IF(A2:T2<>0,COLUMN(A2:T2)))=0,"",FREQUENCY(IF(A2:T2=0,COLUMN(A2:T2)),IF(A2:T2<>0,COLUMN(A2:T2))))).
If you are using an older version, press Ctrl+Shift+Enter instead of Enter to confirm the formula.
Drag the fill handle from U2 down to apply the formula to the remaining rows.

Use a VBA Macro to Count Zero Periods and Return the Average
Use this method when you also need the number of zero periods or want to handle a trailing zero period after the last populated cell.
Calculate Empty or Zero-Cell Periods Directly in WPS Office Excel
You can determine the average length and number of consecutive empty or zero-value periods in a row directly in WPS Office Excel using an array formula or macro. WPS Office Excel supports the same workbook formulas and functions, so you can analyze gaps without switching software.
- 1. Open your workbook: Launch WPS Office Excel and open the workbook that contains the row you want to analyze.
- 2. Select the result cell: Click the first cell where you want the result, such as U2, to the right of the target row.
- 3. Enter the formula: Type the FREQUENCY-based array formula into U2 and adjust A2:T2 to match your actual data range.
- 4. Confirm and copy: Press Enter or Ctrl+Shift+Enter if required, then drag the fill handle down to copy the formula to other rows.

Frequently Asked Questions
Does this method treat blank cells and zero-value cells as the same type of period?
Yes. The array formula uses A2:T2=0, so it treats both truly empty cells and cells containing 0 as zero periods. Non-zero or populated cells act as separators.
How do I count the number of zero periods instead of only the average length?
Add a COUNT-based array formula such as =SUM(--(FREQUENCY(IF(A2:T2=0,COLUMN(A2:T2)),IF(A2:T2<>0,COLUMN(A2:T2)))>0)) in the result cell and confirm it with Ctrl+Shift+Enter if required. Alternatively use the VBA macro to return both count and average.
Do I still need Ctrl+Shift+Enter in WPS Office Excel?
In newer dynamic array versions you may only need Enter. In older versions, press Ctrl+Shift+Enter after typing the formula so it is recognized as an array formula.
Can I apply the same calculation to a column instead of a row?
Yes, but you need to transpose the logic: use ROW instead of COLUMN and adjust the ranges and bins accordingly, for example A2:A21.




