Function Range

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

Thank you for any help you can provide.

Rich

 

[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

 

[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

 

[JE McGimpsey]

One way:

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

 

[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))

 

[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

 

[Guest]

Hi Gary, Thank you for your added information. Your reply gives me even
more flexibility. Again, thank you and all the people that replied and
educated me on these solutions! Best regards, Rich