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

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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Rich" wrote in message
...
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

=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


--

Regards,

Peo Sjoblom



"Rich" wrote in message
...
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

One way:

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

In article ,
Rich wrote:

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

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

L
3
:
L
20

then:

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

--
Gary''s Student - gsnu200741


"Rich" wrote:

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

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

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Rich" wrote in message
...
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

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

"Gary''s Student" wrote:

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

L
3
:
L
20

then:

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

--
Gary''s Student - gsnu200741


"Rich" wrote:

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