Get the range of the last X rows in Google Sheets

Question

Say I have a spreadsheet with 18 rows, and I can hard code the range of the last 7 values in column D and average their values like so:

=AVERAGE(D12:D18)

How then could I do the same without hard coding them, so it'll work as I add more rows?

Answer 1

If you want to calculate the average or sum, there's no need for a script. You can accomplish this with an array filter as well. The following formula calculates the average over the last 7 rows in the A column:

=AVERAGE(FILTER(A:A;ARRAYFORMULA(ROW(A:A)>COUNT(A:A)-7+1)))

This assumes that the data starts at row 1. Otherwise you have to change the latter constant in the formula to the row number where the data starts.

Answer 2

You can also use OFFSET() as this Webapps.SO answer does for aggregating over the last X cells of a row.

For the opposite---aggregating the last X cells of a column---the OFFSET params just need moving around. Here's a command should should work for your example (this assumes your data starts at D2 and goes to D18):

=iferror(average(offset($D$2, max(0, count($D$2:$D18)-7), 0, 7, 1)), 0)

Answer 3

Turns out you can use your own code. Messy, but it works:

function lastValues(column, num) {
  var lastRow = SpreadsheetApp.getActiveSheet().getMaxRows();
  var values = SpreadsheetApp.getActiveSheet().getRange(column + "1:" + column + lastRow).getValues();

  for (; values[lastRow - 1] == "" && lastRow > 0; lastRow--) {};

  arr = [];

  for(i=0; i < num+1; i++){
    arr.push(values[lastRow - i]);
  };

  return arr;
};