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])
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.
One other thing you might be able to do while you are working on a better solution (such as pivot tables/power query) may be to narrow the range(s) upon which the averageifs/sumifs/countifs formulas on the StratCalcs-NYSE worksheet are looking at. You could add two helper columns, Start and End, to identify the start/end index location of the stock ticker in the table on File Review-NYSE using Match (exact option) and Match (approximate option). With the caveat that the table in File Review-NYSE must be sorted in ascending order (which it appears to be).
Then, use the Index function to reference the range for just that stock ticker. For example:
INDEX(tblFileReviewNYSE[Length],[@Start]):INDEX(tblFileReviewNYSE[Length],[@End])And, you could remove the condition to test for the stock ticker, since you already narrowed the range to that particular item.
mwwoodru , glad it helped and good luck with this project.
From my point of view your model bit overcomplicated and there are ways for the optimization, from data structure to concrete formulas. But it takes time.