Convert Offset Formula into Non-Volatile (Index/Match)?

I'm trying to simplify a cumulative expense calculation for financial modeling into one cell instead of creating a full waterfall. After lots of googling, I pieced together something that works using this formula:

=IF($R$3,SUM(OFFSET(O3,,,1,-MIN($R$3,COLUMNS($B$3:$P$3))))/$R$3,)

- Row 3 = annual expense
- Row 4: =IF($R$3,SUM(OFFSET(O3,,,1,-MIN($R$3,COLUMNS($B$3:$P$3))))/$R$3,), this calculates the cumulative expense for all previous years
- R3 = # of years to lookback

Workbook attached. To summarize: P4 needs to be the sum of the 20% (1/R3) of each of the previous R3 years of expense. So it will need to look-back from periods 10-14. And this lookback period is changed by the # of years. The current formula seems to work.

But I understand Offset is a volatile function and my workbook has really slowed down since starting using it (I re-enter/re-calculate thousands of cells many times). Is there a way to adjust this using Index/Match or some other formula?

It will need to handle changing the number of years in row R3
And preferably have some error checking built-in as not every calculation will have the full 15 periods of data. If not available, it will need to start at the first period which is greater than the # of years in in R3.

Try

in C4

=IF(C2>$R$3,B4+C3-INDEX($A$3:$P$3,,COLUMNS($A$4:C4)-$R$3),B4+C3)

I also believe F5 value is wrong as only it effectively only adds 4 years A:E but A is not a value.

Should the first result be in G5 (SUM(B3:F3)/R3) ????

My apologies, in my rush to extract the data from my actual spreadsheet, I made an error in the sample workbook and conflicted 2 different steps. Updated/correct one is attached (couldn't figure out how to edit the original post).

I removed the formulas and hardcoded how the data would look, I need to have Row 4 do all of the math in rows 6-10 so that it matches row 11. It boils down to a SUM last X periods / X (thanks JohnTopley)...with the wrinkle that once the remaining # of periods is less than X, it needs to adjust to only sum the remaining periods.

So essentially how can I sum the last X number of cells in a row, while adjusting for cases where the number of cells remaining is less than X?

And avoiding volatile functions.

For Depreciation (based on last file)

in B4

=IF(B$2<=$R$3,"",SUM(INDEX($B$3:$P$3,,COLUMNS($B$4:B$4)-$R$3):INDEX($B$3:$P$3,,COLUMN(A5)-1))/$R$3)

copy across