WPS Spreadsheets: Fill a Formula Down Without Shifting Lookup Ranges
Question details
The user needs to fill a formula down while allowing the input reference to change and keeping the lookup ranges fixed.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A formula uses values from column D and searches fixed ranges in columns J through L for a matching interval.
- Observed behavior
- Relative references shift when the formula is filled down, and placing the formula in D3 while referencing D3 creates a circular reference.
Choose an output column that is different from the input column, such as C, and confirm that the lookup ranges J3:J18, K3:K18, and L3:L18 cover all required rows.
Use absolute references for the lookup ranges before filling the formula down
Use this solution when the input cell should change by row while the lookup ranges must remain fixed.
Enter the formula in an output cell such as C3, not in D3, because a formula in D3 that references D3 is circular. Dollar signs lock the lookup ranges, while D3 remains a relative reference that updates as the formula is filled down.
Click an empty output cell such as C3, making sure it is not the D3 input cell referenced by the formula.
Type =2*SUMPRODUCT(($J$3:$J$18<D3/2)*($K$3:$K$18>D3/2)*$L$3:$L$18) in the formula bar or directly in C3, then press Enter.
Select C3 and drag its fill handle down, or use Home > Fill > Down, to copy the formula through the required rows.
Check a copied cell such as C4 in the formula bar. D3 should become D4, while $J$3:$J$18, $K$3:$K$18, and $L$3:$L$18 remain unchanged.
Fill WPS Spreadsheet formulas with fixed lookup ranges
WPS Spreadsheets lets you enter the formula in a separate output column, lock lookup ranges with dollar signs, and fill the formula down so only the input reference changes by row.
- 1. Open your spreadsheet: Launch WPS Office and open the spreadsheet containing the input values and lookup ranges.
- 2. Choose the output column: Select an empty cell such as C3 instead of D3, because D3 is used as the formula input reference.
- 3. Enter the fixed-range formula: Type =2*SUMPRODUCT(($J$3:$J$18<D3/2)*($K$3:$K$18>D3/2)*$L$3:$L$18) and press Enter.
- 4. Copy the formula down: Use the fill handle or choose Home > Fill > Down to apply the formula to the remaining rows.
Frequently Asked Questions
Why should the formula not be entered in D3?
The formula references D3 as its input. Entering it in D3 makes the cell depend on itself, which creates a circular reference. Use a separate output cell such as C3.
Which references need dollar signs when filling the formula down?
Lock the lookup ranges as $J$3:$J$18, $K$3:$K$18, and $L$3:$L$18. Keep D3 relative so it changes to D4, D5, and later rows.
How can I fill the formula down in WPS Spreadsheets?
Select the formula cell, drag the fill handle down, or use Home > Fill > Down after entering the formula.
What should I check if the final interval returns an incorrect result?
Confirm that the upper-bound range in K3:K18 contains a sufficiently large value for the final interval and that all three lookup ranges contain the same number of rows.
