I apply a function, such as SUM(A10:A20) to a set of data.When I give A21 a value I would like SUM(A10:A20) to automatically update to SUM(A11:A21), following the last X number of cells in a row/column.Example:| Day 1 | Day 2 | Day 3 | Day 4 ||   1   |   1   |   1   |   1   ||   2   |   2   |   2   |   2   ||   3   |   3   |   3   |   3   ||   4   |   4   |   4   |   4   ||   5   |   5   |   5   |   5   ||       |   6   |   6   |   6   ||       |       |   7   |   7   ||       |       |       |   8   || 15    |   21  |   27  |   33  |The four days represent the state of a single column during 4 days, as opposed to four separate columns.The last row, is the sum of the last 6 values in the row. So day one it, takes the 5 available rows: =SUM(A2:A6), day 2 there's six rows, so all get included: =SUM(B2:B7), day 3 there's seven rows, so the oldest one gets skipped: =SUM(C3:C8), and the fourth day, there's eight rows, so the oldest two gets skipped: =SUM(D4:D9)If I enter a new value on a daily basis, the above requires me to manually adjust the argument to SUM daily. I would like to find a way to automate that process.Here's a spreadsheet with the same info as above: example
How to have function input row/cell automatically update in response to new data?
google spreadsheets