using a cell value as an index reference

[oceanjunkie]

Cool site.

My question is if I am using any function...for instance:

=AVERAGE(G2:G6)

and instead of the range going from G2:G6 I want to make it go to G36.
I would like to have the value 36 in another cell, let's say A1. Then
would like the original function to use the value in A1 as th
reference:

=AVERAGE(G2:G36)

where 36 is the value in cell A1

My reasoning is that I have many functions all referencing the sam
cells. However, if I would like to look at the first 50 instead o
just the first 25 cells, I would like to be able to change that quickl
rather than having to edit each function.

I tried concatenating strings together to build the function as text
but then I don't know how to evaluate that text. For instance I coul
type:

="=AVERAGE(G2:G",A1,")"

and it will show this in the cell when I press return:

=AVERAGE(G2:G36)

Is there any way to then evaluate that command?

Thanks in advance
-j

 

[Charles Maxson]

jb,

Use the indirect function like so:

=AVERAGE(INDIRECT("G2:G"&A1))

 

[Govind]

Hi,

Lets say A1 has 36, you can use this formula to count the average

=AVERAGE(G2:INDIRECT("G"&A1))

Regards

Govind.

 

[Peo Sjoblom]

I would rather use index instead of indirect since it is non volatile

=AVERAGE(G2:INDEX(G:G,A1))

Regards,

Peo Sjoblom