Get last X values from a table starting from the current row in Excel

I would like to make a formula to retrieve a range of cells inmediate up of the current one.

For example, in this table the SUM 5 column has to calculate the SUM for the last 5 values (including the same cell).

I tried with OFFSET but I don't seem to find the right approach.

Would you mind pointing me in the right direction?

Solution 1:

In C2, formula copied down :

=SUM(OFFSET(B2,,,-MIN(5,MATCH(B2,B:B,0))))

Solution 2:

Or not use INDIRECT() at all.

Use =SUM($B2:$B6) for the formula in D6, then copy down. It will always be the five cells in column B starting in the cell's row and counting upward four more cells. So B3:B7, B4:B8, B5:B9, and so on down the column.

If you have a normal situation, it doesn't really matter except for being easier to understand. If you have a huge number of rows to work with, it will likely make a visible difference in calculation time.

And it's very easy for anyone looking under the hood to understand and be sure it's doing what is desired.