Formula referencing cells depending on another cell's value

[EngrStudent]

I have a spread sheet with four columns of data. Column A is a list of numbers. I need to take the average over a specific range of cells in Column A and place that average in Column D. Column B & C tell me the range of cells I need the average between. For example if B1 = 3 and C1 = 7, then D1 = AVERAGE(A3:A7). However, I need a way to write this formula referencing B1 and C1 so I don't have to manually type the formula for every Column D cell. Something like D3 = AVERAGE(A[B3]:A[C3]) imagining that [X] took the value in the cell.

Clear? I had trouble explaining this one to a friend. Thank you.

 

[Andrew Poulsom]

Try:

=AVERAGE(INDIRECT("A"&B1&":A"&C1))

or the non-volatile:

=AVERAGE(INDEX(A:A,B1):INDEX(A:A,C1))

 

[EngrStudent]

Thanks a lot! That works well.

Though my Excel Sheet is a bit more complicated and "Column A" and "Column B & C" are on separate sheets.

I am using
=AVERAGE(INDEX(Sheet2!A:A,Sheet3!B1):INDEX(Sheet2!A:A,Sheet3!C1))

I think this should work.