How to Calculate the Minimum Value in Every Seven-Row Date Block in WPS Office Spreadsheets
Question details
The user needs a WPS Office spreadsheet formula that returns the minimum value from a moving seven-row block on the source sheet '1D', with date criteria that can also advance as the formula is copied.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is analyzing data grouped in seven-row date blocks on the '1D' sheet and wants one formula that automatically advances the source range and the date comparison when copied.
- Observed behavior
- The formula should evaluate each seven-row block, compare the integer date values against the selected date criteria, and return the smallest positive value from the value column.
Confirm that the source sheet is named '1D', dates are in column A, and the values to evaluate are in column D. Decide the first result cell, because the formula uses that cell position to calculate the moving seven-row offset.
Use an OFFSET-Based MIN(IF) Formula That Moves by Seven Rows
Best when you want a single formula in WPS Office Spreadsheets to return the minimum positive value in each seven-row block as the formula is copied down.
The OFFSET function creates a seven-row-by-one-column block that starts at '1D'!$A$1439 or '1D'!$D$1439 and moves down in increments of seven rows.
The MIN(IF(...)) combination tests each date value against the date criteria and returns the smallest matching positive value from column D.
Select the first result cell, for example A2, and enter: =MIN(IF((INT(OFFSET('1D'!$A$1439,7*(ROW(A2)-ROW($A$2)),0,7,1))>$A208)*(INT(OFFSET('1D'!$A$1439,7*(ROW(A2)-ROW($A$2)),0,7,1))<=$A209)*(OFFSET('1D'!$D$1439,7*(ROW(A2)-ROW($A$2)),0,7,1)>0),OFFSET('1D'!$D$1439,7*(ROW(A2)-ROW($A$2)),0,7,1))).
If the first formula is not in row 2, change A2 and $A$2 to the actual starting cell, and change the source row 1439 to the first date row in your '1D' sheet.
Drag or copy the formula from the first result cell to the adjacent rows. Each copied row advances the OFFSET block by seven rows.
If WPS Office does not return a spilled result, press Ctrl+Shift+Enter to enter the formula as an array formula. If the date criteria must also advance by one row per copied row, adjust $A208 and $A209 to relative references or wrap them with OFFSET.
Calculate Every 7-Row Date Block Minimum in WPS Office Spreadsheets
Use WPS Office Spreadsheets to enter the OFFSET and MIN(IF) formula shown on this page. It evaluates each seven-row date block, lets you copy the result after adjusting the reference type, and keeps your existing .xlsx formulas working.
- 1. Open the workbook in WPS Office Spreadsheets: Launch WPS Office Spreadsheets and open the workbook that contains the '1D' source sheet.
- 2. Enter the OFFSET MIN(IF) formula: Select the first result cell and enter the recommended formula from the solution above.
- 3. Adjust the starting references and array entry: Update A2, $A$2, and the source row 1439 to match the first data position. Press Ctrl+Shift+Enter if the formula does not spill automatically.
- 4. Copy the formula to your analysis rows: Drag the fill handle down or across after changing ROW to COLUMN if necessary, so each result cell evaluates the next seven-row block.
- 5. Verify the returned minimum values: Check that each returned value corresponds to the start date and end date criteria for the intended seven-row block.
Frequently Asked Questions
Why does the OFFSET formula use 7*(ROW(A2)-ROW($A$2))?
It calculates the starting row offset. In the first formula cell it evaluates to 0, in the next row it evaluates to 7, then 14, so the seven-row source block moves down each time.
How can I make the formula advance when copied across columns?
Replace ROW(A2)-ROW($A$2) with COLUMN(A2)-COLUMN($A$2) in every OFFSET part. If the date criteria must also move, change $A208 and $A209 to relative or OFFSET-based references.
Do I need to enter the formula as an array formula in WPS Office?
If WPS Office Spreadsheets does not spill the result automatically, select the cell and press Ctrl+Shift+Enter after typing the formula. Dynamic array versions may not require this.
What should I update if my source data starts in a different row?
Change the source row 1439 in the OFFSET formula to your first date row, and update the start cell reference A2/$A$2 to match the cell where the first result is entered.
Why is the date column wrapped in INT()?
INT() removes the decimal time portion from date-time values, so the comparison against the date criteria is based on whole dates only.
