Questions about Strange ReCalc Behavior

I'm currently struggling with an MOAS (that's "Mother of All Spreadsheets") 
that someone else at work has given to me.

It's 28 MB and takes several seconds to re-calculate. I'm in the process of 
trying to diagnose ways in which I can make it more efficient (Ultimately 
I'll need to run it as part of a Monte Carlo simulation--25,000 recalcs, so 
every second is important). 

I've run into a couple of peculiar Excel behaviors. Can anyone explain these?

1) I can do an F9 recalc on any sheet. However, there are a couple of sheets 
(out of maybe 15) which, if I try to do a "Sheet Recalc" via Tools...Options 
Calculation Tab, causes Excel to calculate indefinitely. (Or so it 
seems...the percentage number on the status bar doesn't change, but the Excel 
process shows 99% CPU use in Task Manager.) I discovered this when I was 
trying to do individual sheet recalcs in a VBA macro to diagnose calculation 
bottlenecks.

Anybody know why this happens or what I can do to fix it?

2) When I am connected directly to my office network, the recalc time for my 
workbook is around 3 seconds. If I run it at home using the same machine, 
while disconnected from the office network, the recalc time is 11 seconds. 
The workbook has no external references whatsoever.

Does anyone know why this disparity occurs?

3) And finally, can anyone point me to resources available to diagnose and 
speed up Excel calculations? I've seen the Knowledgebase and MSDN articles. 
Are there diagnostic software tools or other sources of information that 
would help me make this thing more efficient?

Sorry for the long post. Thanks for the help.
0
12/4/2005 9:58:02 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
238 Views

Similar Articles

[PageSpeed] 26

For speed issues, visit

www.decisionmodels.com

-- 
Kind regards,

Niek Otten

"LawrenceHG" <LawrenceHG@discussions.microsoft.com> wrote in message 
news:A21A6956-A183-4D35-B203-D0599E91391E@microsoft.com...
> I'm currently struggling with an MOAS (that's "Mother of All 
> Spreadsheets")
> that someone else at work has given to me.
>
> It's 28 MB and takes several seconds to re-calculate. I'm in the process 
> of
> trying to diagnose ways in which I can make it more efficient (Ultimately
> I'll need to run it as part of a Monte Carlo simulation--25,000 recalcs, 
> so
> every second is important).
>
> I've run into a couple of peculiar Excel behaviors. Can anyone explain 
> these?
>
> 1) I can do an F9 recalc on any sheet. However, there are a couple of 
> sheets
> (out of maybe 15) which, if I try to do a "Sheet Recalc" via 
> Tools...Options
> Calculation Tab, causes Excel to calculate indefinitely. (Or so it
> seems...the percentage number on the status bar doesn't change, but the 
> Excel
> process shows 99% CPU use in Task Manager.) I discovered this when I was
> trying to do individual sheet recalcs in a VBA macro to diagnose 
> calculation
> bottlenecks.
>
> Anybody know why this happens or what I can do to fix it?
>
> 2) When I am connected directly to my office network, the recalc time for 
> my
> workbook is around 3 seconds. If I run it at home using the same machine,
> while disconnected from the office network, the recalc time is 11 seconds.
> The workbook has no external references whatsoever.
>
> Does anyone know why this disparity occurs?
>
> 3) And finally, can anyone point me to resources available to diagnose and
> speed up Excel calculations? I've seen the Knowledgebase and MSDN 
> articles.
> Are there diagnostic software tools or other sources of information that
> would help me make this thing more efficient?
>
> Sorry for the long post. Thanks for the help. 


0
nicolaus (2022)
12/4/2005 10:20:01 PM
Thank you, Niek. It looks like there are a lot of information there on 
Excel's calculation engine.


0
12/5/2005 12:53:02 AM
Reply:

Similar Artilces:

Redeploy question regarding databases
We just finished the redeploy of CRM 1.2 to 3.0 in a new server. Everything is good but we need help with SQL. The databases need to be updated with the data that employees enter in the old server running 1.2. What is the best way to do it? Do the table structures of both Databases are the same? ...

A question about Outlookexpress backup file (.dbx)
Hi, there. Is there anybody here know how to recover the compact Outlookexpress5 backup files (.dbx file)? Thanks a lot! "Jerry" <chance912@hotmail.com> wrote in message news:18e201c4a252$09d330b0$a301280a@phx.gbl... > Hi, there. Is there anybody here know how to recover the > compact Outlookexpress5 backup files (.dbx file)? Thanks a > lot! From where? Jerry <chance912@hotmail.com> wrote: > Hi, there. Is there anybody here know how to recover the > compact Outlookexpress5 backup files (.dbx file)? Thanks a > lot! DBX files are NOT "Outlook...

Exchange OWA question
All, Two questions on OWA: 1. Do I need a Front End server to enable OWA from outside for my users? I only have one Exchange 2003 server running Windows 2003. 2. When I access OWA from inside my network, the public folders are view on a different screen. I am not able to use the move items option to move or copy items from my inbox to a public folder as they are not available in the Pick Folder list. Can this be changed? Thanks, Rafael 1. No; OWA can run on that server - but be cautious in allowing connections to the server. I'd be sure to use SSL connections only. 2. I thin...

Question: Before you Buy
Hi All, Concerning the new Microsoft Money version, may be coming from Quicken Deluxe 2006. Question: For the newest version of Microsoft Money, does it support Direct Downloading from Banks? Question: For Backing up your Data Files that you are working with, does Microsoft Money support backing up to DVD Media that you can re-write to, like DVD+RW Disks that are pre-formatted with Nero's InCD program, so you can use the DVD Disks more than one time? -- thecreator In microsoft.public.money, thecreator wrote: > > > Question: For the newest version of M...

Simple Question
Can anyone tell me how to prevent the annoying 'Security Warning' form from appearing everytime I open/load a new database file in ACCESS 2003. Go to: Tools | Macro | Security and set the dialog to Low. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. <trekgoes2malaysia@hotmail.com> wrote in message news:b80879f9-3e62-4e20-b234-6691e32afffe@s12g2000prg.googlegroups.com... > Can anyone tell me how to prevent the annoying 'Security Warning' ...

forwarding question for exchange 2k3
I have a new Exchange 2k3 server up and running. I am looking to add email addresses for folks in our sister company and route the messages to them. For example I wish to have johndoe@xyz.com (my domain) route to johndoe@xyz.de (sister domain). Can this be done without creating full mailboxes for those users? Thanks JP You can add Active Directory contact objects into your domain for the sister company. If there are shedloads you can consider using something like SimpleSync www.cps- systems.com to do regular synch ups between the two domains. If the two domains are only liked via the...

Strange error message when loading a collection with an item of user defined type
Hi, I want to store an unknown number of records, read from a txt data, into a Collection. Each record is made of 26 strings, so I defined a user-defined type Record: Const NLines As Long = 26 Type Record Lines(NLines) As String End Type Dim MyRecord As Record I also defined a Collection object to store all the record: Dim List As Collection Set List = New Collection However, when I read a record and then try to load it into List: Data.Add Item:=MyRecord I get this incomprehensible (for me) error message: "Only public user defined types defined in publ...

I have a question about using a function in a form
I have used the "workingdays2" function in a query to calculate the number of working days between two days like so... WorkingDays2([SLA_Date3],[SLA_Date4]) I have tried using this in a text box in a form but the calculation won't work. Any ideas? Another stupid moment. Date4 was earlier than Date3, duh! "SteveM" wrote: > So I assume WorkingDays2() is a user-defined public function? > SLA_Date3 and SLA_Date4 are fields on your form? > > Try putting = sign first: > > =WorkingDays2([SLA_Date3],[SLA_Date4]) > > Steve > > &quo...

SFO connection question
On SFO I am connected to a MSCRM server 1. Now I want to connect to another server. Where do I have to specify the server ? Or is it only at the client installation time I can specify it ? Does that mean I have to uninstall and install again. Please help. There are registry entries under hkey localmachine, software, microsoft,mscrm which point to the web server. I have never tried changing the web server address. As long as the new web server is still using the same database it may work. Honestly I would reinstall "Arch" <anonymous@discussions.microsoft.com> wrote in mes...

A Tricky Question! Macro & Chart
Hi All I have a very tricky problem here. I have a column chart which has sa 4 columns - namely, A, B, C and D. First Issue - I would like to check is it possible to assign a specifi macro for A, B, C, and D to their respective columns? Second Issue - And as each macro has its own codes, would it b possible for the macro to be so smart that if I click on the column fo A, A macro will run... Please note that the order of the columns will change over time as will sort the values and put the one with the highest value as th first column. Kindly advice me whether the above are possible.....

excell sorting question
does anyone know how to do a sort for how close a list of numbers com to a designated cell with a index number in that cell?? example: a 39 b 40 c 51 d 44 e 43 sort by being the same or the closest to "44" results should be: d 44 e 43 b 40 a 39 c 5 -- Message posted from http://www.ExcelForum.com Twosix, You can add a helper column... In the column next to that one (assume A1:A5) enter =ABS(44-A1) and drag down to fill the series. Select both columns and choose Data -> Sort and sort according to the helper column. Dan E "twosix >" <<twosix...

MultiCurrency Question
Hi: I am in a situation where a canadian based company whose Products and Product List is in Canadian Dollars wants to create a Quote in US dollars. It seems that we cannot add the products with Canadian Dollars Currency in a USD Price List when we are using a Percent method of pricing. If that is the case, how can it be handled? I cannot create 2 sets of the same Product because the exchange rate is not fixed. Typically, CRM should be calculating the currency in USD using the exchange rate that's provided, but CRM simply is refusing to allow me to add a product with a different c...

strange error message on start up: Office 2004
HI Everybody, I just installed MS Office 2004 on my new iMac. When I open a new word document I get a dialogue box saying: "An unexpected error occurred while trying load Microsoft Framework Library." Is there any way I can get this message to stop? Thanks!! Your installation of Office needs to be updated. Locate the AutoUpdate app that was installed into your Applications folder & run it to download/apply the updates beginning with 11.3.5 on through 11.4.0 - Did you not get a prompt about updating during the installation? -- HTH |:>) Bob Jones Office:Mac MVP <gr...

Question about modal userform.....
My userform has various controls which allow the user to enter data. (i.e textboxes, calendar control, etc...). I load the userform as follows: Userform1.Show ' ' Code here runs after userform is closed! ' My question is....will the Visual Basic stop and wait at the "Userform1.Show" line until the user is done entering data and they close it out??? I'm just worried that the form is loaded and that VBA continues exiting the code below the "userform1.show" line. thankx No, you are okay, the code flow is passed to the code in the fo...

Question about win32 exe format.
Is there a method or class/construct in MFC which can give me information about an Win32 exe file? I've done *some* reading and have learned *some* stuff, like determining if a file IS a Win32 exe, but I was wondering about all the other stuff that you get when you right click on an exe and select 'properties' such as 'Description', 'Copyright', 'Comments' etc. If nothing in MFC, can someone provide a link/article/resource where I might learn such info? Paul The values you are referring to have nothing to do with knowing how to read the executable. Y...

Strange Pivot Behavior
I read a post regarding the identical problem that I have, but the possible solution does not work for me: I have an Excel 2002 datalist, with many columns. The fields of importance for my purposes are Client(A1) Purchase Price(E1) Paid Amout(F1) Balance Due(G1) Client records repeat innumerable times, with varying Purchase Price and Paid Amounts. The Balance Due is a calculated field =Purchase Price - Paid Amount =E2-F2 in G2 and is filled down. The calculation yields appropriate and correct amounts with the three ranges formatted with the Accounting format. I t...

Average Question Again
I am trying to average a column of numbers on Control Group 1 say I1 t I300. Some cells are empty. I am using sheet 1 to do my averaging. Fo what ever reason I keep getting error messages. I am not sure why i won't calculate. Formating is set to number. I have tried severa suggestions from a previous post. I am open to any and all suggestions. Please Help ASAP!!!! Thank You In Advance Mik -- mikeee ----------------------------------------------------------------------- mikeeee's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1461 View this thread: http:/...

Odd Behavior
I type a number into a cell and nothing shows up. I hit enter and the number shows up. Can't figure out why. On Nov 21, 2:16=A0pm, JimS <jim...@msn.com> wrote: > I type a number into a cell and nothing shows up. =A0I hit enter > and the number shows up. Can't figure out why. Although I have no idea myself, you are missing some critical information. Actually, I do have one idea. A WAG: I wonder if the cell font color is the same as the background initially (usually white). In that case, you will not see what you type in the cell itself, although it will appear in th...

Sales Process Question 08-02-06
Is it possible to create a sales process based where stage progression is based on a field value - for example a pick list rather than the status of an activity? I want to create a pick list with sales stages and have a process evaluate the picklist value before progressing to the next stage. ...

Question on organizing large amounts of small Excel Files
I have a question regarding how I can best organize large amounts of excel worksheets so that they are more managable. I am using Excel 2002, and my problem is that the tool logs for the machine that I work with are getting out of hand. The machine stores process information in Comma Delimited form that we download into folders on our network drive. Usually we generate about 1500 logs per year. Currently, my Engineer has me open each individual file and format it. I created a small micro for this to make it easier, but it is still time consuming. Is there a way to apply a micro to large a...

Questions on Document/View architecture
Hi, I've gone over several books that I bought off-shelf on MFC. But even now, I can't understand what the document is used for. The most confusing element is OnOpenDocument and serialize For onopendocument, the book indicates that you can initialize array elements and other stuff. However, what happens when I want to load stuff from disk amd initialize it on Onopendocument, I guess it is coming from the serialise method. But I did not find a place you can find the file's name.... Please help. Jack Jack wrote: > Hi, > I've gone over several books that I bought o...

Excel question...
I've got a monthly report that I have to run, based off of data coming from a Unix server via Simba Serv. Once I have the data, I've got formulas I run-one column plus or divided by another column equals data I enter in a new column & so-on. My question is can I automate this & if so, how? Macros? VB Script? Looking for a nudge in the right direction. Thanks in advance- Hi the best way may be to start with recording a macro whilde doing this manually (goto 'Tools - Macros - Record macro). After this have a look at the created code and clean it up. A good starting point ...

POST A QUESTION
Formula is in cell A3 which is =A1+A2. If I don't put any numbers in A1 or A2 cell A3 displays #value! Is there a way to not have the #value! show in cell A3? Hi, if you are doing a sum shouldn't give you that result anyway you can use =if(A1="",A2,if(A2="",A1,A1+A2)) "dwolf" wrote: > Formula is in cell A3 which is =A1+A2. If I don't put any numbers in A1 or A2 > cell A3 displays #value! Is there a way to not have the #value! show in cell > A3? Check to see if A1 or A2 is formatted as something other than a number, o...

Investment account question
Hi I'm trying to setup a Pension Account and find this quite confusing. I hope that you can assist me. I want to track the "Account Value" but do not want this to be reflected in the "Total Account Balance". Is this possible? I would just like to track the monthly contributions - is this done via "Bills and Desposits"? Thank you I'm not sure Investment Account is the best bet for a pension, but there are infinite varieties of pension accounts, so maybe it is appropriate for your case. Are we talking a 401(k)/403(b) type of account? A defined ...

Matrix/array question
I am trying to demonstrate the effect of changing scores on a cumulative total. My table has 7 categories. Each category has a weight factor of between 1 and 4, and can receive a score between 1 and 5. I want to create a table showing all the possible combinations of scores, to show what effect any change in score in one category will have on the total score. I realize that I will get 78,125 results, but I can then summarize them to show the overall effects. What formula would give me these results, or is there even a way to do it? My categories are a2:a8, weights are b2:b8, and scores ...