How to Adjust WPS Office Spreadsheet RANK Formulas When Dragging Across Columns
Question details
The user needs to correct mixed references in RANK and AVERAGE/OFFSET formulas in a league table so the score references move while anchored references stay fixed when the formulas are dragged across game week columns.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A league table uses RANK and AVERAGE with OFFSET formulas to award points by weekly ranking, and the user drags formulas across columns for the next game week.
- Observed behavior
- Dollar signs in absolute references are locking cells that should move, so the formulas do not adjust correctly when dragged to the next game week.
Open the workbook in WPS Spreadsheets and identify the cells containing the RANK and AVERAGE/OFFSET formulas. Click a formula cell and review the formula bar to see which references are absolute and which are relative before editing.
Correct Mixed References in RANK, AVERAGE, OFFSET, and COUNTIF Formulas
Use this when formulas are dragged or copied across game week columns and only selected references should move while others remain fixed.
RANK, AVERAGE, OFFSET, and COUNTIF all support mixed references. A dollar sign before the column letter locks the column; a dollar sign before the row number locks the row. Remove or keep dollar signs only on the part of the reference that must remain fixed.
Click the cell that contains the first game week RANK formula, such as C2, so the formula is visible in the formula bar.
Replace the formula with =RANK(C2,$C$2:$C6,0). This lets C2 adjust when dragged while $C$2:$C6 keeps the ranking range anchored in column C.
In the cell that averages points, enter =AVERAGE(OFFSET($A$15,C9,0,COUNTIF($C$9:$C13,C9))). The C9 references move across columns while $A$15 and the COUNTIF range column remain fixed.
Use the fill handle or copy and paste the formula into the next game week columns, then check the updated cell references.

Inspect Relative and Absolute Reference Behavior with Show Formulas
Use this when you want to compare formula references before and after dragging to avoid editing the wrong dollar sign.
Use WPS Spreadsheets to Fix RANK and OFFSET Formula References
Open the workbook in WPS Office Spreadsheets, edit the RANK and AVERAGE/OFFSET formulas in the formula bar, and use the fill handle to drag the corrected mixed references across game week columns without losing Microsoft Excel compatibility.
- 1. Open the league table in WPS Spreadsheets: Launch WPS Office, click Menu > Open, and select the existing .xlsx workbook that contains the league table.
- 2. Edit the RANK formula in the formula bar: Select the first game week cell, click in the formula bar, and enter =RANK(C2,$C$2:$C6,0).
- 3. Edit the AVERAGE/OFFSET formula: Select the corresponding average cell and enter =AVERAGE(OFFSET($A$15,C9,0,COUNTIF($C$9:$C13,C9))).
- 4. Drag the fill handle across columns: Click the cell, drag the fill handle across the next game week columns, and release to apply the mixed references.
- 5. Verify the results: Check the calculated points for each week and adjust any reference that should move or stay fixed.

Frequently Asked Questions
Why does my RANK formula stay on column C when I drag it across to column D?
Because the range contains a dollar sign before the column letter, such as $C$2:$C6. In WPS Spreadsheets, edit the formula bar and keep the score reference like C2 relative while locking only the range column that must stay fixed.
Can I use the same RANK and OFFSET formulas in WPS Office Spreadsheets as in Excel?
Yes. WPS Office Spreadsheets supports Excel formula syntax including RANK, AVERAGE, OFFSET, and COUNTIF, and it can open and save .xlsx workbooks with these formulas.
How do I see which references are absolute before editing a formula?
Go to the Formulas tab and click Show Formulas or press Ctrl+`. Click the formula cell to inspect the formula bar and identify dollar signs before column letters or row numbers.
What should I do if the formula still shows an error after dragging?
Select the destination cell and open Formulas > Error Checking or check the formula bar for locked references that should move. Correct the mixed references and recalculate by pressing F9.




