Forum Discussion
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
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.
- OFFSET and INDIRECT are volatile. I prefer the non-volatile INDEX, like this:
=AVERAGE(INDEX(B:B,G1):INDEX(B:B,G2)) That works
Can you explain what it is doing?
I understand the average bit, but not the use of offset and indirect
