Forum Discussion

Feargal's avatar
Feargal
Copper Contributor
Nov 06, 2019

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 ...
excel
Formulas and Functions
Twifoo's avatar
Twifoo
Silver Contributor
Nov 07, 2019
OFFSET and INDIRECT are volatile. I prefer the non-volatile INDEX, like this:
=AVERAGE(INDEX(B:B,G1):INDEX(B:B,G2))
Feargal's avatar
Feargal
Copper Contributor
Nov 08, 2019

Twifoo 

Whats the difference between volatile and non-volatile?

 

    • Feargal's avatar
      Feargal
      Copper Contributor
      Nov 15, 2019

      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

      • Twifoo's avatar
        Twifoo
        Silver Contributor
        Nov 15, 2019
        The choice is yours.

Resources