aggregate array based on condition

[alosled]

I am trying to get this done with a single array formula:

For row in a column array, if the array row tests positive for a condition, calculate the average of the next N rows in a different array of equal size. Each of these averages would be placed in an array, and then the average of this array would be taken.

This is the most promising thing I've been able to come up with so far:

=AVERAGE(AVERAGE(INDIRECT(IF($G$1:$G$16=2,CONCATENATE("AVERAGE(",ADDRESS(ROW($H$1:$H$16)+1,COLUMN($H$1:$H$16)),":",ADDRESS(ROW($H$1:$H$16)+2,COLUMN($H$1:$H$16)),")"),""))))

It puts the text of: average of the proper sub array I want to calculate.

But I am trying to get the actual values instead of the text. Or alternatively if there is an easier/smarter way to do this I am open to that as well.

Any help is much appreciated.

 

[XOR LX]

Hi.

Obviously it's quite difficult to work with your rather esoteric formula, since we don't have the associated context/values.

Why don't you create a very small mock dataset, e.g. 10 rows' worth, paste the necessary column entries into this thread and add your expected result? Once you have a solution for that dataset, I'm sure you'll be able to adapt it to suit your real data.

Regards

 

[alosled]

Thank You. Apologies for formatting I am still not accustomed.

In the first column is "test" data. the second are the data values. When a row in the first column is 2, i take the average of the following two rows from the second column. I place this average in the in the third column. (since 2 shows up twice in the first column there are two values in the third column. Finally I calculate average of the values in the third column (underlined at the bottom, 5.5 is the answer I am expecting).



[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]2[/TD]
[TD="width: 64, align: right"]7[/TD]
[TD="width: 64, align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]21[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65, align: right"]5.5[/TD]
[/TR]
</tbody>[/TABLE]

 

[XOR LX]

Thanks.

This works for that example:

=SUM(AVERAGEIF(A1:A16,2,OFFSET(B1:B16,{1,2},)))/2

though your first post would suggest that the number of values to be considered is variable, not fixed.

Can you clarify, perhaps indicating what the solution for the dataset you provided would be if this value were to change?

Regards

 

[alosled]

Yes I was hoping that it would be general, so that I can take the average of the values of the next N rows.

 

[alosled]

I am looking for something generic that could be applied to the next N rows.
Yes for the case of average of the next three rows this would be a result:

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]2[/TD]
[TD="width: 64, align: right"]7[/TD]
[TD="width: 64, align: right"]13[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]21[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65, align: right"]8[/TD]
[/TR]
</tbody>[/TABLE]

 

[alosled]

Thanks for your help. I adapted your formula to make it sufficiently generic: by replacing {1,2} with row(indirect("1:n")).

This is good enough although I am always hesitant to use SUMIF or AVERAGEIF, type functions.

Regards.

 

[XOR LX]

You're welcome, though I cannot for the life of me think why you might be hesitant about using such functions.

Regards

 

[alosled]

well actually now I just came up with an example. I want to take the standard deviation formula also, and this formula cannot be adapted for that.

 

[Domenic]

For your average, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

=AVERAGE(SUBTOTAL(1,OFFSET(B1:B16,SMALL(IF(A1:A16=2,ROW(B1:B16)-ROW(B1)+1),ROW(INDEX(A:A,1):INDEX(A:A,COUNTIF(A1:A16,2)))),0,2)))

1 - the number 1 in red means that you want an average (see the help index for other functions available for SUBTOTAL)

2 - the number 2 in blue means that you're looking for that number in Column A

2 - the number 2 in green means that you want to average the "next 2" rows

For the standard deviation, can you provide an example, as per your original post?