Calculation bottleneck in a large financial model

[lynnsong986]

I built a fairly large and complicated financial model for valuation purpose, I’m now concerned with calculation speed. A couple of questions:

Does table slows down calculation a lot?
My data is kept in tables so that other formula referring to the data will auto update when adding deleting records

Is there a sub I could use to identify the slowest calculating sheet/formula so I can modify those to make it run faster

I’ve followed rules such as using intermediate columns (breaking up long formulae), avoiding volatile functions, use faster functions such as index/match, sumifs...

Any suggestions??
Thanks in advance

 

[DRSteele]

My understanding is that using ExcelTables speeds up calculation.

Not all volatile functions slow things down. In fact, I learned the other day that, in my particular application at least, using function OFFSET was far quicker than using an expanding range - my calculation speed across 44000 rows went from over 20 minutes to under 1 minute. So try to reduce the number of expanding ranges.

I know that Excel365 Insider has a new calculation engine. Testimony from users is that large workbooks are considerably faster now. Perhaps updating your software might solve all your sluggishness. This would be especially true if you are using large or complex array formulas. Also, there are new, immensely powerful functions now (e.g., FILTER, UNIQUE and SORT) that might substitute for some of your more complex formulas.

https://blog-insider.office.com/2019/06/13/dynamic-arrays-and-new-functions-in-excel/

 

[lynnsong986]

Thank you very much for the suggestions, I’ll definitely look into them and test them out!

 

[DRSteele]

You're welcome. Come back and report on your outcomes.

I neglected to respond to your other enquiry. I don't know about any subroutines that identify formula speed. But I do know this procedure will help. Save a sacrificial copy of your workbook as another filename. Choose a formula that is under you suspicion, which is probably an entire column. Select and Copy every cell that contains the formula and Paste them back into the same cells as Values. Try forcing the rest of the workbook model to recalc by altering another input somewhere. See if it's any quicker. If there is an improvement, you've gathered some knowledge. Try the same procedure on other suspects. This might help narrow down the sluggishness to the culprits.

Bon chance!

 

[lynnsong986]

https://exceloffthegrid.com/optimize-formula-calculation-speed-part-2-timer/

I found this article, apparently Microsoft has a sub that tests calculation time for every need...I’m going to give this a try

Thanks so much for your help!!

 

[PaddyD]

The info / addin here will probably help:

http://www.decisionmodels.com/fastexcel.htm

 

[lynnsong986]

Yes indeed, very much appreciated PaddyD!