# How to create an indirect range reference

Highlighted
Occasional Contributor

# How to create an indirect range reference

How do I create an indirect reference to a range for an average() or Max() function?

So if I want to display the average value in a part of a column, but the part is changeable.

Today I want to show the average(B4:B11)

But next week it might be average(B4:B12)

If I could have a cell G1 containing 4

and cell G2 containing 11

Then a formula like =AVERAGE("B"&G1:"B"&G2) would allow me to change the values in G1 and G2 to calculate the average for different ranges.

That is possible, isn't it?

How?

TIA

11 Replies
Highlighted

# Re: How to create an indirect range reference

You may try something like this...

=AVERAGE(OFFSET(INDIRECT("B"&G1),,,G2-G1+1))

Highlighted

# Re: How to create an indirect range reference

That works

Can you explain what it is doing?

I understand the average bit, but not the use of offset and indirect

Highlighted

# Re: How to create an indirect range reference

OFFSET and INDIRECT are volatile. I prefer the non-volatile INDEX, like this:
=AVERAGE(INDEX(B:B,G1):INDEX(B:B,G2))
Highlighted

# Re: How to create an indirect range reference

Depending on your data arrangement, you may might arrive at your goal by tabling the data and using the totals row feature.  You can toggle the total row on/off as needed when adding more data. It will always be at the bottom.  This avoids volatile functions and references to entire columns.

Highlighted

# Re: How to create an indirect range reference

Whats the difference between volatile and non-volatile?

Highlighted

Highlighted

# Re: How to create an indirect range reference

Volatile functions are discussed in this link:
http://www.decisionmodels.com/calcsecretsi.htm
Highlighted

# Re: How to create an indirect range reference

Merged cells in Column B might be causing the error.
Highlighted

# Re: How to create an indirect range reference

As that article seems to suggest, Volatile functions recalculate on every change, whereas involatile do not.

Am I getting that correct?

If so then I definitely want a volatile function. This is a very small dataset and recalc issues are irrelevant

Highlighted

No merged cells
Highlighted

# Re: How to create an indirect range reference

The choice is yours.