2

I'm trying to make a spreadsheet that displays the average of a row, i.e. =average(c3:al3) and the function is in column AN3.

I want to copy that function down a row (but need to do this for 560 rows) and I need it to auto change from C3:AL3 to C4:AL4 (if this makes sense). I know I can do it by hand but that would be prohibitively time consuming. I can insert a row into a column wide formula (such as =average(c3:c560) and it will auto change it to =average(c3:561)) just want to know if can do the same or do it as a bulk action for rows.

Improve this question

2 Answers 2

Reset to default
6

The easiest thing is to copy and paste; the change from c3:al3 to c4:al4, etc, will happen automatically. I would proceed as follows:

  1. Enter the formula =average(c3:al3) in AN3.
  2. Select cell AN3, press CTRL+C (or CMD+C on Mac) to copy it (the cell itself, not the formula from the formula line).
  3. Select the range AN3:AN562 (or whichever range you want to paste to).
  4. Press CTRL+V (or CMD+V on Mac).

The formula that will actually appear in the cells will have appropriate row numbers.

A mouse-based way to do the same is to drag the formula down using the dot in the bottom right corner of the cell.

Improve this answer
1

You can double click on the little square that appears when you select a cell and automatically repeats all available rows down.

Improve this answer
5
  • He?? .... who??
    – serenesat
    Commented Aug 4, 2016 at 7:41
  • This applies to Excel but does not apply on Google spreadsheets.
    – marikamitsos
    Commented Aug 4, 2016 at 8:00
  • It works fine to me (in the Spanish version of Google spreadsheets).
    – Fernando
    Commented Aug 4, 2016 at 9:26
  • @Fernando You are correct. My mistake. I will not delete my comment though as a reference to others (in case they make the same mistake as I did).
    – marikamitsos
    Commented Aug 4, 2016 at 10:10
  • Please clarify in your answer that: The above trick only works for clusters of not empty rows. This is never mentioned in the original question.
    – marikamitsos
    Commented Aug 4, 2016 at 10:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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