Formula Errors

How to Adjust WPS Office Spreadsheet RANK Formulas When Dragging Across Columns

Kushani NimanthikaKushani Nimanthika Nov 13, 2026 869 views

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.
Before you start

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.

Solution 1Recommended

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.

1
Select the current formula cell

Click the cell that contains the first game week RANK formula, such as C2, so the formula is visible in the formula bar.

2
Edit the RANK formula references

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.

3
Edit the AVERAGE/OFFSET formula references

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.

4
Drag or copy the formulas across game week columns

Use the fill handle or copy and paste the formula into the next game week columns, then check the updated cell references.

Correct Mixed References in RANK, AVERAGE, OFFSET, and COUNTIF Formulas
Reference check: After dragging, select each new formula cell and confirm that only the intended references moved and the anchored references stayed fixed.

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. 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. 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. 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. 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. 5. Verify the results: Check the calculated points for each week and adjust any reference that should move or stay fixed.
Fixes RANK, AVERAGE, OFFSET, and COUNTIF reference issues in spreadsheet league tables.Maintains compatibility with Microsoft Excel .xlsx, .xls, and .csv files.Provides a familiar formula bar and fill-handle workflow for mixed references.Free and lightweight alternative for editing and auditing formulas.
microsoft office alternative - wps office

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.