How to Count Consecutive Weather Runs and Average Duration in WPS Office Spreadsheet
Question details
The user needs to calculate how many separate runs of identical weather occur in a chronological list and the average duration of those runs.
- Product
- WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- A chronological weather list contains repeating conditions, and the user wants to summarize consecutive runs by weather type.
- Observed behavior
- The spreadsheet currently has weather values by date but no helper column that identifies when a new run starts or groups consecutive identical values for averaging.
Sort the weather data by date in ascending order and make sure the weather condition column is directly beside the date column. If there are blank cells, fill or filter them first so blanks do not become false runs.
Identify Consecutive Runs with Helper Columns and a PivotTable
Use this method when your weather readings are sorted by date and you need an auditable summary of run counts and average length for each weather type.
The helper columns mark only the rows where a weather value differs from the previous row, then assign a cumulative run number to every row. A PivotTable groups identical weather plus run number so each consecutive block is treated as one run.
In cell C1 type RunStart, then in C2 enter =IF(B2<>B1,1,0) and press Enter. Copy the formula down to every row of weather data; this marks the first day of each new run with 1.
In cell D1 type RunNumber. In D2 enter =SUM($C$2:C2) and fill down. This assigns a unique number to each weather run across the entire table.
Select the data range including Date, Weather, RunStart, and RunNumber. Go to Insert > PivotTable, place it on a new worksheet, and click OK. In the PivotTable Fields pane, drag Weather and RunNumber to Rows, then drag Date to Values and set it to Count to count the days in each consecutive run.
Create a small summary area for each weather type. Use =COUNTIFS(WeatherRange,weather_type)/COUNTIFS(WeatherRange,weather_type,RunStartRange,1) to divide total weather days by the number of run starts, giving the average consecutive run length.
Calculate Run Counts and Average Duration with COUNTIFS
Use this formula-based approach when you prefer a compact summary that recalculates automatically as the weather data grows.
Calculate Consecutive Weather Runs in WPS Office Spreadsheet
Open the weather workbook in WPS Office Spreadsheet and use helper columns with run-start flags and cumulative run numbers. Then summarize the run counts and average durations with a PivotTable or COUNTIFS formulas, with no extra add-ins required.
- 1. Open the workbook in WPS Office Spreadsheet: Launch WPS Office, click File > Open, and select the Excel workbook containing the weather dates and conditions.
- 2. Add run-start and run-number helper columns: Select the first helper cell, enter =IF(B2<>B1,1,0), and fill down. In the next column enter =SUM($C$2:C2) to create cumulative run numbers.
- 3. Create the run summary: Select the data, go to Insert > PivotTable, and drag Weather and RunNumber to Rows and Date to Values to count days per run. Alternatively use COUNTIFS to count weather days and run starts.
- 4. Calculate average duration per weather type: For each weather type, divide total weather days by the number of run-start rows: =COUNTIFS(WeatherRange,type)/COUNTIFS(WeatherRange,type,RunStartRange,1).
Frequently Asked Questions
How does the run-start formula work when the first data row has no previous weather value?
The first data row is usually compared with the header cell in B1, so it is marked as 1 and starts the first run. This is expected; if you want a strict check, use =IF(ROW()=2,1,IF(B2<>B1,1,0)) in C2.
Can I calculate average run duration without a PivotTable?
Yes. Create the RunStart and RunNumber helper columns, list unique weather types, count runs with =COUNTIFS($B$2:$B$100,G2,$C$2:$C$100,1), and calculate average duration with =COUNTIFS($B$2:$B$100,G2)/H2.
What should I do if my weather data has blank cells or missing dates?
Blank cells can be treated as a weather condition and create false runs. Fill in the correct weather value or filter out incomplete rows before applying the helper formulas.
Does WPS Office support the same PivotTable fields as Excel?
Yes, WPS Office Spreadsheet includes PivotTable support and opens .xlsx files directly, so the same drag-and-drop field settings and helper formulas work without conversion.
