# Formula referencing cells depending on another cell's value

#### EngrStudent

##### New Member
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.

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

#### Andrew Poulsom

##### MrExcel MVP
Try:

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

or the non-volatile:

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

#### EngrStudent

##### New Member
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.

Replies
4
Views
641
Replies
5
Views
492
Replies
3
Views
483
Replies
3
Views
809
Replies
1
Views
739

1,191,053
Messages
5,984,374
Members
439,883
Latest member
onions44

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back