Previous Rows Calculation

[tpowell55]

Hello,

I want to calculate the previous 7 cells, before the formula cell. But my formula cell is changing weekly, so it carriers the previous 7 cells plus the 1 added per week.

So how can I have a formula that does just a set prevoius number of cells??

Thank you.

[DonkeyOte]

It sounds as though you're physically inserting a row between the last prior cell and the formula, correct ?

And you wish to always sum the last 7 cells irrespective of whether or not new rows are inserted, correct ?

If so - there are a few options... you could for ex. use OFFSET

Assume current sum cell is A8:

=SUM(OFFSET(A8,-1,0,-7,1))
(you can tweak the height and width if you need to account for possibility of fewer than 7 records to sum)

Or, you can use INDEX which unlike the above is non Volatile

=SUM(INDEX(A:A,ROW()-7):INDEX(A:A,ROW()-1))