Dynamic arrays and "loop" functions without using vba

[Beinleif]

Hello,
I've been playing around with dynamic arrays and spill functions trying to find out a way to get the information seen in column H.

I have length data in column A and a data value in column B (could be anything, i.e height parameter, salinity, burial depth etc. at that specific length).
I want to look at the data in column B with a lower resolution than column A, hence the "step length". In column D and E I've simply marked the data that corresponds to a length <=2, <=4, <=6 .... <=2*n. Column H is the average of the data corresponding to the different length sections.
I tried to use the filter function =FILTER($B$2:B22;$A$2:A22<=D2) which spills the data with a length <=2 [m] in cells E2:E4 followed by =AVERAGE(E2#) in cell H2. This is however not a viable solution since the number of data points in each length section will vary. The actual length data in my case would be more like 0, 0.2, 0.21, 0.9, 1.8 etc. hence between 0 and 2 [m] there could be perhaps 12 rows of data and between 2 and 4 [m] there could be 3 rows of data. Since i don't know how many rows the filter function will spill in column E I can't manage to create column D.
Any advice on how to do this in excel without vba?

In vba I would have done something like;

lastrow=22 '(xlenduprow somethingsomething)
steplength=2
counter1=1
counter2=0
tempdata=0

for i=2 to lastrow

if cells(i,1)<=steplength then

tempdata=tempdata+cells(i,2)
counter2=counter2+1

else

newarray(counter1,1)=steplength*counter1-2 & "-" & steplength*counter1 '(same as column G. Could also just be counter1)
newarray(counter1,2)=tempdata/counter2 '(average data per length section, same as column H)
counter1=counter1+1
tempdata=0

end if

next

 

[Norie]

Why not use a pivot table?

 

[Beinleif]

I'm not sure if a pivot table would give me the flexibility I need. If only column A and B exists can a pivot table give me column G and H?
As far as I can see I can use column A as a filter in pivot and very manually select length = 0, 1 and 2 and get the corresponding average data from column B. However this is only one length section (0-2 [m]). How would i get access to all the length sections? I can't do that manually if column A goes from 0 to 10 000 [m].
When i have column G and H I want to further manipulate it. It could be graphs etc.

 

[Norie]

You could add the Length column as a row item and average of Data as a data item then you can add grouping for the Length group.

It might not give you the exact results you've shown, but I'm not 100% sure how you arrived at those.

Book1
	A	B	C	D	E	F
1	Length	Data			Row Labels	Average of Data
2	0	0.1			0-1	0.15
3	1	0.2			2-3	0.35
4	2	0.3			4-5	0.55
5	3	0.4			6-7	0.75
6	4	0.5			8-9	0.95
7	5	0.6			10-11	1.15
8	6	0.7			12-13	1.35
9	7	0.8			14-15	1.55
10	8	0.9			16-17	1.75
11	9	1			18-20	2
12	10	1.1			Grand Total	1.1
13	11	1.2
14	12	1.3
15	13	1.4
16	14	1.5
17	15	1.6
18	16	1.7
19	17	1.8
20	18	1.9
21	19	2
22	20	2.1
Sheet1

 

[Beinleif]

Ah, have not used the "grouping" in pivot table. That would give me column G and H, however the lower and upper limit in grouping seems to be like this:
lower<=data<upper but I can work with that. That is why your pivot table has slightly different avg data values than my spreadsheet.
Thank you for this solution