3

A simplified version of my problem is that on a different sheet called Sheet2 I have data that I want to average:

=AVERAGE(Sheet2!$A$1:Sheet2!$A$100)

Depending on where the code above is used I need to exchange column A for another letter, for example column D. To do this I have created a function:

=MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ",4,1)

which will output D.

My problem is that instead of having to write:

=AVERAGE(Sheet2!$D$1:Sheet2!$D$100)

I want to use:

=AVERAGE(Sheet2!$MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ",4,1)$1:Sheet2!$MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ",4,1)$100)

but Excel does not accept this. How can I use functions next to Sheet2!?

  • This could be done with INDIRECT, but there may be a better way. What determines which column you want to use? – cybernetic.nomad Oct 16 at 16:16
  • As I move to the right in my spreadsheet I want to increase by increments of 3, i.e. the first Average uses column A, second Average uses column D(=A+3), third Average uses column G (=A+3+3) etc – litmus Oct 16 at 16:25
3

You can use INDIRECT :

=AVERAGE(INDIRECT ("Sheet2!$" & MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ",4,1)&"$1:Sheet2!$" & MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ",4,1)&"$100"))

  • Thank you, Máté! This is exactly what I needed help with, I would never have figured out that its possible to use quotation marks in this manner. Thanks!! – litmus Oct 16 at 18:42
  • There is a useful Excel function that simplifies and strengthens this approach, called ADDRESS(row, column,[abs],[format],[sheet]) that produces the cell reference as text. For example, the output of ADDRESS(30,77,,,"Sheet2") is "Sheet2!$BY$30" The benfits of using this include: (1) formula that is short, simple, fast and understandable (2) handling columns > "Z", and (3) coping with localisation issues. So you can use INDIRECT( ADDRESS(.....) & ":" & ADDRESS(....) ) – Duke Bouvier Oct 21 at 20:22
5

OFFSET and INDIRECT are volatile we can use INDEX

=AVERAGE(INDEX(Sheet2!$A$1:$Z$100,0,4))

The 0 tells the INDEX to return all the rows and the 4 is the 4th column. So all the rows from 1 to 100 in column 4 will be averaged.

As per your comments:

=AVERAGE(INDEX(Sheet2!$A$1:$Z$100,0,(COLUMN(A:A)-1)*3))
3

Here is one way:

=AVERAGE(OFFSET(Sheet2!$A:$A,0,(COLUMN()-1)*3))

Note that OFFSET is volatile and will increase the calculation load in your workbook

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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