Financial model taking forever to load

[198four]

Folks,

I've expanded a really comprehensive financial model which includes tons of INDEX and MATCH formulas. I had no choice because the way the model was built by previous person, that seemed to be the right way to expand it for our purposes. However, the problem now is that the model takes forever to load....and i mean good 10-15 minutes to just load. I have another computer with an SSD and it loads much faster on it...say about 3-4 mins...is there anything i can do to make it load faster? What am i doing wrong here? I did not think that it would take this long to load even with so many array formulas. I have set the formulas to "manual" and even then wasn't able to resolve it.

HELP!!!

TIA!

[JLGWhiz]

I am not positive this will work, but it might. Code goes in the ThisWorkbook code module. The Idea is to set the calculation to manual until the workbook loads and then reset it to automatic. It is likely your volatile formulas that are causeing the loading problem and this would hopefully let the hard data load before starting the automatic calcs.

Please Login or Register  to view this content.

If it does not give satisfactory results, just delete it.

[MrShorty]

Lookups and array formulas are a common source of computation bottlenecks in spreadsheets that can slow down the loading of a spreadsheet. You have not given us any specifics to work with, so we cannot make specific recommendations. However, I will point to this thread where -- by paying careful attention to lookups and duplicated effort -- we took a sheet that claimed to take a week to calculate and got it down calculating within a few seconds https://www.excelforum.com/excel-pro...lculation.html

[Pepe Le Mokko]

And how heavy is the file?

[198four]

And how heavy is the file?

File is about 5 MB....Let me try my best to describe the file

1- I have a tab wiith a waterfall rows for contract phasing in different quarter...e.g. Q12020 10%, Q22020 15% etc
2- There are 3 different contract types i.e. Large, medium, small and each contract type has different phasing hence separate waterfall
3- In another tab, I have index formulas that multiply contract amt (inputs) to phasing

Hope that makes sense

[Pepe Le Mokko]

Perhaps https://www.techrepublic.com/blog/mi...rkbook-bloat/r would be a start ?
Also try to get rid of volatile functions if any

[198four]

Perhaps https://www.techrepublic.com/blog/mi...rkbook-bloat/r would be a start ?
Also try to get rid of volatile functions if any

that first link seems to be broken

[Pepe Le Mokko]

http://www.techrepublic.com/blog/mic...orkbook-bloat/