How to drag multiple instances of a range at the same time in excel

Question

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.

Solution

Going off of @Kyle's comment on my question, here is how to created a relative named range:

Starting from the original worksheet:

With this formula for cell F5:

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

Select the cell with the first formula you want to edit in the worksheet (F5 in the example)

Then, click on Formulas Ribbon, and the Name Manger button

Click on New, and enter the name for the range (e.g., quizzes). Delete everything in the Refers to box and select the data from the first row:

Now, here's the key step. To make this a relative reference, delete the $ in front of the part of the reference that should change. Since the number part changes as we go from row to row, we will delete each $ in front of the 5s:

Click OK and Close to get back to the worksheet. Now, in the first formula, use the named range everywhere:

=SUM(quizzes)/(COUNTA(quizzes)+COUNTBLANK(quizzes))

Now you have a formula using a name range that can be copied! Copy cell F5 onto cells F6:F9.

Now, you can add another column of data as usual. Just click on the first cell, return to the Name Manager, and edit the range. You don't even need to copy the cell over the other rows. All the other cells will automatically update as well, as visible by selecting them and looking at the box drawn:

Thanks again to @Kyle for suggesting this solution.

Comments

[Máté Juhász]

there is no exact way for that, but posting some sample sheets and explaining it a bit more how do you work we probably could suggest workarounds (find - replace, OFFSET...)

[Josiah Yoder]

How do you post example sheets? Screenshots? If something else, can you link to a question that illustrates?

[Kyle]

Use a named range.

[Josiah Yoder]

Can I define and use a name in the same formula? Would I need to define a named range for each row of student data?

[Kyle]

@JosiahYoder you define names using the Name Manager. Names can use relative references so if I'm understanding your problem correctly you should be able to use just one name.

[Máté Juhász]

Yes, screenshot. If you can't post picture, then upload it to imgur and post the link.

[Michthan]

@JosiahYoder you can use a named range with offset in it, so it automatically changes when you add data

[Josiah Yoder]

Named ranges sound promising. I'll have to try those. Thanks!

[Josiah Yoder]

@Michthan, this article discusses named ranges with an offset. As you describe, it automatically changes when you add data. But that is not what I'm trying to do. I'm trying to avoid having a separate name for every row of data.