Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 5th 08, 03:18 AM posted to microsoft.public.excel,microsoft.public.excel.links,microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2006
Posts: 188
Default Calculation 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,

--

Alan.

The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:



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
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculation speed optimisation - Links and Dependency Tree Alan Excel Discussion (Misc queries) 0 March 5th 08 03:18 AM
Calculation speed optimisation - Links and Dependency Tree Alan Excel Worksheet Functions 0 March 5th 08 03:18 AM
Calculation Speed Jay Excel Worksheet Functions 1 April 18th 07 06:36 PM
Need to output the dependency tree for formulas Adhiza Excel Discussion (Misc queries) 0 October 13th 06 04:51 PM
UDF and Calculation tree Ken Wright Links and Linking in Excel 1 February 6th 05 04:58 PM


All times are GMT +1. The time now is 02:49 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017