 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / March 2008Calculation speed optimisation - Links and Dependency Tree
Hi All, Environment = Excel 2007, Centrino Dual-core 2.3MHz processor, 2Gb RAM, WinXP Pro. I am trying to optimise a fairly large set of workbooks for a client, some of which have multiple worksheets, and some of those worksheets are fairly big (e.g. 2000 rows x 200 columns of calcs = 400,000 cells per sheet, say 10 sheets, say 5 workbooks = 20m cells if my maths are good and of course it varies across the books). My question is what would be optimal in terms of calculation time. To simplify, we'll narrow it down to one workbook with two sheets. Sheet1 contains the 'source' data (lets assume 300 product names) which do change. Sheet2 contains multiple working areas where those product names are referenced six times (assume a vertical layout with dates across the top, and six 'sections' of calculations each of which is 300 rows deep to give a total of just under 2000 rows). Option 1: Linking each of those six sections in Sheet2 to the source Sheet1 separately (minimises dependencies since there is only one level of dependency, but means that I have 1800 links to another sheet) Option2: Linking the top section of Sheet2 to the source Sheet1, and each of the other five sections of Sheet2 to the top section of Sheet2 (creates an additional level of dependency - now three deep (including the source) rather than two deep (inc source), but means that I only have 300 links to another sheet. I understand that there are lots of other factors in optimising a set of linked workbooks and calculation time (not least of which is that we have already moved to Excel 2007 and a dual core machine), and we will be pursuing those, but I have to make the changes one at a time and test to be sure that it all still works as the client is very dependent on this for now. Therefore, please do offer any other suggestions on optimisation, but I need to address this specific design question in the first instance, so please offer your opinion on this one too! It would also be interesting from a basic design perspective for any future design / build assignments I may get! I have set follow-ups to microsoft.public.excel as this seems a fairly general thread. Thanks in advance,  SignatureAlan. The views expressed are my own, and not those of my employer or anyone else associated with me. My current valid email address is: 1bupdvc02@sneakemail.com This is valid as is. It is not munged, or altered at all. It will be valid for AT LEAST one month from the date of this post. If you are trying to contact me after that time, it MAY still be valid, but may also have been deactivated due to spam. If so, and you want to contact me by email, try searching for a more recent post by me to find my current email address. The following is a (probably!) totally unique and meaningless string of characters that you can use to find posts by me in a search engine: ewygchvboocno43vb674b6nq46tvb Hi Alan, For lots of info about calculation and memory efficiency, visit Charles Williams' site: www.decisionmodels.com If this is a commercial project, I assume you can afford his add-in to analyze your workbooks. It helped me improve performance significantly several times. SignatureKind regards, Niek Otten Microsoft MVP - Excel | Hi All, | [quoted text clipped - 48 lines] | | Thanks in advance, > Hi Alan, > [quoted text clipped - 6 lines] > to analyze your workbooks. It helped me improve performance > significantly several times. Thanks Niek. I had a look but I couldn't find anything that really gave fundamental design advice in this area and I cannot get funding for their products - they are really aimed at bigger organisations I think! It is commercial, but my aim here is just to help keep the business going with what they have. If there was the cash available, they'd be keeping the staff in their jobs, or buying a better production / inventory planning application, but for now it is excel or bust! What is your opinion on the question I actually asked? Do you think, in general, we should build models using approach (1) or (2) to optimise calculation speed? Thanks, SignatureAlan. The views expressed are my own, and not those of my employer or anyone else associated with me. My current valid email address is: 1bupdvc02@sneakemail.com This is valid as is. It is not munged, or altered at all. It will be valid for AT LEAST one month from the date of this post. If you are trying to contact me after that time, it MAY still be valid, but may also have been deactivated due to spam. If so, and you want to contact me by email, try searching for a more recent post by me to find my current email address. The following is a (probably!) totally unique and meaningless string of characters that you can use to find posts by me in a search engine: ewygchvboocno43vb674b6nq46tvb The general advice given on my website www.decisionmodels.com is that references to the same sheet are generally faster than references to other sheets, but this may not be a significant bottleneck in your case. Every spreadsheet is different: you really need to find out which parts of your spreadsheet system are slow before you waste time and money trying to speed up things that may not need any attention. If you really cannot afford the $79 that FastExcel costs, you may find the free RangeCalc addin on my downloads page useful: it allows you to time the calculation of selected blocks of formulae so that you can see if they are slow or not. Charles __________________________________________________ Outlines for my Sessions at the Australia Excel Users Group http://www.decisionmodels.com/OZEUC.htm >> Hi Alan, >> [quoted text clipped - 23 lines] > > Thanks, > The general advice given on my website www.decisionmodels.com is > that references to the same sheet are generally faster than [quoted text clipped - 9 lines] > you to time the calculation of selected blocks of formulae so that > you can see if they are slow or not. Thanks Charles. As I mentioned, they are very cash constrained right now, and I am just trying to help them keep going at this point. I will try reducing the links between sheets, and replace them with in-sheet links and see if that helps for now. Thanks, SignatureAlan. The views expressed are my own, and not those of my employer or anyone else associated with me. My current valid email address is: 1bupdvc02@sneakemail.com This is valid as is. It is not munged, or altered at all. It will be valid for AT LEAST one month from the date of this post. If you are trying to contact me after that time, it MAY still be valid, but may also have been deactivated due to spam. If so, and you want to contact me by email, try searching for a more recent post by me to find my current email address. The following is a (probably!) totally unique and meaningless string of characters that you can use to find posts by me in a search engine: ewygchvboocno43vb674b6nq46tvb It's amazing how a cash strapped company will come up with the dough to pay (someone who appears to be) a consultant, but won't pay $79 for a tool that'll probably do what it's advertised to do. I'm such a cynic. >> The general advice given on my website www.decisionmodels.com is that >> references to the same sheet are generally faster than references to [quoted text clipped - 18 lines] > > Thanks, > It's amazing how a cash strapped company will come up with the dough > to pay (someone who appears to be) a consultant, but won't pay $79 > for a tool that'll probably do what it's advertised to do. > > I'm such a cynic. If only (I was getting paid)! SignatureAlan. The views expressed are my own, and not those of my employer or anyone else associated with me. My current valid email address is: 1bupdvc02@sneakemail.com This is valid as is. It is not munged, or altered at all. It will be valid for AT LEAST one month from the date of this post. If you are trying to contact me after that time, it MAY still be valid, but may also have been deactivated due to spam. If so, and you want to contact me by email, try searching for a more recent post by me to find my current email address. The following is a (probably!) totally unique and meaningless string of characters that you can use to find posts by me in a search engine: ewygchvboocno43vb674b6nq46tvb
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.