Google Sheets – Select Range of Cells Relative to Current Cell

google sheets

I am trying to calculate the sum of a range of cells relative to the current cell. This is what I have, and it works:

=SUM(INDIRECT(ADDRESS(ROW()-6,COLUMN()-1)), INDIRECT(ADDRESS(ROW()-5,COLUMN()-1)), INDIRECT(ADDRESS(ROW()-4,COLUMN()-1)), INDIRECT(ADDRESS(ROW()-3,COLUMN()-1)), INDIRECT(ADDRESS(ROW()-2,COLUMN()-1)), INDIRECT(ADDRESS(ROW()-1,COLUMN()-1)), INDIRECT(ADDRESS(ROW(),COLUMN()-1)))

So the sum of the last 7 rows (including current one), column to the left. Is there an easier way to do this?

Best Answer

This is as simple as I could get using INDIRECT (RC notation):

=SUM(INDIRECT("R"&ROW()-6&"C"&COLUMN()-1&":R"&ROW()&"C"&COLUMN()-1,))

However using OFFSET is much shorter (B7 being the cell the formula is in)

=sum(offset(B7,-6,-1,7,1))

Just because I put B7 in the above formula doesn't mean it is not relative, it is, if you copy and paste it into any cell it will "relatively" work but if you insist on not having any cell reference then combining the two methods would produce the cleanest way:

=sum(offset(indirect(address(row(),column())),-6,-1,7,1))

Code

function autoAddRows() {
  var sh = SpreadsheetApp.getActiveSheet();
  var cRow = sh.getActiveRange().getRowIndex(), mRow = sh.getMaxRows();

  if((mRow-cRow) < 1) {
    var lCol = sh.getLastColumn(), key = sh.getRange(mRow,lCol).getValue();
    sh.insertRowAfter(mRow), sh.getRange(mRow+1,lCol).setValue(key+1);
  }
}

Remarks

var ss isn't necessary, since you don't use it in the script.
you need the row of the active cell, compared to the last row (getMaxRows)
add all the goodies into the IF statement that need to be executed, when mRow-cRow=0

Example

I've prepared a file for you to experiment with: Inserting Rows + Reference

Google Sheets – Sum Cells from Another Row Based on a Cell Value

With the magic of matrix multiplication and this solution (credit where credit is due), I have managed to wrangle it to do what you want.

=ArrayFormula(IF( J4:J = "" ; "" ; MMULT((ROW(G4:G)>=TRANSPOSE(ROW(G3:G)))*((ROW(G4:G)+1-I4:I)<=TRANSPOSE(ROW(G3:G)));G3:G)* ( ( J4:J ) / 10 ) ))

My head hurts already, so I won't go into too much explanatory detail, except to say that the key magic happens in

="MMULT((ROW(Range1)>=TRANSPOSE(ROW(Range2)))*((ROW(Range1)+1-I4:I)<=TRANSPOSE(ROW(Range2)));Range2)))"

And I've added a second sheet to your public one with a rough equation builder, for understanding or alternative applications.

Best Answer

Related Solutions

Google Sheets – Inserting Rows and Adding Cell References

Code

Remarks

Example

Google Sheets – Sum Cells from Another Row Based on a Cell Value

Related Topic