I can explicitly reference cell G42 or range A42:G42.

But what if I need something dynamic?
For example: I want to write an expression in cell G200, which would calculate the average of cells A42:G42, without explicitly writing A42:G42, but by taking the value of cell G201, which is 42, finding "G" by seeing in which column the current cell is and finding "A" by going seven columns back from column G.

Is this possible?

1 Answer 1


This can be done by using indirect in R1C1 notation. The following formula matches your situation:

=indirect("R" & G201 & "C" & column()-6 & ":R" & G201 & "C" & column(), False)

The range is described by a string formed by concatenating

R<row number>C<column number>:R<row number>C<column number> 

The row numbers are taken from G201, the column numbers are computed with the column() function that returns the column number of current cell. (By the way, A is 6 places to the left of G, not seven)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.