Excel 2007 : Merge row index with a cell reference

[Div111]

Hi I am trying to use an Average function but I am a bit confused with the syntaxis.

=AVERAGE(D2:D & 'C2')

Cell c2 has a numeric value, which I want to put next to D. Say range d2:d5 but instead of 5 I want to put a reference to cell c2 which returns the value of 5. So whenever c2 changes Average also changes. Thanks a lot in advance.

[pike]

Hi Div111
Welcome to the forum,
you can use indirect to return the value/reference of a string
=AVERAGE(INDIRECT("D2:D"& C2))

[Div111]

Thanks a lot Pike! it really helps

[pike]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save