Indirect to non-volatile

[pto160]

I am just wondering why Microsoft have not made the indirect function non-volatile. In 1997, they changed the index function to non-volatile.

 

[DonkeyOte]

Depending on INDEX construct it has been said that INDEX is Semi-Volatile.

With INDEX the resulting range is always contained within the explicitly referenced range - the same is not true of INDIRECT or other Volatiles such as OFFSET.

For ex.:

=INDEX(A:A,10)

though the resulting range of A10 is not singularly referenced within the function it's obvious to Excel that whatever the resulting range is it must reside somewhere within Col A and that range is explicitly referenced.

If you consider:

=INDIRECT("A10")

the resulting range is not referenced explicitly anywhere either singularly or by virtue of encompassing range - "A10" is after all just a text string (not a range).

Offset is obviously slightly different in so far as range references are used:

=OFFSET(A1,9,0)

however the resulting range is not necessarily contained within the range that is explicitly referenced.


I've always looked at things along the lines of:
wherever the precedents are not explicitly referenced singularly or by virtue of parent range the resulting function will invariably be Volatile.

Another Volatile ex. we found recently was SUMIF where the optional sum range dimensions are set such that it is smaller than the dimensions of the pre-requisite range, eg:

=SUMIF(A1:A10,C1,B1:B10)

is not Volatile and nor is

=SUMIF(A1:A10,C1,B1:B20)

whereas

=SUMIF(A1:A10,C1,B1)

is Volatile.

Though all three would generate the same result the first two are not volatile given all precedents are explicitly referenced ... the 2nd has additional unwarranted precedents (B11:B20).
The third is Volatile* given the precedent range is implicit.
(*in more recent versions - in early versions it is not Volatile but changing B2:B9 would not invoke a recalc. - ie construct would be open to error)

As I see things MS adopt a "better safe than sorry" approach with implicit precedents - an approach I agree with myself I have to say.


Hopefully others will have a more definitive answer for you.

 

[pto160]

DonkeyOte, I never knew any of this before. Thanks for the great answer. So, is there ways to create dynamic ranges without it being non-volatile? Most people resort to indirect.

 

[T. Valko]

is there ways to create dynamic ranges without it being non-volatile?

Yes

A very simple example:

A1 = x
A2 = y
A3 = p
A4 = v

You want to create a dynamic range for column A.

=$A$1:INDEX($A:$A,COUNTA($A:$A))

 

[DonkeyOte]

And as inferred before the argument is that the prior INDEX construct can be regarded as being semi-volatile (volatile at workbook open).

If you're interested in volatility and optimisation reading through Charles Williams' site is an excellent way to spend a few hours...

http://www.decisionmodels.com/calcsecretsi.htm

The above details the various Volatile functions mentioned, INDEX & SUMIF "oddity" etc...

 

[pto160]

Thanks. I will check out the website. I just remember using indirect for 20k rows and the calculations were very slow.

 

[DonkeyOte]

My general rule of thumb with INDIRECT is that from an in-cell function it is really only warranted if

a) the sheet name within a link is variable - a dynamic cell ref. is not important
(the same holds true for a dynamic file name if INDIRECT being used with open external targets)

or

b) the sheet name being linked is constant but the sheet itself may be physically deleted & replaced at any given time


I would say in nearly every other scenario an INDEX based approach would suffice... and also that if a) can be avoided it should be.

The impact of Volatility is all relative though - it will depend on the volume & nature of the Volatile calcs, the model itself (ie how many volatile actions are taking place) etc etc... as a general rule though most would say avoid Volatility where possible - you never know how things will develop and what may start as an insignificant issue may become something else entirely.

 

[pto160]

Thanks for the explanation. Can I tell volatility by pressing shift and F3? I know when I use indirect and use shift+f3, it tells me that it is volatile.

 

[DonkeyOte]

Not bullet proof (see SUMIF construct as detailed earlier)

I tend to use Charles Williams VolatileFuncs.xls file to test for Volatility in a given function - see VolatileFuncs.zip as detailed on the earlier link.

 

[pto160]

Thanks DonkeyOte. I am going to check out the website and download the Volatile function zip file.