How to create an indirect range reference

Copper Contributor

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

@Feargal 

You may try something like this...

 

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

That works

Can you explain what it is doing?

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

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

@Feargal 

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.

@Twifoo 

Whats the difference between volatile and non-volatile?

 

Volatile functions are discussed in this link:
http://www.decisionmodels.com/calcsecretsi.htm
Merged cells in Column B might be causing the error.

@Twifoo 

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
The choice is yours.