Help Find Averages based on column for different row ranges

[Argento_Nuclear]

Hi! I am trying to find averages for certain time steps based on the column name. I am trying to find a sort of moving average but it needs to be done per sensor. A simple example of what I am trying to do is below.

Time	S1	S2	S3	S4	S5	S6	S7
1	3	5	18	1	8	16	81
2	681	6	81	6	81	8	1
3	682	8	68	5	681	7	9
4	1	9	8	11	6	8	44
5	648	1	86	6	48	35	7
6	89	84	4	4	684	48	8
7	684	168	4	48	486	486	48

<tbody>
</tbody>

I want to be able to find the average in column s2, s3 and s6 from time 1-3 and 5-7. Because I have to fill out a table like this below:

Column Name	AVG t=1-3	Std. Dev t=1-3	Avg t= 5-7	Std.Dev t= 5-7
s2
s3
s6

<tbody>
</tbody>

Is there a way I could use one formula and autofill or use minor modifications to autofill the rest of the table? Ive tried indexing and matching but I cannot seem to get it to work using row ranges.

Thanks!

 

[steve the fish]

Put your first table in A1:H8. Then set up the second like this in A10:B14.

	1
	3
s2
s3
s6

<colgroup><col style="width:48pt" width="64" span="2"> </colgroup><tbody>
</tbody>

This does average:

=AVERAGEIFS(INDEX($B$2:$H$8,,MATCH($A12,$B$1:$H$1,0)),$A$2:$A$8,">="&B$10,$A$2:$A$8,"<="&B$11)

This, which requires CTRL-SHIFT-ENTER to enter, does standard deviation:

=STDEV.S(IF($A$2:$A$8>=B$10,IF($A$2:$A$8<=B$11,INDEX($B$2:$H$8,,MATCH($A12,$B$1:$H$1,0)))))

 

[Argento_Nuclear]

If I format my table as a table with headers, could I do the same thing using the time values as ranges? I have a very similar table already set up but they are just in different sheets.

 

[Argento_Nuclear]

I've tried this and it did not work. Get #value !

 

[Argento_Nuclear]

Anyone?

 

[steve the fish]

It does work if you follow the instructions. Which formula gives the value error?