Sorting and Filtering of Functions

[eros]

Hi everyone,

I have discovered a very strange phenomenon 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/3775133...fFunction.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. I cannot eliminate AutoCalculate as I stronly rely on it.

Many thanks in advance.

[JosephP]

I'm afraid your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

[eros]

As per the moderator's demand, here is the original post I initiated the thread with.

http://www.mrexcel.com/forum/excel-q...ml#post3300506

There must be someone who noticed that problem on earth.

Many thanks in advance.