WPS Spreadsheet: Use Cell Values to Define an AVERAGE Range
Question details
The user needs to use values stored in U26 and U27 to define the range for an AVERAGE formula.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A worksheet stores either cell references or row numbers in U26 and U27, and those values should determine the range averaged by the formula.
- Observed behavior
- The AVERAGE range must be built dynamically from the contents of U26 and U27 instead of being entered as a fixed range.
Check whether U26 and U27 contain complete cell references such as U10 and U20, or row numbers such as 10 and 20. Confirm that the resulting range contains numeric values that can be averaged.
Build the AVERAGE range from cell references in U26 and U27
Use this method when U26 and U27 contain cell references, such as U10 and U20.
INDIRECT converts the text created from U26 and U27 into a usable range reference. The ampersand version is concise, while CONCATENATE provides the same result in a more explicit form.
In WPS Spreadsheet, click the cell where you want to display the average.
Type =AVERAGE(INDIRECT(U26&":"&U27)) in the formula bar or directly in the cell, then press Enter.
Verify that U26 and U27 contain valid cell references and that the formula averages the intended range.
Create the AVERAGE range from row numbers
Use this method when U26 and U27 contain row numbers rather than complete cell references.
Create Dynamic AVERAGE Ranges in WPS Spreadsheet
WPS Spreadsheet lets you enter Excel-compatible AVERAGE, INDIRECT, CONCATENATE, and INDEX formulas to build a range from values stored in other cells.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheet, and open the workbook containing U26 and U27.
- 2. Choose the output cell: Click the cell where you want the dynamic average to appear.
- 3. Enter the appropriate formula: Use =AVERAGE(INDIRECT(U26&":"&U27)) for cell references, or =AVERAGE(INDEX(U:U,U26):INDEX(U:U,U27)) for row numbers.
- 4. Press Enter and verify: Press Enter, then confirm that the result matches the values in the intended range.
Frequently Asked Questions
What formula should I use if U26 and U27 contain cell references?
Enter =AVERAGE(INDIRECT(U26&":"&U27)). For example, if U26 is U10 and U27 is U20, the formula averages U10:U20.
Can I use CONCATENATE instead of the ampersand operator?
Yes. Use =AVERAGE(INDIRECT(CONCATENATE(U26,":",U27))). It produces the same range text as U26&":"&U27.
How do I define the range when U26 and U27 contain row numbers?
Use =AVERAGE(INDEX(U:U,U26):INDEX(U:U,U27)). If U26 is 10 and U27 is 20, WPS Spreadsheet averages the values in U10:U20.
Why might the INDIRECT formula return an error?
The formula returns an error when U26 or U27 contains an invalid reference, extra spaces, or a reversed range. Check that both values form a valid range such as U10:U20.
