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.

Could you tell us why 2-4 start from row 2 to row 13, and 14-16 starts from row 14?
Also, why 2 & 4 and 14 & 16?

Originally Posted by bebo021999

Could you tell us why 2-4 start from row 2 to row 13, and 14-16 starts from row 14?
Also, why 2 & 4 and 14 & 16?

(Note: These details refer to the example sheet for the calculations, not the graphic example which I included in my edited post to show my goal but where I decided to keep the number of rows to a minimum - thus with different figures)

For each row in the range 2:13, I want to put the average of the values fetched from rows 2:4. This will be my preceding zero value for correction of measured values with interpolated zero.
In additional columns, I will do the same calculations for the average of the values fetched from rows 11:13 which will be the succeeding zero, but I didn't include that in the example sheet as that will be a simple copy/paste job. In the example sheet, I wanted to focus on the numerical aspect and show all different numerical approaches that I tried so far.

From row 14, I have reset the zero level and thus any consecutive values will have the average of values in rows 14:16 as preceding zero value instead. That is why there will be new row referral numbers from here.

I hope this is clear enough I updated the example sheet with some coloring and a comment about the zero-level reset.

I cannot follow this at all. Partly because there are too many words and partly because there are too many non-working formulae all over the place.

Strip it RIGHT back to basics. Delete everything except the column you want to average and then unambiguously define the parameters.

1. You want a average from contiguous regions of column A. Yes?? No??

2. What defines the START of each range (it might be that the criterion is zero...)

3. What defines the END of each range (it might be 3 rows down from the zero...)

In all 3 cases. What is the loigc?

Originally Posted by Glenn Kennedy

I cannot follow this at all. Partly because there are too many words and partly because there are too many non-working formulae all over the place.

Strip it RIGHT back to basics. Delete everything except the column you want to average and then unambiguously define the parameters.

1. You want a average from contiguous regions of column A. Yes?? No??

2. What defines the START of each range (it might be that the criterion is zero...)

3. What defines the END of each range (it might be 3 rows down from the zero...)

In all 3 cases. What is the loigc?

Firstly, thank you for your interest in helping me! I hope I will be able to find another way to express my intentions and also ask some questions to make sure we understand each other.

I think we have a misunderstanding here, caused by some Excel compatibility problem. You say I have non-working formulae - whereas for me all formulae are giving the desired results. Thus, you got the impression that I'm asking about columns B and C, but these are manual input, although entered array-wise as {=ROW($A$2)} etcetera (so that I can insert rows without having to update the row numbers).
For some reason, the formulae in columns B and C are not working for you - is this correct?

I attach a new Excel sheet which is basically the same, but where I have plain values for columns B and C, so we can focus on the solution for column D.

Addressing your specific questions:
1. Yes. In column D (in the example sheet), I want averages from regions of column A. For each row, this region is defined by columns B and C in the form of row number.

2+3. The START and END of each range are manual input in columns B and C. I would need far more words to explain the logic, but I give it a try in italic below although I believe this doesn't affect the solution to my problem:
The criterion for START is that "from this row on, we have a new starting point for interpolation of some parameter that cannot be monitored at the same time as other measured parameters". This might be zero-level check, or zero-adjustment (which means preceding zeros above should be excluded!) or, in other applications than baseline-interpolation, something like new velocity level checked between measurements of another parameter. Accordingly, the END of each range is the last row over which to take this average. It might be START row + 2, but I want to keep this more general.

Does the new Excel sheet work for you?

I'm working in Excel 365 and what I am looking for is a formula that will calculate

Please Login or Register  to view this content.

but I want the formula to "spill" over to cells downwards. Thus AVERAGE cannot be used in the normal way, as this will take the average over the entire range and give one resulting value, instead of an array. I gave formulae for several approaches that still cannot "spill" in columns E, F, H and I. Do these work for you now - and do you know how to write a formula that can "spill" giving the same results?
I really hope that you don't see the swedish function names. Like so many others, I'm of the opinion that Microsoft should never have offered anything but the english function names...

In the sample file in post #6, i understand set of row 3 and 5 from row 3-11. But A12-A14, which is 0.2,0.3,0.3, with 3 preceding zeros, why did not row 12:14 refer to 12 and 14?

Originally Posted by bebo021999

In the sample file in post #6, i understand set of row 3 and 5 from row 3-11. But A12-A14, which is 0.2,0.3,0.3, with 3 preceding zeros, why did not row 12:14 refer to 12 and 14?

The "preceding zeros" for those rows are 0.0, 0.1 and 0.0 (from row 3:5). I will have additional columns for the "concluding zeros" which will be 0.2, 0.3 and 0.3 for the entire range 3:14, but the principle will be identical, so I omitted that. My question is how to obtain, on each row, the average from column A, for the range fetched from columns B and C, using range arguments so the formula will "spill".
I already do the interpolation x=x0+(x1-x0)/(t1-t0)*(t-t0) - where x0 and x1 as well as t0 and t1 (for a time column) are these desired "preceding" and "concluding" averages - using formulae with arguments of type A3#, it is just the averaging of a specified range for which I haven't found a satisfactory formula. (So for the moment, I've temporarily settled for allowing up to 6 columns, which makes my sheet unnecessarily large but at least allows me to stick to "spill" formulae, so I can insert rows without having to worry about updating formulae)

The problem is that functions like AVERAGE already take a range as argument, and thus, at best, interpret my request as if I want the average over the total range of ranges. Please see the attempts I made in columns E to I. For me, they all give the correct results, but they are not of "spill" type.

Originally Posted by bebo021999

In the sample file in post #6, i understand set of row 3 and 5 from row 3-11. But A12-A14, which is 0.2,0.3,0.3, with 3 preceding zeros, why did not row 12:14 refer to 12 and 14?

Still waiting reply for this

Originally Posted by bebo021999

Still waiting reply for this

The answer to that question is: row 12:14 still belong to the first set of measurements, or should we say "trajectory", with the first set of "preceding zeros" 0.0, 0.1, 0.0 marking the START of this trajectory.
If it wasn't for the zero adjustment after 0.2, 0.3 and 0.3 (with new zeros for the next trajectory), it would have been arbitrary to view rows 12:14 as the end of the first trajectory or the beginning of the next one, but because of the zero adjustment back to 0.0/-0.1 we will have a new trajectory (for interpolation) from there.

There WILL in fact be a reference in rows 12:14 to rows 12:14 as well, but that is for the "concluding zeros", marking the END of the trajectory. So row 12:14 will have references to both 3:5 (preceding zeros) and 12:14 (containing the concluding zeros). However, the formula for averaging the concluding zeros will be "identical" to the formula for averaging the preceding zeros, so I thought I did exactly what is asked of me in the forum: strip the sheet to a minimum, only asking for help with the formula in column D (and including some example formulae in columns E:I that illustrate where I got stuck).

Are they alway group of (0,0,1) or (0,0,-1) with 2 zero's and 1 (or -1)?

So my question boils down to:
Is there any way to create an array formula, which, from input ranges A3#, B3# and C3#, creates (by "spilling") a fourth range D3# with values corresponding for each row to the average of "A"&TEXT(B3#,"0")&":A"&TEXT(C3#,"0") ? (where A3# could stand for e.g. A3:A1000 etc)
(The problem being that the AVERAGE function, and many others already taking a range as input parameter, will bunch together all ranges to one and return a single value instead of a range)

Is it possible to delete this entire thread and start over with a new thread that also could be helpful to others? I think this thread is a little clogged with details as to why I want this formula, so that even if we come to a solution that helps me, it would've been nicer for the community if we had a clean thread.