# Function Range

G

#### Guest

I am looking for a way to reference a cell to define a function range. That
is the standard function =AVERAGE(L3:L20) I would like to have the 3
and the 20 sourced from other cells. The goal is to be able to change all
the average ranges in a spreadsheet by changing just one â€œreferenceâ€ cell.
My mind would have it look like this

=AVERAGE(L(=A1):L(=A2)) and cells A1 and A2 would define the range.
And I could change all formula ranges, that references cell A1 and A2 with
just an input to these two cells.

Any ideas?

Rich

B

#### Bernard Liengme

The INDIRECT function is what you need.
With G1 holding the value 3 and H1 the value 20 (Of course, any cells can be
used)
I used =AVERAGE(INDIRECT("L"&G1&":L"&H1)) to achieve your objective
best wishes

P

#### Peo Sjoblom

=AVERAGE(INDEX(L:L,A1):INDEX(L:L,A2))

you could also use offset and indirect but those formulas would be volatile
and if possible avoid volatile functions since they will slow down the
workbook and annoy you with their prompting of saving the workbook even if
you don't change anything in the workbook

J

#### JE McGimpsey

One way:

=AVERAGE(OFFSET(L1,A1-1,0,A2-A1+1,1))

G

#### Guest

You can make ALL the parts references. If A1 thru A5 contain:

L
3
:
L
20

then:

=AVERAGE(INDIRECT(A1&A2&A3&A4&A5))

G

#### Guest

Hi Bernard, AWESOME! I went ahead and gave it a try and it worked great.
This will save me so much time and headache. Thank you and best regards!
Rich

G