**Edit**: This question is completely revised to incorporate screenshots as suggested by the commenters.

I track student grades in Excel using a spreadsheet something like this

I often find myself writing Excel formulas that use the same range over and over again:

```
=SUM(C5:E5)/(COUNTA(C5:E5)+COUNTBLANK(C5:E5))
```

And then, I want to add just a few more columns to the range, that is, to edit it to:

Then, I have to either drag each of the boxes one at a time, or edit each of the instances in the formula itself one at a time to get:

```
=SUM(C5:F5)/(COUNTA(C5:F5)+COUNTBLANK(C5:F5))
```

But is there a way to drag all of them at the same time? (From the answers, apparently not.) This becomes more important as the formulas become more complex, e.g:

```
=(SUM(AF5:AO5)-IF(COUNTBLANK(AF5:AO5)>0,0,MIN(AF5:AO5)))/(COUNTA(AF5:AO5)+COUNTBLANK(AF5:AO5)-1)0
```

This is a formula for dropping the lowest grade. I copy-paste this formula once for each row (for each student in a class). Then, if I want to add an assignment, I need to edit every single copy of the range AF5:AO5 to be AF5:AN5.

Is there a way to re-write this formula to only use the referenced range once? I prefer to use a single cell with no VBA for simplicity.

**Research**:
I can't seem to find anything remotely related on Google. The closest I could find was this discussion of how to edit selections, not formulas.

@Michthan mentions named ranges with an offset, but that does not allow the same name to be used for multiple student records.

`OFFSET`

...) – Máté Juhász Feb 24 '16 at 15:14