How to Create a Dynamic Array Convolution with Zero Padding in WPS Spreadsheets
Question details
The user needs a self-contained WPS Spreadsheets formula that convolves a vector and a kernel with offsets, moving averages, and automatic zero padding, without relying on helper formulas.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Creating a dynamic calculation for convolution-like operations such as moving averages, smoothing, or signal processing inside a single spilled range.
- Observed behavior
- The formula should automatically pad boundaries with zeros and spill the full convolution result for every offset.
Confirm that your WPS Office version supports dynamic array functions such as LET, LAMBDA, MAP, VSTACK, DROP, TAKE, and MAKEARRAY. Prepare two vertical numeric ranges: one for the source vector and one for the kernel.
Use LET, MAP, and SUMPRODUCT to Build a Zero-Padded Convolution Formula
Best for a fully dynamic, single-cell formula that spills all convolution results without helper columns.
This pattern pads the vector with m-1 zeros above and below, then uses MAP to slide a window of kernel size across the padded vector. SUMPRODUCT multiplies each window by the kernel and sums the products.
The formula assumes the vector and kernel are vertical ranges. If your convolution definition requires reversing the kernel, replace k in SUMPRODUCT with a dynamically reversed kernel.
Click an empty cell where the spilled result should begin, for example E2.
Type or paste the following formula, replacing B2:B6 with your vector and C2:C4 with your kernel: =LET(v,$B$2:$B$6,k,$C$2:$C$4,n,ROWS(v),m,ROWS(k),p,VSTACK(MAKEARRAY(m-1,1,LAMBDA(r,c,0)),v,MAKEARRAY(m-1,1,LAMBDA(r,c,0))),o,SEQUENCE(n+m-1),MAP(o,LAMBDA(i,SUMPRODUCT(TAKE(DROP(p,i-1),m),k))))
Press Enter. WPS Spreadsheets spills one value for each offset in the convolution result.
Create Zero-Padded Convolution Arrays in WPS Spreadsheets
WPS Spreadsheets can directly complete this dynamic-array convolution task. Use LET, MAP, SUMPRODUCT, DROP, and TAKE to build one spilled formula with automatic boundary padding, just as you would in Excel.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office and open the Excel-compatible workbook that contains your vector and kernel data.
- 2. Select a target cell for the spilled result: Click an empty cell where you want the convolution output to start, such as E2.
- 3. Enter the convolution formula: Type the LET/MAP/SUMPRODUCT formula and replace the vector and kernel references with your actual cell ranges.
- 4. Confirm automatic zero padding: Press Enter and check that the formula pads boundaries with zeros and spills the complete result.
- 5. Save the workbook: Save the file as .xlsx or WPS Workbook format to preserve the dynamic array formula.
Frequently Asked Questions
Does this formula work in WPS Spreadsheets?
Yes. WPS Spreadsheets supports dynamic arrays, LET, LAMBDA, MAP, VSTACK, DROP, TAKE, and MAKEARRAY in recent versions. Open the workbook, select the output cell, and enter the formula exactly as shown.
How do I reverse the kernel for mathematical convolution?
Replace k in the SUMPRODUCT expression with a reversed kernel. You can create a reversed kernel dynamically using INDEX(k,SEQUENCE(m,,m,-1)). Then use that reversed array inside SUMPRODUCT.
Can I use horizontal vector and kernel ranges?
The formula above is written for vertical ranges. For horizontal data, transpose the ranges or adjust the padding to use HSTACK and COLUMNS instead of VSTACK and ROWS.
What if I use an older WPS Office version without LAMBDA?
Update WPS Office to the latest version first. If dynamic array functions are still unavailable, use helper columns to pad the vector and apply SUMPRODUCT per offset.
Can this formula compute moving averages?
Yes. Set the kernel to the moving-average weights, such as 0.5 and 0.5 for a two-period average, or 1/3 for each of three periods. The same formula computes the weighted sliding sum across the padded vector.
