Calculation Issues

Automatically Update Excel RSQ and SUMSQ Formulas When Sample Ranges Change

Huda QurayshiHuda Qurayshi Nov 12, 2026 869 views

Question details

Create formulas that automatically adjust their input ranges when daily in-sample and out-of-sample row boundaries change.

Automatically Update Excel Formulas When Sample Range Sizes Change
Product
Microsoft Excel formulas
Device & OS
not provided
Scenario
The user maintains a table of dates and corresponding start and end row numbers for changing sample periods.
Observed behavior
RSQ, SUMSQ, and similar formulas require manual range edits whenever the sample row ranges change.
Before you start

Set up a structured lookup table containing a Current Date column plus separate start-row and end-row columns for each sample period. Confirm that the selected date in the worksheet exactly matches a date in that table.

Solution 1Recommended

Use INDEX and XLOOKUP to create dynamic formula range endpoints

Use this method when the start and end rows are stored in a date-based table and calculations must update automatically as those row numbers change.

INDEX returns a cell reference at a specified row, while XLOOKUP retrieves the correct start and end row for the selected date. Combining them with a colon creates a dynamic range that functions in RSQ, SUMSQ, and other range-based formulas.

1
Create a sample-range lookup table

In your worksheet, create an Excel table with columns such as Current Date, In Sample Start, and In Sample End. Enter the applicable start and end row numbers for each date.

2
Select the calculation date

Enter or select the active date in a cell such as A1. This date is used by XLOOKUP to find the correct row boundaries in the table.

3
Build the first dynamic range

For the first RSQ input range in column M, enter: INDEX(M:M,XLOOKUP(A1,Table[Current Date],Table[In Sample Start])):INDEX(M:M,XLOOKUP(A1,Table[Current Date],Table[In Sample End])).

4
Build the second dynamic range and complete the formula

For the matching values in column G, use the same pattern and combine both ranges: =RSQ(INDEX(M:M,XLOOKUP(A1,Table[Current Date],Table[In Sample Start])):INDEX(M:M,XLOOKUP(A1,Table[Current Date],Table[In Sample End])),INDEX(G:G,XLOOKUP(A1,Table[Current Date],Table[In Sample Start])):INDEX(G:G,XLOOKUP(A1,Table[Current Date],Table[In Sample End]))).

5
Reuse the range pattern in other calculations

Replace RSQ with functions such as SUMSQ where appropriate, while keeping the INDEX and XLOOKUP range endpoints. Update only the table row values when a sample period changes.

Use INDEX and XLOOKUP to create dynamic formula range endpoints
Automatic range updates: After the formula is set up, changing the start or end row for a date in the lookup table updates the calculation range without editing the formula itself.
Dynamic spreadsheet calculations

Build automatically updating sample-range formulas in WPS Spreadsheet

WPS Spreadsheet lets you create date-driven formulas with INDEX and XLOOKUP, so statistical and calculation ranges can follow changing sample boundaries instead of being edited manually each day.

  1. 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, select Spreadsheets, and open the Excel workbook containing the source data and sample-range table.
  2. 2. Format the boundary data as a table: Select the date, start-row, and end-row data, then use the table option on the Insert tab to organize the lookup values.
  3. 3. Enter the selected date: Click the date input cell, such as A1, and enter the date whose sample range should be calculated.
  4. 4. Insert the dynamic calculation formula: Click the result cell and enter an INDEX and XLOOKUP formula that returns the start and end references for the required columns.
  5. 5. Update row limits in the table as needed: When sample boundaries change, edit the relevant start or end row value in the table and allow WPS Spreadsheet to recalculate the formula.
Create dynamic start and end points for RSQ, SUMSQ, and other range-based calculations.Use XLOOKUP and INDEX to retrieve row boundaries from a date table.Open and edit Microsoft Excel .xlsx and .xls workbooks with compatible spreadsheet formatting.Use a familiar spreadsheet interface for formula entry, tables, and calculation review.
microsoft office alternative - wps office

Frequently Asked Questions

Why use INDEX instead of INDIRECT for dynamic row ranges?

INDEX returns a reference without converting text into a reference, making it generally more suitable for building dynamic endpoints such as INDEX(M:M,startRow):INDEX(M:M,endRow).

Can I use the same dynamic-range method with SUMSQ?

Yes. For example, use =SUMSQ(INDEX(M:M,startRow):INDEX(M:M,endRow)), where startRow and endRow are returned by XLOOKUP or stored in helper cells.

What happens if XLOOKUP cannot find the selected date?

XLOOKUP returns an error when A1 has no matching value in Table[Current Date]. Check that both values are true dates, not mismatched text, and that the date exists in the lookup table.

Do both RSQ ranges need to have the same number of rows?

Yes. The two arrays supplied to RSQ must cover matching observations. Make sure the start and end row lookups produce equivalent row counts for both columns.

How can I create separate in-sample and out-of-sample formulas?

Add separate columns such as Out of Sample Start and Out of Sample End to the lookup table. Use those columns in the XLOOKUP return-array arguments for the out-of-sample formula.