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]

Is there a way to get a function working only when its arguments change but not when sorted and filtered?

I imagine the answer is yes, if you don't have a static variable that increments on each call to the function.

 

[eros]

That I don't have a static variable which increments on each call to the function, do NOT change the fact that the function is called, no matter through argument change, sorting or filtering.

That's the failure part: My function needs to be calculated on each argument change, but when I also want that table (full of rows of the very same function) filtered and sorted as per the function results, all functions which were just calculated are recalculated again in vain.

This is exactly what I am trying to fix, recalculation of already calculated functions by sorting and filtering. And as I said earlier, when I both sort and filter at the same time weird things happen and system crawls...