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!?

litmus
  • 121
  • 8
  • 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 '19 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 '19 at 16:25

3 Answers3

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))
Scott Craner
  • 22,693
  • 3
  • 21
  • 25
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

cybernetic.nomad
  • 5,415
  • 12
  • 25
3

You can use INDIRECT :

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

Máté Juhász
  • 21,403
  • 6
  • 54
  • 73
  • 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 '19 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 '19 at 20:22