INDEX - volatile or not?

  • Thread starter Thread starter T. Valko
  • Start date Start date
T

T. Valko

I have a sample file that contains just 1 formula:

=IF(COUNT(B2:S2)=0,"",IF(COUNT(B2:S2)<5,AVERAGE(B2:S2),AVERAGE(SMALL(S2:INDEX(B2:S2,LARGE(IF(B2:S2<>"",COLUMN(B2:S2)-1),5)),{1,2,3,4}))))

This formula averages the 4 lowest values from the last 5 values in the
range, or, if there are not 5 values, averages what's available.

Whenever I open the file and then attempt to close the file without do
anything whatsoever, I'm prompted to save changes.

I know this subject has been hashed before and I believe the consensus
opinion is that INDEX is not volatile. But, there you go!

Biff
 
Biff,

Certainly if you replace the INDEX() function with a constant then it stops
prompting you to save the changes so it would appear that the INDEX()
function is act like a volatile function but on the other hand if you have
the INDEX() function on its own then is does not prompt - strange. I wonder
if it is possible that it is volatile under some circumstances but not under
others?

It was hard enough remembering what is volatile and what is not but if it is
going to be changing with circumstances it is going to be impossible.

Anyway it is way past my bed time but I will be interested to see what the
experts make of it.


Well done in finding it.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
Did the obvious:

Entered an Index() formula in a new WB ... saved ... closed ... opened ...
closed ... received *NO* save prompt.

Entered your formula, and then *did* get a prompt!?!?!?

So, waiting for someone to venture a theory!
 
My theory:

In the formula I posted the Indexed range is being "built" through
calculation. It's not a static predefined range.

Biff
 
I wonder if it is possible that it is volatile under some circumstances but
not under others?
Click to expand...

My theory:

In the formula I posted the Indexed range is being "built" through
calculation. It's not a static predefined range.

Biff
 
If you test the formula using the technique in VolatileFuncs.xls
(download from http://www.decisionmodels.com/calcsecretsi.htm)

it shows up as non-volatile when you press F9, but looks as though it is
volatile when the workbook is opened: in other words it gets calculated once
when the workbook opens but does not get recalculated at each subsequent
recalculation unless its precedents change.

Maybe this is caused by Excel having to build the dependency chain at open
time?

Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com
 
Hi Charles!

That seems reasonable.

Biff

Charles Williams said:
If you test the formula using the technique in VolatileFuncs.xls
(download from http://www.decisionmodels.com/calcsecretsi.htm)

it shows up as non-volatile when you press F9, but looks as though it is
volatile when the workbook is opened: in other words it gets calculated
once when the workbook opens but does not get recalculated at each
subsequent recalculation unless its precedents change.

Maybe this is caused by Excel having to build the dependency chain at open
time?

Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com
Click to expand...
 
Back
Top