Nov 06 2019 03:56 AM
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
Nov 06 2019 04:07 AM
Nov 07 2019 01:51 AM
That works
Can you explain what it is doing?
I understand the average bit, but not the use of offset and indirect
Nov 07 2019 06:19 AM
Nov 07 2019 06:54 AM
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.
Nov 08 2019 01:30 AM
Nov 08 2019 05:15 AM
Nov 08 2019 05:17 AM
Nov 15 2019 04:56 AM
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