The bizarre phenomenon - Sorting and Filtering Rows of Functions without having to cause Functions to be recalculated on each filter and sort

[eros]

Hi everyone,

The title is the shortest I could ever have imagined to describe a very strange phenomenon I have discovered while sorting and filtering a table of function returns and it just causes continuous loops and the system eventualy (and quickly) hangs up due to excess cpu use and the fan truly gets crazy.

Each row calls the same function but uses different parameter data located again on the same row. I belive this is what causes continous loops and system crashes when I both filter and sort, but why is that? What I am aiming to achieve is to get rid of unnecessary recalculation of functions when filtering and sorting so that the system stays intact and does what I mean to.

I provide a simplified Excel file here so that you yourself could try and see what was described below.
https://rapidshare.com/files/821729708/SortOfFunction.xlsm


Let's go one by one:
1) Only Filtering: When I just filter by pressing the filter button, all functions are calculated once. You can see that as the function return value increases by 1. I can understand that because all lines are moved subsequent to filtering and so do the parameters to functions and therefore I belive Excel thinks all parameters to respective functions have been changed and Excel eventually recalculates all functions and returns values.

Question 1: Why all functions are recalculated again even if no rows are replaced after the very first filter when I press the filter button second (and third and forth and so on) time? I need to get rid of that. That consumes cpu time unnecessarily.

2) Only Sorting: When I just sort by pressing the sort button, all functions are calculated once. That's fine, but sorting consecutive times results in recalculation of functions again and again. See how function returns increase by 1 after each sorting.

Question 2: Like the first question, why all functions are recalculated again even if no rows are replaced which could otherwise change the function parameter values and could cause a reasonable and necessary recalculation of functions?

Notice, when some rows are hidden, (after first pressing filter button) if you sort the table by pressing the sort button, only the functions on visible rows are recalculated. All hidden rows are not updated. You can see that by unfiltering and comparing funtion return values. That makes sense though. Only visible rows that are effective are recalculated by Excel in that case.

Here is the fun part:
3) Both filtering and sorting: This is when my whole computer gets crazy. It hangs up and fans starts running very noisily. I imagine both filtering and sorting causes some sort of infinite loop somehow. Why is that and how to eliminate this?

I hope I am clear enough. Please see the attached Excel 2010 file and my trial of deactivating Automatic calculation and Screen update as a solution in order to get rid of the problem, both of which don't help unfortunately.

Many thanks in advance.

 

[Andrew Poulsom]

Filtering is a volatile action as explained here:

Volatile Excel Functions -Decision Models

I can't reproduce the behaviour you describe in point 3.

 

[eros]

Hi Andrew,

Thanks for your prompt return.

The behaviour I describe can be seen on my original Excel file, in which there are nearly 400 rows and 20 columns to sort and filter. Since the file is huge I was unable to put it here.

However, a similar behaviour can be seen in the attached Excel file. If you randomly and continuously click both sort and filter On/off buttons, after some time (the exact timing varies vastly but within a few minutes in general) all buttons start becoming inactive. They won't do sort or filter eventually. I really don't know what is causing this but I truly get rid of that.

If filtering is a volatile action as you described, is not there a way to turn it into non-volatile? Truly, from a perspective of filtering and sorting of function returns, after filtering and sorting most of the 400 rows do not change their position nor their function parameters and therefore there is no need to get these functions to be recalculated.

Many thanks again.

 

[eros]

Moreover, what about sorting: Is it a volatile action as well? What causes on each press of the sort button all functions to be recalculated even there are no change on the table after sorting which would otherwise cause recalculation of functions?

 

[Andrew Poulsom]

To make filtering non-volatile set calculation to manual.

 

[eros]

Unfortunately, that's not an option. I must rely on autocalculate.

Is Sorting a volatile action? What causes all functions on each (visible) cell to recalculate even though no change both on colocation of rows nor funtion arguments subsequent to sorting. Not mentioned in the ref you provided but behaves so as volatile. Perhaps that's where the reason for failure I experience resides.

 

[eros]

Unfortunately, I see this thread has stopped drawing attention. I am uttlerly helpless on the issue.

 

[Andrew Poulsom]

If the formulas update when you sort, then sorting is volatile. Why do you want a function whose result increments each time it is called, but not when you filter or sort?

 

[eros]

It is the behaviour I need for my VBA algorithm to act properly.

 

[eros]

... Also the function is calculated once already when its arguments change. So no need for the very same function to be recalculated again when the table the functions reside is sorted and/or filtered. It just returns incorrect results because functions calculate 2nd time.

This is rather annoying and creating huge problems of performance and reliability of VBA generated results. As you wonderfully summarize my problem in just one simple sentence:

"Is there a way to get a function working only when its arguments change but not when sorted and filtered? - Just a brilliant and concise question...

Thank you Andrew