• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Spreadsheet Slowing Down!

P

PaulFogel123

Member
My Excel forecasting program has suddenly slowed down. When I change a formula, enter a number, or even enter a letter in a blank cell, it takes at least a minute to recalculate, when it once was almost instant. Calculation is set to automatic and there are no external connections. There are no Pivot Tables or Power Queries. I haven't changed any of the settings for this file. I did add a 2000-row database tab, but it didn't increase the size of the file too much, which stands at nearly 7MB in binary format (probably 12-13MB in normal .xlxs format). Array and SUMPRODUCT formulas elsewhere capture and manipulate this data.

What could be the cause of this calculation slowdown?

Paul
 
Paul

Slowness in normally caused by poor layout and poor choice of functions

You need to avoid Volatile functions where you can and also avoid referencing Columns or Rows ("C:C") or ("1:1") as unless you have 1048576 rows of data you are checking data that you don't need to

Can you save the file somewhere, Dropbox etc, for us to look at?
 
David Evans said:
More than likely you have volatile functions in your spreadsheet - see http://chandoo.org/wp/2014/03/03/handle-volatile-functions-like-they-are-dynamite/

That should explain it ....
Click to expand...

I did a search for the volatile functions you listed, and sure enough, OFFSET is in there! And, since this was used in the last tab I created, it all made sense. I had a large, complex file with a volatile function. Surprisingly, it didn't take too many OFFSET formulas to bog speed way, way down. Thank so much!
Paul
 
Hui said:
Paul

Slowness in normally caused by poor layout and poor choice of functions

You need to avoid Volatile functions where you can and also avoid referencing Columns or Rows ("C:C") or ("1:1") as unless you have 1048576 rows of data you are checking data that you don't need to

Can you save the file somewhere, Dropbox etc, for us to look at?
Click to expand...

Surer enough, it was the OFFSET formula I recently put in that caused all the trouble. Here's the culprit: =SUMPRODUCT(B10:B50,OFFSET(Assumptions,,MATCH(B1,Dates,0)-1,,1))

The formula references a range (B10:B50) and then finds the matching range (Assumptions) by matching the date in cell B1. Is there a simple way to get rid of the OFFSET here?
 
Hi ,

Volatile functions by themselves will not cause a problem , unless :

1. There are many of them , and many means hundreds or thousands

2. The volatile functions / formulae are in turn referenced by many other formulae , which may not themselves be volatile.

Can you give more details about how many such formulae you have in your worksheet ?

What is the definition of the named range Assumptions ?

As far as replacing the OFFSET function is concerned , the following formula will return the same result as the OFFSET function :

=INDEX(Assumptions,,MATCH(B1,Dates,0))

Narayan
 
NARAYANK991 said:
Hi ,

Volatile functions by themselves will not cause a problem , unless :

1. There are many of them , and many means hundreds or thousands

2. The volatile functions / formulae are in turn referenced by many other formulae , which may not themselves be volatile.

Can you give more details about how many such formulae you have in your worksheet ?

What is the definition of the named range Assumptions ?

As far as replacing the OFFSET function is concerned , the following formula will return the same result as the OFFSET function :

=INDEX(Assumptions,,MATCH(B1,Dates,0))

Narayan
Click to expand...

I had probably 250+ OFFSET function cells in there. I copied and pasted over them with values to see if the problem would go away, and it did. Now I'm replacing OFFSET with the INDEX fix you supplied. Thank you!
Paul
 
jeffreyweir said:
@NARAYANK991 Note that one offset formula alone can cause issues, if it is at the head of a large-enough dependency tree, as per my article at http://chandoo.org/wp/2014/03/03/handle-volatile-functions-like-they-are-dynamite/
Click to expand...
Hi Jeff ,

I think I have covered that in point 2 of my comment ?
Volatile functions by themselves will not cause a problem , unless :

1. There are many of them , and many means hundreds or thousands

2. The volatile functions / formulae are in turn referenced by many other formulae , which may not themselves be volatile.
Click to expand...
Narayan
 
NARAYANK991 said:
Hi Jeff ,

I think I have covered that in point 2 of my comment ?

Narayan
Click to expand...

You did indeed cover it. I replaced all of the OFFSET formulas with INDEX. There are now no volatile functions anywhere in the workbook. I now find that a number entered in any tab is almost automatic, but the exception is my assumptions tab, in which you enter forecast inputs. It's pretty slow, if not as bad as before. Can I assume that this is because there are numerous calculations depending on those forecast inputs? I also find that although I can otherwise enter and copy numbers without delay, cut and paste takes a long time, as does inserting rows. What do you think might be the cause of that?
Paul
 
Hi Paul ,

Sorry , but off-hand I cannot think of any reason why data entry is fast while inserting rows takes time , unless a lot of recalculation happens when a row insert is done. But why would you want to do this time and again ?

Can you not work in Manual Recalculation mode , and do a full calculation with CTRL ALT F9 once all your operations are completed ?

Narayan
 
If you insert rows/columns in between existing data does the work sheet not have to recalculate all data in the sheet as there has been formula/function movement in the data.
 
PaulFogel123 said:
You did indeed cover it. I replaced all of the OFFSET formulas with INDEX. There are now no volatile functions anywhere in the workbook. I now find that a number entered in any tab is almost automatic, but the exception is my assumptions tab, in which you enter forecast inputs. It's pretty slow, if not as bad as before. Can I assume that this is because there are numerous calculations depending on those forecast inputs? I also find that although I can otherwise enter and copy numbers without delay, cut and paste takes a long time, as does inserting rows. What do you think might be the cause of that?
Paul
Click to expand...

Do you have links to external files which are on a network drive, by any chance? What else does you Assumptions Tab have on it? Have you checked for the Hui reference noted above - referencing entire columns/rows?
 
NARAYANK991 said:
Hi Paul ,

Sorry , but off-hand I cannot think of any reason why data entry is fast while inserting rows takes time , unless a lot of recalculation happens when a row insert is done. But why would you want to do this time and again ?

Can you not work in Manual Recalculation mode , and do a full calculation with CTRL ALT F9 once all your operations are completed ?

Narayan
Click to expand...
Yes, row insertion is a rare event and my users won't be able to do it anyway due to protection restrictions. My question was aimed at assessing if it's reasonable for a spreadsheet to take a while to recalculate if it lacks volatile functions but is otherwise large and very interdependent.
 
Hui said:
Paul

Slowness in normally caused by poor layout and poor choice of functions

You need to avoid Volatile functions where you can and also avoid referencing Columns or Rows ("C:C") or ("1:1") as unless you have 1048576 rows of data you are checking data that you don't need to

Can you save the file somewhere, Dropbox etc, for us to look at?
Click to expand...
I don't have functions that reference columns or rows, but I do have numerous named ranges from which SUMPRODUCT formulas in arrays are used to extract numbers from database sheets in the workbook. There are no external references, though.
 
David Evans said:
Do you have links to external files which are on a network drive, by any chance? What else does you Assumptions Tab have on it? Have you checked for the Hui reference noted above - referencing entire columns/rows?
Click to expand...
David,
There are no links to external files, and no formulas referencing entire rows or columns. The Assumptions tab has hundreds of items (expenses, rates, revenues, balance sheet, capital spending) in which users enter their assumptions for the future. For example, salaries are expected to increase by 3% in June 2017, or volume is forecast to rise 1% in October 2016, and so forth.
 
bines53 said:
Hi @PaulFogel123,
First, the computer will restart,
Secondly if you can replace the function SUMPRODUCT with the function sum,
If you can upload a file, for example, it may be possible to help, I guess you have a lot of formulas with array.
David
Click to expand...
I restarted but it had no effect. Here's a sample formula, typical of thousands in my workbook:
{IF(OR((Dates=BF5)*Data_Load_Complete),SUMPRODUCT((Dates=BF5)*Medicare_Discharges),"NR ")}

The formula finds where the data for month BF5 is complete (I used a data validation "yes/no" for this). If it's complete and final, it uses the number of Medicare discharges from a data tab in the same file; if not complete, it enters NR (for Not Reported).
 
Hi Paul ,

See the uploaded file. It follows the instructions given in the link I posted earlier , as follows :

1. It has a worksheet tab named ExecutionTimes , with the named headers in A1 and B1 ; you can copy this tab into your workbook.

2. It has a standard code module named Optimize ; you can copy this module into your workbook.

Once the above are done , run the macro named timeallsheets ; remember , it will take some time to complete execution.

Narayan
 

Attachments

  • optimizeExample.xlsm
    52.9 KB · Views: 15
Last edited:
NARAYANK991 said:
Hi Paul ,

See the uploaded file. It follows the instructions given in the link I posted earlier , as follows :

1. It has a worksheet tab named ExecutionTimes , with the named headers in A1 and B1 ; you can copy this tab into your workbook.

2. It has a standard code module named Optimize ; you can copy this tab into your workbook.

Once the above are done , run the macro named timeallsheets ; remember , it will take some time to complete execution.

Narayan
Click to expand...
Thank you for this, but I ran into two problems:
  1. I cannot right-click on a sheet tab and do anything; the menu doesn't come up anymore. I don't know what's causing this.
  2. I found Optimize, but I don't know how to copy this tab into my workbook (my VBA knowledge is limited to Record Macro). Right-clicking on Optimize didn't produce a Copy command.
 
Hi ,

For the first problem , I am not able to suggest a solution.

For the second , just select the Optimize module and drag it into your workbook when your workbook is open. Excel will do the copy correctly.

Narayan
 
NARAYANK991 said:
Hi Paul ,

See the uploaded file. It follows the instructions given in the link I posted earlier , as follows :

1. It has a worksheet tab named ExecutionTimes , with the named headers in A1 and B1 ; you can copy this tab into your workbook.

2. It has a standard code module named Optimize ; you can copy this tab into your workbook.

Once the above are done , run the macro named timeallsheets ; remember , it will take some time to complete execution.

Narayan
Click to expand...
Thank you for this, but I ran into two problems:
  1. I cannot right-click on a sheet tab and do anything; the menu doesn't come up anymore. I don't know what's causing this.
  2. I found Optimize, but I don't know how to
    NARAYANK991 said:
    Hi ,

    For the first problem , I am not able to suggest a solution.

    For the second , just select the Optimize module and drag it into your workbook when your workbook is open. Excel will do the copy correctly.

    Narayan
    Click to expand...

    copy this tab into my workbook (my VBA knowledge is limited to Record Macro). Right-clicking on Optimize didn't produce a Copy command.
NARAYANK991 said:
Hi ,

For the first problem , I am not able to suggest a solution.

For the second , just select the Optimize module and drag it into your workbook when your workbook is open. Excel will do the copy correctly.

Narayan
Click to expand...
 
You must log in or register to reply here.
Back
Top