Spill/array formula to calculate average for specified ranges?

I would like to do the following with measuring data:
1) Each row contains a set of data
2) For each row of data, I want to make a reference to a range of other rows
3) For each row of data, I want to calculate the average over this associated range of other rows
4) So far I can manage this, but I would like to make the formula "spill" over the entire target array, so I don't have to worry about updating formulas when rows of data are inserted in the middle etc.

E.g., this could be a set of measuring data with multiple zero values taken on several instances, where I want to specify which range of zero values that should be associated to each row, but I have other applications as well (all for interpolating).

Value	1st pr 0	Last pr 0	1st suc 0	last suc 0	<pr 0>	<suc 0>	zero	corrected
0	1	2	7	8	0,05	0,25	0,03	-0,03
0,1	1	2	7	8	0,05	0,25	0,07	0,03
5,0	1	2	7	8	0,05	0,25	0,10	4,90
5,1	1	2	7	8	0,05	0,25	0,13	4,97
10,2	1	2	7	8	0,05	0,25	0,17	10,03
10,2	1	2	7	8	0,05	0,25	0,20	10,00
0,2	1	2	7	8	0,05	0,25	0,23	-0,03
0,3	1	2	7	8	0,05	0,25	0,27	0,03
0,0	9	10	...	...	...	...	...	...
0,0	9	10	...	...	...	...	...	...

The example Excel sheet shows the four calculating approaches I've tried so far, where every attempt to make the formula spill, using ranges like B2:B19 or G2# as parameters, has failed.
(Note that the example sheet has different row referrals, because I wanted a little more data for the example calculations.)

All my other columns in the calculations rely on "spill" so it could be a risk if one column suddenly needs manual updating. I also tried to find appropriate Control-Shift-Enter (CSE) formulas instead, but ran into the same problems there.