Calculation time

[Brennen81]

Hello - I am hoping someone could help me here.

I have a workbook with 4 sheets.

Sheet 1 - An area for paste-able data that contains dates and numbers. Dates in one column and numbers in the other. Around 5,000 rows (15-20 years)
Sheet 2 - Cells containing raw date values from 1993 - 2014 Ex. 3/4/1997 (This is used for a vlookup reference in the following sheet
Sheet 3 - Thousands of different calculations
Sheet 4 - Pulls values from the calculated cells in sheet 3 and is displayed in a condensed way.

After opening the workbook. I can paste new data into sheet 1 and the calculation is done in about 4 seconds. I have cell in Sheet 1 that looks at the calculated cells to tell me if there is any data worth viewing. Ex. any number higher than X. So I can keep pasting and calculating every 4-5 seconds until I know there is data I need to look at.

The issue is. After I have put in a new data set, then I go click/view Sheet 4 (or any other). From that point on, when I paste new data in sheet 1, it takes 16 seconds to calculate. This is so frustrating when I have a lot of data sets to check. It actually looks like it just hangs there for about 12 seconds and then calculates for the 4 seconds.

Would anyone know what I could do to keep the calculation running fast? Reopening the workbook of course works, but that takes a while.

I hope this was clear.

B

[rcm]

Question, do you look at each data set separately or only need to view the results after you entered all data? if the answer is yes to the second part then turn the calculation method to manual and hit f9 after you are done entering the data

[Brennen81]

I actually did try that. It still hangs for the 12 seconds. (mouse turns into a circle) Then I can click "Calculate now" and it takes 4 seconds again, just like originally opening the workbook. I do need it to be calculated and viewed each time i enter a data set.

[duncandhu]

What is the nature of the calculations in Sheet3? Maybe there is a way to optimize them so it doesn't take long to calculate.

[FDibbins]

Check the following...
use the minimum size range needed. avoid using entire columns or rows, rather, restrict to just the range you need. If the range may grow, use 2-3 times more than you think you will need.
avoid using volatile functions (like today() etc), these recalc every time a change is made to the workbook. Rather, put that in its own cell, and then reference that cell from your formulas
Keep conditional formatting to a minimum (if you have any)

show us some of the formulas you are using?

[Brennen81]

I am wondering if it is something other than the actual calculation. Because, the calculation text "Calculating ...%" you see at the bottom of the window is always about 4 seconds. Either happens right away when I first have opened the workbook and can keep changing the data and it still takes 4 seconds. It only adds the 12 seconds of stalling after I have viewed the other sheets.

Follow?


duncandhu & FDibbins: Here is a couple sample formulas.

=((BT$7-B$7)/B$7)*A1-(1-A1)*((BT$26-B$26)/B$26)

=((SUMIF(BU44:BU61,">0")-MAX(BU44:BU61))/(COUNTIF(BU44:BU61,">0")-1)*((COUNTIF(BU44:BU61,">0")-1)/17))/((AVERAGEIF(BU44:BU61,"<0",BU44:BU61))*(COUNTIF(BU44:BU61,"<0")/17))*-1


FDibbins: I do have the "name" area in the data paste area limited and not the entire columns.

[FDibbins]

Do you have any VBA code running in the background?

[duncandhu]

Here's a thought:

When you press F9 to calculate, you're just calculating the current sheet. If you press Ctrl-Alt-F9, then it calculates the entire workbook and any others that are open - try that and see how long it takes.

[Brennen81]

Hello - I have no VBA running. Not that savvy yet!

When I turn the auto calculation off, and I paste the data in, it still hangs (mouse arrow turns into a circle) for 14 seconds. I pressed Ctrl-Alt-F9 after that and it calculated very quickly as usual, 3-4 seconds.

[FDibbins]

Can you upload the workbook? (providing it contains no sensitive data, of course)

[duncandhu]

If you are pasting 5,000 rows, it's a relatively small amount of data (how many columns?). It could depend on where you are copying from, and what kind of data you are pasting as to why it hangs for 14 seconds. Any insight would be great (as would an uploaded workbook with desensitized data as FDbibbins suggests).

[Pepe Le Mokko]

Any volatile functions you can get rid of?

[Brennen81]

I really wish I could upload the workbook, but it is sensitive calculations and information from over 100 hours of work.

In the pasting area there are 4 columns and 9/10 times I am pasting into 2 of them while the other 2 remain with data. The columns are (examples in brackets):

A: Dates (1/1/1999) B: Values (10.55) C: Dates (1/1/1999) D: Values (10.55)

It hangs weather I paste fully or just paste the values. I am copying from another workbook.


What would volatile functions be?

[FDibbins]

Volatile formulas are those that calc with every workbook/worksheet change. See this link for m ore details and examples...
http://www.decisionmodels.com/calcsecretsi.htm

Basically functions like DATE() update EVERY time you do anything in the workbook - enter or edit data for instance, so they take up time and recourses