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.

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.

1

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

  • He?? .... who?? – serenesat Aug 4 '16 at 7:41
  • This applies to Excel but does not apply on Google spreadsheets. – marikamitsos Aug 4 '16 at 8:00
  • It works fine to me (in the Spanish version of Google spreadsheets). – Fernando Aug 4 '16 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 Aug 4 '16 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 Aug 4 '16 at 10:20

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.