How to Use a Dynamic Formula in WPS Spreadsheets to Return the Maximum of Two Spilled Ranges
Question details
The user needs a dynamic array formula that calculates the row-by-row maximum of two spilled ranges in WPS Spreadsheets when the ranges can spill to different rows based on user selections.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is building a dynamic report where two array formulas spill into variable-sized ranges based on selections and needs a formula that returns the larger value from each corresponding row.
- Observed behavior
- A standard fill-down formula cannot automatically adapt when the two spilled ranges change size, and the user wants the result to spill row-by-row without manual updates.
Verify that each source dynamic array formula starts in a known cell, such as B2 and C2, and that both spilled ranges have compatible dimensions before entering the comparison formula.
Use IF with Spill Range References to Compare Two Dynamic Arrays Row by Row
Use this solution when both spilled ranges start in known cells and you want the formula to spill the maximum value for each corresponding row automatically.
In WPS Spreadsheets, appending # to a spill start cell returns the entire spilled range from that dynamic array formula. The expression B2#>C2# compares each corresponding value in the two spilled ranges and returns TRUE or FALSE for each row. The IF function then returns the value from B2# when the condition is true; otherwise, it returns the value from C2#.
Click the cell where the row-by-row maximum should begin spilling.
Type =IF(B2#>C2#,B2#,C2#), replacing B2 and C2 with the first cells of your two spilled ranges.
Press Enter to let WPS Spreadsheets spill the maximum value for each corresponding row automatically.
If you see a #SPILL! error or incorrect results, make sure both spilled ranges have the same number of rows and columns and that the output area is clear.
Create Dynamic Max Formulas for Spilled Ranges in WPS Spreadsheets
WPS Spreadsheets supports dynamic array formulas and spill range references, so you can compare two spilled ranges row-by-row and return the maximum value automatically as your data changes.
- 1. Open WPS Spreadsheets: Launch WPS Office and open the workbook that contains the two dynamic array formulas.
- 2. Locate the spill start cells: Identify the first cell of each spilled range, such as B2 and C2.
- 3. Enter the dynamic comparison formula: Select the result start cell, type =IF(B2#>C2#,B2#,C2#), and press Enter.
- 4. Verify the spilled results: Confirm that WPS Spreadsheets fills the maximum value for each row and updates when the source ranges change.
- 5. Save the workbook: Save the file in .xlsx format to keep full compatibility with Microsoft Excel.
Frequently Asked Questions
What does the # symbol mean in a WPS spreadsheet formula?
The # symbol after a cell reference returns the entire spilled range from a dynamic array formula. For example, B2# refers to all cells spilled from the formula in B2.
Why do I get a #SPILL! error when comparing two spilled ranges?
A #SPILL! error usually means the output area is blocked or the two spill ranges are not compatible. Clear the cells below the formula and make sure both ranges have the same number of rows and columns.
Can I use this dynamic formula in Microsoft Excel?
Yes, the same formula =IF(B2#>C2#,B2#,C2#) works in Microsoft Excel because WPS Spreadsheets and Excel both support dynamic arrays and spill range references.
How do I make the maximum formula update when my selections change?
Use spill range references with # and ensure the source formulas are dynamic array formulas. WPS Spreadsheets will recalculate and spill the new maximum values automatically.
Does WPS Office support dynamic array formulas in .xlsx files?
Yes, WPS Spreadsheets supports dynamic array formulas and saves them in .xlsx format, maintaining compatibility with Microsoft Excel.
