How to Calculate the Average of the Two Closest Values in an Excel Table with WPS Office
Question details
The user needs a structured-reference formula that averages the two most recent non-empty positive values in an Excel table, even when blank rows occur.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working with a formatted Excel table that records positive values in a column named Value. Blank rows appear between entries, and they want a rolling result that always averages the two most recent positive values up to the current row.
- Observed behavior
- A blank row should return an empty result, a single positive value should return that value, and two or more positive values should return the average of the latest two.
Before entering the formula, verify that the data is in a formatted Excel table with a column named Value, and make sure any empty cells in that column are truly blank rather than storing zeros or text.
Use a LET-FILTER-TAKE formula to average the two latest positive values in an Excel table
Ideal for WPS Office versions that support dynamic array functions. It keeps the formula readable, ignores blank and non-positive rows, and automatically returns the rolling two-value average.
This formula builds an array of all values from the first table row to the current row, filters out values that are not greater than zero, and then averages the last two entries. The IFS function handles blank rows and the one-value case separately.
Click the first cell in the column where the rolling average should appear. If you add a new column inside the table, WPS automatically applies the table formatting and structured references.
Type =LET(idx,INDEX([Value],SEQUENCE(ROW()-ROW(Table1[#Headers]))),flt,FILTER(idx,idx>0),IFS(ISBLANK([@Value]),"",ROWS(flt)=1,[@Value],TRUE,AVERAGE(TAKE(flt,-2)))) and press Enter. Replace Table1 with the actual table name shown on the Table Design tab.
Drag the fill handle from the first result cell to the last table row, or press Ctrl+D after selecting the range. WPS updates the structured references for each row automatically.
Calculate the Two-Value Rolling Average in WPS Spreadsheets
WPS Office Spreadsheets lets you open your Excel table and use structured references, LET, FILTER, and TAKE to calculate the average of the two latest positive values while ignoring blanks. The familiar interface and Excel-compatible formula engine make the transition straightforward.
- 1. Open the workbook: Launch WPS Office and open the .xlsx file that contains the Excel table.
- 2. Go to the result column: Click the first cell in the column where you want the rolling two-value average.
- 3. Enter the formula: Type the LET-FILTER-TAKE formula exactly as shown in the solution and press Enter. Replace Table1 with your table name if it is different.
- 4. Fill down the formula: Use the fill handle or Ctrl+D to copy the formula through all data rows in the table.
- 5. Check the output: Confirm that blank rows remain blank, single-value rows show the single value, and later rows show the average of the two latest positive values.
Frequently Asked Questions
Does this formula work if my Excel table is not named Table1?
Yes. Replace Table1 in the formula with the actual table name. Select any cell in the table, open the Table Design tab, and read the name in the Table Name box.
What if there is only one positive value above the current row?
The IFS condition ROWS(flt)=1 returns the current value instead of an average, so no #DIV/0! or #CALC! error appears.
How does the formula handle blank rows?
The ISBLANK([@Value]) check returns an empty string for blank rows, so the rolling average is not calculated for those rows and they remain visually blank.
Can I exclude zeros as well as blanks?
Yes. The FILTER condition idx>0 already excludes zeros and negative values; only positive values are included in the rolling average.
Is this formula available in WPS Spreadsheets?
Current WPS Office versions support LET, FILTER, SEQUENCE, and TAKE. If your version does not recognize them, update WPS Office to the latest release and reopen the workbook.
