Forum Discussion
Excessive time preforming calculations
I am indicating whether an item is found in the filtered list of items on another worksheet within the same file. It displays "1" if it is found and a "0" if not. Below is the formula. Is there another way to write the formula that can perform the calculation faster. Currently this formula is performed several thousand times (once for each unique item row in the worksheet).
=SUMPRODUCT(SUBTOTAL(3,OFFSET('StratCalcs-NYSE'!A$5:A$905,ROW('StratCalcs-NYSE'!A$5:A$905)-ROW('StratCalcs-NYSE'!A$5),0,1)),--('StratCalcs-NYSE'!A$5:A$905='File Review-NYSE'!A5))
Any Help is much appreciated,
Michael
Detlef is right, such projects better to do with data model using Power Pivot and Power Query. But that will require to rebuild everything practically from scratch.
With current workbook I'd at least to convert ranges to structured tables. That itself allows to optimize formulas and improve performance. Another variant is dynamic ranges, but that's harder in maintenance.
Some formulas could be optimized independently of above. For example, instead of calculating max of the range, keep reference on sequential numbers range and use MAXIFS to find position of max, it could be used something like =XMATCH(bigNumber, range, -1).
As for filtered rows, you may add helper column to first table as
=AGGREGATE(3,5,[@STOCK])and in next table use helper columns as
=XLOOKUP([@STOCK],tblStratCalcsNYSE[STOCK],tblStratCalcsNYSE[Helper])
12 Replies
Another way is to add helper column in 'StratCalcs-NYSE' sheet with formula as
=AGGREGATE(3,5,A5)It returns 1 for visible row and 0 for hidden one. Let say it'll be in column H, when
=SUMPRODUCT(--('StratCalcs-NYSE'!A$5:A$905='File Review-NYSE'!A5)*'StratCalcs-NYSE'!H$5:H$905)- Sergei - thank you for this suggestion. I have tried =AGGREGATE(3,5,A5). But still does not seem to do what I need. The stocks listed on sheet StratCalcs are to be Filtered (not hidden) to show only the ones that meet certain criteria. What I want to do is in sheet File Review (which shows all of the trades of all of the stocks) to be able to display (by filtering) only the trades of stocks displayed on sheet StratCalcs when that sheet has been filtered. was able to add the helper column/AGGREGATE function to sheet StratCalcs, but does't seem to make it any easier to do what I want (i.e. perform a COUNTIF on a Filtered list). The above complicated SUMPRODUCT function does, but is not good when done over 100,000+ trades. Do you have any other way I can do a COUNTIF function on a Filtered (not hidden) list? Thanks - Michael
AGGREGATE works the same way for filtered and manually hided rows. It returns 1 if row is visible and 0 if not visible.
I don't know other ways. Or with OFFSET() as in your initial formula or with helper column and AGGREGATE / SUBTOTAL (it works the same way). But OFFSET is volatile function which recalculates with any other cell recalculation which could reduce the performance dramatically.
As for COUNTIFS vs SUMPRODUCT it depends on which Excel you are. Microsoft improved performance significantly during previous 2-3 years. I don't think above functions have big difference in performance for the latest versions of Excel.
Given that there might just be another way altogether to accomplish your objective--one of the wonders of Excel is how many different ways there often are to "skin the same cat"--I wonder if we could ask you to post a copy of the actual workbook. If it's too proprietary, perhaps you could create a mock-up that replicates the situation without revealing confidential or proprietary info. (From the formula, it looks like it's something to do with the New York Stock Exchange, or data from that source.)
You're clearly not a novice with Excel--a novice would not have written that formula--but it's possible even for experts to have tunnel vision, to always do things as they've done them in the past. Excel introduces new functions and capabilities as time passes; it may be that one or two new ones would work here.
mathetes Thanks for the reply. I have attached a stripped down file for your review. There are 4 worksheets : Totals (graph of various strategy use), StratCalcs (summary of all trades by stock), FileReview (list of all trades), Review (list if various strategy data and a stock's preferred strategy). What I am trying to do through formulas is use worksheet StratCalcs to filter various stocks to display (with their related figures in the header) then have worksheets FileReview & Review to automatically indicate or show filtered display of data from those corresponding stocks (represented by column Best in each worksheet - I use "1" for displayed stock and "0" if not displayed which then allows me to manually filter those two worksheets buy the Best column).
Ever since I have used the XLOOKUP and SUMPRODUCT functions my file runs VERY slow, also file now requires a double Enter for some reason. FYI - I copied the code for SUMPRODUCT off the internet because I could not figure out a way to run an IF function on a filtered list. So although I do know some Excel I am unfortunately not an expert and would never have thought to write that code.
Your help is greatly appreciated - Michael
- I wouldn't know where to begin just in navigating through that workbook. I am sorry, but I don't have the time to spare to figure out how it all fits together. It is no surprise to me that it works slowly: there are far too many rows of data, far too many columns with complex functions, for it to do anything BUT run slowly. Maybe somebody else can put their finger on the issue......it exceeds my abilities.
