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.

A	B	C	D	E	F
1	5	3		2	5
2	7	4
4	9	3
5	10	5
6	11	3

<tbody>
</tbody>

 

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