Forum Discussion
Excessive time preforming calculations
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])
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
