Fix INDIRECT Returning Only the First Cell of a Range in WPS Spreadsheets
Question details
The user needs to understand why INDIRECT returns only the first value from a dynamically generated range and how to make it return the full range to functions such as AVERAGE or FORECAST.ETS.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is building a dynamic range with ADDRESS, ROW, or similar functions and passing it through INDIRECT to AVERAGE or FORECAST.ETS.
- Observed behavior
- INDIRECT returns only the first cell value instead of the complete range, so the calculation uses one value instead of the intended range.
Open the worksheet in WPS Spreadsheets and click the cell containing the INDIRECT formula. Check whether the inner ADDRESS and ROW or COLUMN parts return a single value or an array before applying the fix.
Force ROW or COLUMN to return a scalar value inside INDIRECT
Use this solution when your dynamically generated range is passed to AVERAGE or FORECAST.ETS but INDIRECT returns only the first cell.
When ADDRESS receives an array from ROW or COLUMN, INDIRECT can interpret the resulting reference as an array and return only the first element. Wrapping ROW or COLUMN in MIN or SUM resolves it to a single value so the reference string remains scalar.
Click the cell that contains the INDIRECT formula in WPS Spreadsheets, then click inside the Formula Bar so you can edit the formula.
Inside the ADDRESS functions, change ROW() to MIN(ROW()) so the row component resolves to a single scalar value instead of an array. For example, use =AVERAGE(INDIRECT(ADDRESS(MIN(ROW()),5,4)&":"&ADDRESS(MIN(ROW()),7,4))).
Make sure the formula builds one continuous text range from the start ADDRESS to the end ADDRESS, then press Enter and verify that AVERAGE or FORECAST.ETS now uses the full range.
Replace volatile INDIRECT with direct or INDEX-based references
Use this solution when you want a more reliable and faster formula that does not depend on volatile INDIRECT.
Fix INDIRECT Range References in WPS Spreadsheets
Open your workbook in WPS Spreadsheets, apply the scalar ROW or COLUMN fix or switch to INDEX and direct references, and your AVERAGE or FORECAST.ETS formulas will read the full range as intended.
- 1. Open the workbook: In WPS Spreadsheets, open the workbook and select the sheet containing the INDIRECT formula.
- 2. Edit the formula: Click the formula cell, then click in the Formula Bar to make the formula editable.
- 3. Apply the scalar fix: Wrap the row or column argument inside ADDRESS in MIN() or SUM(), or replace the INDIRECT range with a direct or INDEX-based reference.
- 4. Recalculate and verify: Press Enter and check that AVERAGE or FORECAST.ETS now uses the complete intended range.
- 5. Save the workbook: Click the Save icon or press Ctrl+S to keep the corrected formula.
Frequently Asked Questions
Why does INDIRECT return only the first cell when I use ROW() inside ADDRESS?
In array-capable formulas, ROW() may evaluate as an array rather than a single row number. INDIRECT can then coerce the range to its first item. Wrap ROW() in MIN(ROW()) or SUM(ROW()) to force a scalar value.
How do I write an INDIRECT range for AVERAGE in WPS Spreadsheets?
Use a single text string built from two ADDRESS parts. For example: =AVERAGE(INDIRECT(ADDRESS(MIN(ROW()),5,4)&":"&ADDRESS(MIN(ROW()),7,4))). Adjust the column numbers and row logic to match your data.
Can I avoid INDIRECT entirely?
Yes. Use direct references such as =AVERAGE(E5:G5), or use INDEX, TAKE, and DROP where available. These options are usually faster and avoid volatile recalculation.
Does WPS Spreadsheets support FORECAST.ETS with dynamic ranges?
Yes, but the range argument must resolve to a contiguous range. Apply the same scalar-ROW fix or use direct references so FORECAST.ETS receives the full intended range.
Why is my formula still returning one value after adding MIN(ROW())?
Check whether the ADDRESS function returns the same start and end cell or whether the referenced area contains only one numeric value. Use Evaluate Formula in WPS Spreadsheets to inspect each step of the formula.
