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])
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.
- I agree. Although, I need to have the Helper column (with 1 or 0) placed in the File Review sheet (where all trades are listed) so that I can then filter this table to display only trades with a 1. Problem I am having is that I do not know how to get the AGGREGATE function to work, because there is a many to one relationship. Meaning, sheet File Review has many rows of trades from the same stock which is listed only once in sheet StratCalcs (the sheet that will be filtered prior to filtering the File Review sheet). Not sure if this helps to explain things. If not, I can attach a step by step graphic to show what I am doing.
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])- Sergei - Yes, this does help! I have been trying many ways. I was also able to get everything to work faster without a helper column by using a COUNTIF statement to produce the 1/0 column. The trick was to just Sort the stocks on the StratCalcs sheet then calculate the COUNTIFs on the File Review sheet then go back and Filter the StratCalc sheet. Produces the same result as what you did (faster calculation but more clicking). Both methods were faster than the original complicated and processor intensive SUMPRODUCT/OFFSET statement. Thanks so much for your effort. I believe I will use it. Bottom line - it still takes quite a bit of time when there are many thousand stock records, but your method is a little faster. Mainly, my computer is very old and I need to upgrade to a newer faster system. Thanks for everyone's help.
