INDEX - volatile or not?
 

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

INDEX - volatile or not?
 

Here is the bible on what is and is not volatile...

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

"Some are volatile in some versions of Excel but not in others:
INDEX()became non-volatile in Excel 97"
--
HTH...

Jim Thomlinson


"T. Valko" wrote:

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

INDEX - volatile or not?
 

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


with @tiscali.co.uk


"T. Valko" wrote in message
...
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

INDEX - volatile or not?
 

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!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


"T. Valko" wrote in message
...
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

INDEX - volatile or not?
 

This from MS:

http://support.microsoft.com/kb/274500

INDEX()became non-volatile in Excel 97"

I'm using Excel 2002. I believe it is the way Index is being used. For
example, this formula doesn't cause a prompt:

=INDEX(A1:A10,MATCH(MIN(B1:B10),B1:B10,0))

Biff

"Jim Thomlinson" wrote in message
...
Here is the bible on what is and is not volatile...

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

"Some are volatile in some versions of Excel but not in others:
INDEX()became non-volatile in Excel 97"
--
HTH...

Jim Thomlinson


"T. Valko" wrote:

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

INDEX - volatile or not?
 

My theory:

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

Biff

"Ragdyer" wrote in message
...
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!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


"T. Valko" wrote in message
...
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

INDEX - volatile or not?
 

I wonder if it is possible that it is volatile under some circumstances but
not under others?

My theory:

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

Biff

"Sandy Mann" wrote in message
...
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


with @tiscali.co.uk


"T. Valko" wrote in message
...
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

INDEX - volatile or not?
 

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

"T. Valko" wrote in message
...
My theory:

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

Biff

"Ragdyer" wrote in message
...
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!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


"T. Valko" wrote in message
...
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

INDEX - volatile or not?
 

Hi Charles!

That seems reasonable.

Biff

"Charles Williams" wrote in message
...
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

"T. Valko" wrote in message
...
My theory:

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

Biff

"Ragdyer" wrote in message
...
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!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
---------------------------------------------------------------------------


"T. Valko" wrote in message
...
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