Calculation time - can't find the bottleneck

I have a large file, approx 10MB, in excel 2003. Most of the data relates to 
calculations for around 5,000 records, and the workbook takes too long to 
recalculate.  All the vlookup's have been replaced with Index / Match, but 
it's still slow, and I have a number of macros that rely upon it 
recalculating.  Based on a post here, I went to this site:
http://msdn.microsoft.com/en-us/library/aa730921.aspx
and ran the timer macros to try and isolate the problem.
The FullCalcTimer takes around 19 secs
The ReCalcTimer takes 0.00013 secs, and 
The SheetTimer takes no more than 0.015 secs for each of the 10 tabs.
(I was expecting there to be a problem on one of the tabs)
Does anyone know why the FullCalcTimer would take so long or another method 
of isolating the problem?
Thanks,
Graeme
0
Utf
11/28/2009 2:36:01 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
910 Views

Similar Articles

[PageSpeed] 8

Depending on the circumstances....I sometimes replace long columns of
formulas with "Copy > Pastespecial > values", and manupulate the data with
values rather than formulas........then the formulas can be replaced later
by macro if necessary.

Vaya con Dios,
Chuck, CABGx3




"Graeme" <Graeme@discussions.microsoft.com> wrote in message 
news:8D42EF1D-A8E5-4544-9139-A64E8F6B6A80@microsoft.com...
>I have a large file, approx 10MB, in excel 2003. Most of the data relates 
>to
> calculations for around 5,000 records, and the workbook takes too long to
> recalculate.  All the vlookup's have been replaced with Index / Match, but
> it's still slow, and I have a number of macros that rely upon it
> recalculating.  Based on a post here, I went to this site:
> http://msdn.microsoft.com/en-us/library/aa730921.aspx
> and ran the timer macros to try and isolate the problem.
> The FullCalcTimer takes around 19 secs
> The ReCalcTimer takes 0.00013 secs, and
> The SheetTimer takes no more than 0.015 secs for each of the 10 tabs.
> (I was expecting there to be a problem on one of the tabs)
> Does anyone know why the FullCalcTimer would take so long or another 
> method
> of isolating the problem?
> Thanks,
> Graeme 


0
clr
11/28/2009 3:31:56 PM
Reply:

Similar Artilces:

Rounding up Time Values
Hi I am using the formula below to round time values up,in this case t the next five minute increment =(TIME(HOUR(BB14),CEILING(MINUTE(BB14),BB13),0)) BB13 = 5 BB14 = 16:00:02 This formula only rounds up to 16:05:02 if the time is 16:01:00 o greater,i would prefer it if the formula could make the time round u as soon as one second has passed, 16:00:01 and not when one minute ha passed. Can this be done Ad -- Message posted from http://www.ExcelForum.com What is in BB14? You can just use =CEILING(BB14,5/1440) will do what you want Or if there are dates as well you can just format...

A Time Format Problem
I remember reading this on this or another Excel group, and now can not find it. Please help Column A and B contain Start and End time. Column C calculates elapsed time =B2-A2 =B3-A3 etc The sum total elapsed time at bottom of Column C What I can not remember is the custom format to use in column C so that if the end time goes past midnight, it calculates correctly. Thanks for repeating the answer for me. Paul The custom format you need is: [h]:mm:ss this allows hours above 24 to be displayed, rather than wrapping. Hope this helps. ...

Where can i find more detailed info about SOA in CRM 2005?
...

SMTP Logs
I sent a test message at 2:30 p.m. and then I went to Exchange SMTP Logs and tracked my test message and it shows it was sent at 16:35, strange enough because it is should be showing 14:35. How can I fix this ... I want the SMTP logs to show 2:35 p.m instead of 14:35. Time on my Exchange Server and on my PC is correct. Please advise. Thank you in advance. Are you using local time on your smtp logs? In smtp virtual server properties | logging properties. -- Bharat Suneja MCSE, MCT -------------------------------- "WILDPACKET" <WILDPACKET@discussions.microsoft.com&g...

MS Project Task Start Times
Hello - I am fairly new to MS Project and am having some problems with a Project Plan created for a class I am in. All of the tasks that have the same predecessor automatically have the same start date (even with resources assigned). My instructor is saying that Project will automatically adjust task start times depending on resource availability. My tasks just won't do this. Is this true? She posted an example of her project plan and I noticed that she did not use a start date constraint for these tasks yet they were organized seemingly to resource availability. ...

Download from mutliple POP3 servers at the same time?
Hello, Can outlook download from multiple POP3 servers at the same time. My outlook seems to serially go through the servers, and only download from one at a time. Regards, Scott F1 NUT <noonehome@speakeasy.net> wrote: > Can outlook download from multiple POP3 servers at the same time. My > outlook seems to serially go through the servers, and only download > from one at a time. You can't have multiple POP connections open simultaneously. Outlook will, as you observe, download from each sequentially. -- Brian Tillman ...

excel time format
How can i format a cell that will display the ime as 08:00 when i enter 8? i have tried using the format hh:mm but when i enter 8 the cell displays 00:00:00 and the formula bar shows 08/01/1900 00:00:00. see http://cpearson.com/excel/DateTimeEntry.htm In article <C635A1E7-DCC6-4304-B236-1FA95FFE6075@microsoft.com>, JarvisC <JarvisC@discussions.microsoft.com> wrote: > How can i format a cell that will display the ime as 08:00 when i enter 8? i > have tried using the format hh:mm but when i enter 8 the cell displays > 00:00:00 and the formula bar shows 08/01/190...

Find utility, highlighting answer
In the older version of Excel when I used the find utility it would highlight the cells found in a different shade. When I use the same utility in Excel 2007 the highlighting is bearly visable, so I cannot see what I searched for. Is there a way I can change this so the highlighted result is more visable ?. -- Phil C Engineer Hi Phil, I don't think there is. What I do to get around the problem that you have identified is, before I start the search, highlight all the cells in the Worsheet (by clicking on cell to the left of A and above 1 towards the top left hand cor...

Adding Tax as percentage and calculating gross amount
Newbie. I want to apply a tax % rate rather than a monetary value to quotes and orders.Unlike the US we have a single national rate of 17.5%. Do I need the SDK to action this? There doesn't appear to be any means in deployment manager or forms customisation to insert formulas or scripts against fields. Guess I was hoping for an easy ride as usual.... Can anybody give me any pointers on how I would achieve this? Cheers, Rav Rav, Quick tip. Add a new, required, picklist field called VATRate. Add the picklist to the form. Add values: blank, 17.5, 0 (because you will have 0 rate ...

It's about time again
This is a multi-part message in MIME format. ------=_NextPart_000_0036_01C4A811.955E4470 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, I am keeping track of time I work at home. The first formula in cells = E2 through E17 gives me the total hours I worked after entering starting = time and ending time. The second formula calculates the totals from the = E column. A problem arises when the sum is more than 24, if it's 27 = hours, the answer is 3 instead of 27. What can I use so the sum of my = hours just get totaled? Thanks...

negative time values
not to worry, have found the answer but thanks anyway Mike ...

How can I create one mail that can send several times to many peop
Hi, Maybe my question is stupid, but I'm new to Outlook. I would like to send one mail to many people (not in one go) without having to retype the mail. I downloaded templates from Microsoft website but I don't know how I can change the text and save it. Anyone can help me? Thanks, Remy Remy wrote: > Hi, > > Maybe my question is stupid, but I'm new to Outlook. I would like to > send one mail to many people (not in one go) without having to retype > the mail. I downloaded templates from Microsoft website but I don't > know how I can change the text and ...

3/10/2008 TIME: 12:30
NNTP TEST ...

Sync DC's time with external clock?
Hello, I was just wondering how other make sure their DC's time is kept correct as ours over the months can drift ahead of itself so I change oen DC's time time to the correct time and "net time \\DCname" the others, very manual I know. Hello Whiteford, Configure the DC with the PDCEmulator FSMO to an external time source: w32tm /config /manualpeerlist:PEERS /syncfromflags:manual /reliable:yes /update With "PEERS" you can set the time source, either DNS name (time.windows.com) or an ip address from a reliable time source. Here you can find ...

Fixed task time with flexible schedule
I am trying to create a yearly task schedule for building maintenance. My task durations are fixed at 4 to 8 hours but each task needs to be scheduled for sometime during the week of... "8 hours window cleaning anytime during the third week of July" for example. How do I represent this in MS Project 2007? Here is a reasonable work around. Project cannot schedule work "Any random 8 hour period during the week of January 25, 2010 to Januar 29, 2010." Here is what it can do: First key the task name and duration. On the main Gantt, select Window/Split... T...

How to: find reason for rejected messages?
I'm trying to find the reason, why certain messages get rejected by our exchange 2000 server. Is there a way to find out? Scenario: pullmail gets mails from pop3 mailboxes from, let's say "internetmailserver.com" and puts it in our exchange server "intranet.com". For most mails it works marvellous but for some mails it just fails and I can only see (output of pullmail command): .... 1 messages waiting << +OK 8488322 octets Message from: some.user@validdomain.com to: user@intranet.com SMTP message rejected, reply OK Unexpected response rsion:...

Find within a list that is not unique
I'm trying to figure out the quickest way to search a column for a lis of values and the answer may not always be exact. For example, within a column, I want to find if there are any of a lis companies (ABC, IBM, Sears, XYZ, etc.). Those companies may be liste there, but not exact. For example, there may be ABC Inc. or AB Corporation. This is a routine search. When I use vlookup with a named range, I can only do exact matches. Fo some reason, anyway that I try to use * does not find matches. I�v tried the following: =VLOOKUP(B2,Looklist,2,0) where �Looklist� refers to a named range an ...

Cant find xml source pane in data menu
Just upgraded to Excel 2003 (Student & Teachers Edition). Been trying the Microsoft training on xml. In one of the seesions it tells me to point to XML on the data menu and then click XML Source. I dont seem to have the XML option. I've looked in toolbar options etc. but nothing there. Is it to do with the version? ...

Calculating tax Formula
I am constructing a payroll spreadsheet. In the cell that calculates the Federal TaxI have the following: =if(i7<100,(i7*.04769)=if(i7>101<150,(i7*.05693))). The value in i7 is 140 and it returns a FALSE in cell i7 instead of a dollar amount. Any help would be greaatly appreciated. Hi IFs are made like =If(Test, test true then this, Otherwise this) You missed a comma after "if true then this", and you don't have an"otherwise"after the second test.. Also; i7>101<150 is not valid syntax. HTH. Best wishes Harald "DWright" <DWright@discu...

how to find system ipaddress in network connection
hi, i want to find system ipaddress in network and i run application in serverside to check whether that system get installed software or not in the particular system in vc++. Thanks and Regards Muthukkumaran.S.A ...

Calculating using IF and AND to show numbers based on %
Hi, I have an issues which should be solved in Excel, but I cannot seem to find an answer, here's the problem. In cel H2 is a percentage of target realisation. In cel I2 is the formula which will give a numer of points based on the percentage in H2. E.G: if H2 has the percentage 100% then cel I2 should show the number "30" if H2 has the percentage 100,07% then cell I2 should show the number "30" if H2 has the percentage 102% then cell I2 should show the number "40" This is the table on which the point are given: 0-100% = 0 (points) 100 till 101 = 30 (poin...

How to find out length of a string in a listbox (in pixels)?
Hello, How can one determine the length of a string in (or will be in) a CListbox Control? I'm dying to find out. Thank you for your help. Ivan >-----Original Message----- >Hello, > >How can one determine the length of a string in (or will be in) a CListbox >Control? > >I'm dying to find out. > >Thank you for your help. > >Ivan > > well is your string a CString? if yes use CString::GetLenght(); also take a look at strlen >. > Ivan Starr wrote: > Hello, > > How can one determine the length of a string in (or will be in) ...

where to find converters
i want to export my excel 2003 spreasheet to csv or tab but when downloaded the office 2003 converter OCONVPCK.EXE 'save as' stil doesn't show options to text such as tab, csv, lotus etc. And anybody kbnow what OCONVPCK.EXE converters this hass. The doc give no clue -- GottaRu ----------------------------------------------------------------------- GottaRun's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3129 View this thread: http://www.excelforum.com/showthread.php?threadid=50971 GottaRun wrote: > i want to export my excel 2003 spreasheet to...

Find and replace a set of strings in Powerpoint
Hi, I need to find a set of words in powerpoint and update them with another set of words. Though we have the "Find and Replace" option in powerpoint, changing numerous words is quite hectic. I saw the following code in the website pptfaqs.com. ----- Sub TheCatInTheHat() Dim oTextRange As TextRange Dim OriginalString As String Dim NewString As String Dim FindThis As String ' what string are we looking for? Dim SubstituteThat As String ' what do we replace it with FindThis = "XXXXXX" SubstituteThat = "yada yada yada" Set oTextRange = ...

ADO : can't find a user, but the user exists !
Hi, I don't understand this code below : > cscript //nologo MyScript.vbs USER DOES'NT EXIST But the user exsits, I can see it with Active Directory and the sAMAccountName is good ? '------------------------------------------------ Const adPosEOF = -3 Set objConnection = Wscript.CreateObject("ADODB.Connection") Call objConnection.Open ("Provider=ADsDSOObject;") Set objCommand = Wscript.CreateObject("ADODB.Command") objCommand.ActiveConnection = objConnection objCommand.CommandText = "<LDAP://DC=yourcenar,DC=local&quo...