0

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

Grading Spreadsheet

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:

enter image description here

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.

  • 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...) – Máté Juhász Feb 24 '16 at 15:14
  • How do you post example sheets? Screenshots? If something else, can you link to a question that illustrates? – Josiah Yoder Feb 24 '16 at 16:46
  • Use a named range. – Kyle Feb 24 '16 at 18:36
  • 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? – Josiah Yoder Feb 24 '16 at 19:30
  • @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. – Kyle Feb 24 '16 at 22:07
1

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

Starting from the original worksheet:

Three Columns

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)

select F5

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

Name Manager 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:

Select data

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:

Make reference relative

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

enter image description here

=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:

enter image description here

Thanks again to @Kyle for suggesting this solution.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.