Referencing a cell using ADDRESS

[crash_override]

I have three columns of information and I want to talk the STDEV of a certain range of data in the third column based on information in the first.

So based on whatever I put into columns A-C, E1 and F1 will change to give the first and last values in column 1 that I am interested in - these are the same bounds that i am interested in analyzing in column C. So in this case I have determined that my bounds are row 2 to row 4, I only want to analyze data in rows 2, 3, and 4. So now I want to take the STDEV(C2:C4) based on what comes up in E1 and F1. Is this possible using the ADDRESS function or another similar function?

So far I tried using STDEV(ADDRESS(MATCH(E1,A:A,0),3):ADDRESS(MATCH(F1,A:A,0),3)) but this does not work.

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]7[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]9[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]10[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]11[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Andrew Poulsom]

You can use INDEX to return a reference:

=STDEV(INDEX(C1:C5,E1):INDEX(C1:C5,F1))

 

[crash_override]

You can use INDEX to return a reference:

=STDEV(INDEX(C1:C5,E1):INDEX(C1:C5,F1))

Can I get the INDEX function to return a cell reference? I thought INDEX could only return a value.

 

[Andrew Poulsom]

INDEX returns a reference which is evaluated if necessary. Did you try the formula? For more details on INDEX look at the Excel help topic.