ways to reduce file links memory (office XP)

Topic

New Reply

hi, there are 4 Excel files links together to support data. one has 8k KB, one has 29k KB, one has 77k KB and last one has 38k KB. these files have data, Excel function formulas, pivot charts, pivot tables and their memory will grow as new more data coming in. can anyone suggest anyway to reduce the files burden to recalc? THANKS.

Reply | Quote

Replies

here is some info on reducing the size due to pivots.

reducing formulas is not so general. Some suggestions: get rid of array formulas they calculate slowly since they have many calculations in each one. Many times a macro can do it much, much faster and use alot less memory.

IFs are memory hogs.

built-in (non-array) functions (even mega-formulas) are faster than user-defined functions

if you use the same formula a lot (just copied), a “named formula” might offer some memory improvement.

If your data does not change alot, copy – paste special -values can be done (saving perhaps 1 row of calcs) and then copying that row as needed to update and then changing to values again.

Steve

Reply | Quote

hi Steve, THANKS a lot for your many suggestions on reducing file memory. my 4 files now can improve its memory to about 77k KB and i shrink them into two files now and that is a lot for me, around 80% of my data are merely copied down calculations from row 10 until row 4461 and increasing rows of new data everyday. now i use the copy-paste speacial- values for above formulas and leave a number of rows below with formulas to update necessary new data to generate new result. around 70% of the copied formulas calculations use IF function, others use a mixture of formulas, some pivot charts and pivot tables. i think maybe the named formula comment can be use somewhere i just have to pick up the theory and practice. what do you mean by IFs are ‘memory hogs’? are array formulas are the one that must be entered with Ctrl+Shift+Enter and a formula like =MIN(A2:A6000) is not an array, isn’t it? i don’t understand the ‘ built-in (non-array) functions (even mega-formulas) are faster than user-defined functions ‘ but i think built-in functions are those in list in excel by default and mega formulas are long list of functions combination and udf is new function created in vba i will check more later. all my formulas obtained from the excel function list. and i haven’t ponder upon pivot memory improvement. Thanks and bye.

Reply | Quote

Happy I could help.

Concerning some of the other “questions/comments”
what do you mean by IFs are ‘memory hogs’? They seem to use up a lot of memory. If possible (some IFs can not be done this way) “boolean” formulas are better. Again an IF in a named formula could reduce memory.

Are array formulas are the one that must be entered with Ctrl+Shift+Enter and a formula like =MIN(A2:A6000) is not an array, isn’t it? The ones entered with “ctrl-shift+enter” are termed “array formula”, though technically min, max, average are also “array formulas” as is SUMPRODUCT, slope, etc. By themselves they are not a big issue. Many times they are used to get multiple criteria and these can involve lots of calculations and many repeat the same calcs so they can really slow excel down.

but i think built-in functions are those in list in excel by default and mega formulas are long list of functions combination and udf is new function created in vba Yes that is correct

Steve

Reply | Quote

Happy I could help.

Concerning some of the other “questions/comments”
what do you mean by IFs are ‘memory hogs’? They seem to use up a lot of memory. If possible (some IFs can not be done this way) “boolean” formulas are better. Again an IF in a named formula could reduce memory.

Are array formulas are the one that must be entered with Ctrl+Shift+Enter and a formula like =MIN(A2:A6000) is not an array, isn’t it? The ones entered with “ctrl-shift+enter” are termed “array formula”, though technically min, max, average are also “array formulas” as is SUMPRODUCT, slope, etc. By themselves they are not a big issue. Many times they are used to get multiple criteria and these can involve lots of calculations and many repeat the same calcs so they can really slow excel down.

but i think built-in functions are those in list in excel by default and mega formulas are long list of functions combination and udf is new function created in vba Yes that is correct

Steve

Reply | Quote

hi Steve, THANKS a lot for your many suggestions on reducing file memory. my 4 files now can improve its memory to about 77k KB and i shrink them into two files now and that is a lot for me, around 80% of my data are merely copied down calculations from row 10 until row 4461 and increasing rows of new data everyday. now i use the copy-paste speacial- values for above formulas and leave a number of rows below with formulas to update necessary new data to generate new result. around 70% of the copied formulas calculations use IF function, others use a mixture of formulas, some pivot charts and pivot tables. i think maybe the named formula comment can be use somewhere i just have to pick up the theory and practice. what do you mean by IFs are ‘memory hogs’? are array formulas are the one that must be entered with Ctrl+Shift+Enter and a formula like =MIN(A2:A6000) is not an array, isn’t it? i don’t understand the ‘ built-in (non-array) functions (even mega-formulas) are faster than user-defined functions ‘ but i think built-in functions are those in list in excel by default and mega formulas are long list of functions combination and udf is new function created in vba i will check more later. all my formulas obtained from the excel function list. and i haven’t ponder upon pivot memory improvement. Thanks and bye.

Reply | Quote

hi Steve, thanks a lot for your advice. about the ‘named formula’, let say i have a formula that inputs are still variable, and the formula need to be copied down, far down to row6000, how am i going to do the ‘named formula’? thanks again.

Reply | Quote

To replace a loooooong formula with a named formula, follow these steps:

– Put your cursor on the first cell that contains the formula;
– Select the entire formula in the formula bar and hit control-c to copy it;
– press escape to leave the formula bar
– Choose Insert, Name, Define;
– Enter a name for the formula;
– Tab to the Refers To box and clear it. Then hit control-v to paste in the formula you have just copied;
– In the first cell, change the formula to:

“=TheNameYouGave” (without the quotes of course)

– copy this new formula down to fill all cells that contained the long formula.

Alas the formula cannot be longer than about 245 character though, if it is, you need to split it up over more than one named formula and piece them together, either in the cell itself or in another named formula.

Reply | Quote

To replace a loooooong formula with a named formula, follow these steps:

– Put your cursor on the first cell that contains the formula;
– Select the entire formula in the formula bar and hit control-c to copy it;
– press escape to leave the formula bar
– Choose Insert, Name, Define;
– Enter a name for the formula;
– Tab to the Refers To box and clear it. Then hit control-v to paste in the formula you have just copied;
– In the first cell, change the formula to:

“=TheNameYouGave” (without the quotes of course)

– copy this new formula down to fill all cells that contained the long formula.

Alas the formula cannot be longer than about 245 character though, if it is, you need to split it up over more than one named formula and piece them together, either in the cell itself or in another named formula.

Reply | Quote

Hi Pieterse, i think i understand the named formula in that way, but, the first cell formula requires changes in its input, let say in G2, a formula goes =SUM((A2),(D2),(F2)) and i copy the first cell formula to G6000, 5 seconds later, i change the fomula on G2 to =SUM((A2),(B2),(F2)) and copy down to G6000, and 5 seconds later, i change the G2 formula again to do some kind of testing. with this, i think this named formula cannot fit this requirement, can it? either there is a better way to reduce this kind of formula memory or i just fix to this formula and copy to G6000, as original way? THANKS for any help.

Reply | Quote

A named formula is very handy in just the situation you describe.

You only need to change the named formula and there is no more copying down involved, all cells that contain the reference to the named formula update automatically.

You do need to make sure you have one of the cells selected that contain a reference to the named formula, before you start editing the named formula.

Reply | Quote

hi Pieterse, sorry, maybe i do not understand your instruction on editing my formula. now, i hit F2 at G2 to provoke the formula on G2 out and i press left arrow key to edit my formula, just like from =SUM((A2),(D2),(F2)) to =SUM((A2),(B2),(F2)) and lastly press Ctrl+Shift+Down Arrow to complete the formula copies to G6000. about named formula to change =SUM((A2),(D2),(F2)) to =TEST, and =TEST appears in G2 until G6000, and when i need to change =SUM((A2),(D2),(F2)) to =SUM((A2),(B2),(F2)), do i have to go Insert-Name-Define- select TEST and edit in the Refers to: column from =SUM((A2),(D2),(F2)) to =SUM((A2),(B2),(F2))? if that is the case, i will stick to my original way. Thanks again.

Reply | Quote

[indent]

---

do i have to go Insert-Name-Define- select TEST and edit in the Refers to: column from =SUM((A2),(D2),(F2)) to =SUM((A2),(B2),(F2))?

---

[/indent]That is correct. In case of the formula you posted, I doubt if there will be much benefit in using a named formula regarding the size of the workbook.

Reply | Quote

[indent]

---

do i have to go Insert-Name-Define- select TEST and edit in the Refers to: column from =SUM((A2),(D2),(F2)) to =SUM((A2),(B2),(F2))?

---

[/indent]That is correct. In case of the formula you posted, I doubt if there will be much benefit in using a named formula regarding the size of the workbook.

Reply | Quote

hi Pieterse, sorry, maybe i do not understand your instruction on editing my formula. now, i hit F2 at G2 to provoke the formula on G2 out and i press left arrow key to edit my formula, just like from =SUM((A2),(D2),(F2)) to =SUM((A2),(B2),(F2)) and lastly press Ctrl+Shift+Down Arrow to complete the formula copies to G6000. about named formula to change =SUM((A2),(D2),(F2)) to =TEST, and =TEST appears in G2 until G6000, and when i need to change =SUM((A2),(D2),(F2)) to =SUM((A2),(B2),(F2)), do i have to go Insert-Name-Define- select TEST and edit in the Refers to: column from =SUM((A2),(D2),(F2)) to =SUM((A2),(B2),(F2))? if that is the case, i will stick to my original way. Thanks again.

Reply | Quote

A named formula is very handy in just the situation you describe.

You only need to change the named formula and there is no more copying down involved, all cells that contain the reference to the named formula update automatically.

You do need to make sure you have one of the cells selected that contain a reference to the named formula, before you start editing the named formula.

Reply | Quote

Hi Pieterse, i think i understand the named formula in that way, but, the first cell formula requires changes in its input, let say in G2, a formula goes =SUM((A2),(D2),(F2)) and i copy the first cell formula to G6000, 5 seconds later, i change the fomula on G2 to =SUM((A2),(B2),(F2)) and copy down to G6000, and 5 seconds later, i change the G2 formula again to do some kind of testing. with this, i think this named formula cannot fit this requirement, can it? either there is a better way to reduce this kind of formula memory or i just fix to this formula and copy to G6000, as original way? THANKS for any help.

Reply | Quote

hi Steve, thanks a lot for your advice. about the ‘named formula’, let say i have a formula that inputs are still variable, and the formula need to be copied down, far down to row6000, how am i going to do the ‘named formula’? thanks again.

Reply | Quote

here is some info on reducing the size due to pivots.

reducing formulas is not so general. Some suggestions: get rid of array formulas they calculate slowly since they have many calculations in each one. Many times a macro can do it much, much faster and use alot less memory.

IFs are memory hogs.

built-in (non-array) functions (even mega-formulas) are faster than user-defined functions

if you use the same formula a lot (just copied), a “named formula” might offer some memory improvement.

If your data does not change alot, copy – paste special -values can be done (saving perhaps 1 row of calcs) and then copying that row as needed to update and then changing to values again.

Steve

Reply | Quote