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:

(E-Mail Removed)

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.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Alan" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
|
| 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:
|
| (E-Mail Removed)
|
| 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
|
|
|
|
|

"Niek Otten" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>
> --
> Kind regards,
>
> Niek Otten
> Microsoft MVP - Excel
>

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,

--

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:

(E-Mail Removed)

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

"Alan" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
>
> "Niek Otten" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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.
>>
>> --
>> Kind regards,
>>
>> Niek Otten
>> Microsoft MVP - Excel
>>
>
> 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,
>
> --
>
> 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:
>
> (E-Mail Removed)
>
> 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
>
>
>

"Charles Williams" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>

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,

--

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:

(E-Mail Removed)

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.

"Alan" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
>
>
> "Charles Williams" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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.
>>
>
> 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,
>
> --
>
> 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:
>
> (E-Mail Removed)
>
> 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
>
>
>

"KenB" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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)!

--

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:

(E-Mail Removed)

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