• 464K Members
• 11.3K Online
• 560K Conversations

## How to create an indirect range reference

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

# Re: How to create an indirect range reference

You may try something like this...

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

# 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

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

# 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.

# Re: How to create an indirect range reference

Whats the difference between volatile and non-volatile?

# Re: How to create an indirect range reference

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

# Re: How to create an indirect range reference

Merged cells in Column B might be causing the error.

# 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

No merged cells

# Re: How to create an indirect range reference

The choice is yours.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies