+ Reply to Thread
Results 1 to 8 of 8

Financial model taking forever to load

  1. #1
    Registered User
    Join Date
    09-11-2013
    Location
    Suffolk, NY
    MS-Off Ver
    Excel 2019
    Posts
    38

    Financial model taking forever to load

    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!

  2. #2
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Financial model taking forever to load

    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.
    Any code provided by me should be tested on a copy or a mock up of your original data before applying it to the original. Some events in VBA cannot be reversed with the undo facility in Excel. If your original post is satisfied, please mark the thread as "Solved". To upload a file, see the banner at top of this page.
    Just when I think I am smart, I learn something new!

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,775

    Re: Financial model taking forever to load

    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
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365
    Posts
    12,816

    Re: Financial model taking forever to load

    And how heavy is the file?

  5. #5
    Registered User
    Join Date
    09-11-2013
    Location
    Suffolk, NY
    MS-Off Ver
    Excel 2019
    Posts
    38

    Re: Financial model taking forever to load

    Quote Originally Posted by Pepe Le Mokko View Post
    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

  6. #6
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365
    Posts
    12,816

    Re: Financial model taking forever to load

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

  7. #7
    Registered User
    Join Date
    09-11-2013
    Location
    Suffolk, NY
    MS-Off Ver
    Excel 2019
    Posts
    38

    Re: Financial model taking forever to load

    Quote Originally Posted by Pepe Le Mokko View Post
    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

  8. #8
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365
    Posts
    12,816

    Re: Financial model taking forever to load


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Workbook with manu ref's taking forever to update
    By horsefish01 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-10-2016, 03:00 PM
  2. code taking forever and freezing
    By ammartino44 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-13-2015, 01:57 PM
  3. help with code that is taking forever to execute- Copying formula down, matching, deleting
    By gjwilson1216 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-18-2015, 09:40 PM
  4. Selecting an item from the combobox takes forever to load the listbox
    By howitusetobe in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-25-2014, 12:46 PM
  5. Replies: 8
    Last Post: 11-08-2013, 09:37 PM
  6. Macro Taking Forever to Complete
    By hk4kim in forum Excel Programming / VBA / Macros
    Replies: 23
    Last Post: 07-22-2013, 01:40 PM
  7. Cells taking forever to calculate
    By letangerang58 in forum Excel General
    Replies: 2
    Last Post: 05-01-2013, 12:02 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1