Use VSTACK to Start a Dynamic Array Spill Below the Formula in WPS Spreadsheet
Question details
Add a blank first row to a dynamic array result so the formula can remain in cell C2 while calculated values start in row 3.
- Product
- WPS Spreadsheet dynamic array formulas
- Device & OS
- not provided
- Scenario
- The user wants to multiply the named ranges Thing_1 and Thing_Two and display the spilled results one row below the formula cell.
- Observed behavior
- The desired result is a blank spilled value in row 2 followed by the multiplication results beginning in row 3.
Make sure the cells below C2 are empty so the dynamic array can spill without a #SPILL! error. Confirm that Thing_1 and Thing_Two are valid named ranges with compatible dimensions.
Add a blank first row with the VSTACK function
Use this formula when you want the formula to stay in C2 but need its dynamic array output to begin with a blank result in row 2.
VSTACK places arrays vertically. By stacking an empty text value above the multiplication calculation, the first spilled row is blank and the calculated values begin on the following row.
Click cell C2, which is the cell where you want to keep the dynamic array formula.
Type =VSTACK("",Thing_1*Thing_Two) in C2 and press Enter.
Verify that C2 displays a blank result and that the multiplication results spill downward starting in C3.
Create offset dynamic array results in WPS Spreadsheet
WPS Spreadsheet lets you combine dynamic array calculations with VSTACK so you can insert a blank row at the top of the spilled output without moving the formula from its original cell.
- 1. Open the worksheet in WPS Spreadsheet: Open your .xlsx workbook and select the worksheet containing the Thing_1 and Thing_Two named ranges.
- 2. Choose the output cell: Click C2 and ensure the intended spill range below it does not contain existing values.
- 3. Insert the formula: Enter =VSTACK("",Thing_1*Thing_Two) in the formula bar or directly in C2.
- 4. Confirm the spill results: Press Enter and confirm that the blank top result appears in row 2 and the calculated array starts in row 3.
Frequently Asked Questions
Why do the results start in C3 when the formula is in C2?
VSTACK adds the empty string "" as the first item in the spilled array. That blank item occupies C2, so the multiplication results begin in the next row, C3.
What causes a #SPILL! error with this VSTACK formula?
A #SPILL! error occurs when cells in the required spill range contain data, merged cells, or another obstruction. Clear the cells below C2 and unmerge any cells in the output area.
Do Thing_1 and Thing_Two need to be the same size?
Yes. For element-by-element multiplication, the named ranges should have matching rows and columns. Otherwise, the calculation can return an error or unexpected results.
Can I use a cell reference instead of named ranges in the formula?
Yes. For example, if the values are in A2:A10 and B2:B10, enter =VSTACK("",A2:A10*B2:B10) in C2.
