How to Find the Minimum Across a 3D Dynamic Array in WPS Office Spreadsheets
Question details
The user needs a dynamic-array formula in WPS Office Spreadsheets that reshapes a 3D array, calculates the minimum for each group, and returns the grouped results as a two-dimensional spill range.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Working with a named multidimensional array and calculating grouped minimum values without manual reshaping or helper tables.
- Observed behavior
- The user wants the calculation organized with LET and LAMBDA so the formula spills the final two-dimensional results automatically.
Confirm that your WPS Office version supports dynamic array functions such as TOCOL, WRAPCOLS, BYROW, DROP, LET, and LAMBDA, and verify that the source data is stored in a named structure such as Array3D.
Use LET and LAMBDA with TOCOL, WRAPCOLS, BYROW, and DROP to calculate grouped minima in WPS Office Spreadsheets
Use this method when you need a single spill formula that reshapes Array3D, calculates one minimum per group, and returns the results as a two-dimensional array.
This formula flattens the 3D array with TOCOL, groups it into rows of 30 values with WRAPCOLS, calculates MIN per row with BYROW, reshapes the grouped minima into rows of 5 values with WRAPROWS, and drops an incomplete final row with DROP.
Launch WPS Office and open the workbook that contains the named multidimensional array you want to analyze.
Click the top-left cell where you want the two-dimensional spill range to start, and leave the surrounding spill area empty.
In the formula bar, enter =LET(MINλ,LAMBDA(x,MIN(x)),reshaped,WRAPCOLS(TOCOL(Array3D),30),minima,BYROW(reshaped,MINλ),DROP(WRAPROWS(minima,5),-1)).
Change the 30 in WRAPCOLS to the number of elements in each group and the 5 in WRAPROWS to the number of groups per output row, then press Enter to spill the result.
Calculate grouped minima from a 3D dynamic array in WPS Office Spreadsheets
Use WPS Office Spreadsheets to enter the same modern dynamic-array formula, reshape the source array, and return a two-dimensional spill range without changing the Excel workbook structure.
- 1. Open your Excel workbook in WPS Office: Launch WPS Office Spreadsheets and open the workbook that contains the 3D dynamic array.
- 2. Name the source data if needed: Select the multidimensional source range, go to Formulas > Name Manager, and create the defined name Array3D.
- 3. Enter the LET/LAMBDA formula: Click the target output cell and enter the formula using TOCOL, WRAPCOLS, BYROW, WRAPROWS, and DROP, adjusting the group dimensions to match your array.
- 4. Press Enter to spill the result: Press Enter to return the two-dimensional spill range with the minimum value for each group.
Frequently Asked Questions
Does WPS Office Spreadsheets support LET and LAMBDA in Excel workbooks?
Yes. Recent WPS Office versions support dynamic array functions including LET, LAMBDA, TOCOL, WRAPCOLS, BYROW, DROP, and WRAPROWS. If a function is not recognized, update WPS Office or use the desktop version, because older releases may lack newer dynamic array features.
How do I adjust the formula if my 3D array is not 30 columns by 5 rows per group?
Change the 30 in WRAPCOLS to the number of elements you want in each group and change the 5 in WRAPROWS to the number of groups per output row. The final DROP(...,-1) removes an incomplete last row, so you can adjust or remove it if your data divides evenly.
Why does the formula use TOCOL before WRAPCOLS?
TOCOL flattens the named 3D structure into a single column in a consistent order. WRAPCOLS then wraps that column into groups of 30 elements so that BYROW can calculate one MIN value per group.
Can I apply the same pattern to MAX or AVERAGE?
Yes. Replace MINλ,LAMBDA(x,MIN(x)) with LAMBDA(x,MAX(x)) or LAMBDA(x,AVERAGE(x)), or use another aggregation function such as SUM inside BYROW, as long as the reshape dimensions match your grouping.
Where should I enter the formula so it spills correctly?
Select only the top-left cell of the intended output area and enter the formula once. WPS Office fills the adjacent cells automatically as a spill range, so keep the surrounding cells empty to avoid a #SPILL! error.
