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?

Thank you for any help you can provide.

Rich
 
Ad

Advertisements

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
 
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
 
Ad

Advertisements

G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top