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
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