Memory and File Size

J

Jim May

I've just completed an enormous analysis and the file size and
Momory useage is pushing the envelope. I've had to set the
Calculation mode to manual (versus Automatic). To begin with
I chose to use the Sumproduct() formula to extract a tremendous amt of
Detailed data from my tables.
At present the File (when saved) is 20,000 KB and contains approximately
99,000 Sumproduct() formulas.

I'm running Excel 2003, and I'm able (on my PC) to open it;
I occasionally have to "re-calc" so on the primary sheet alone
I press "SHIFT-F9" -- and the status bar shows the Varous xx% for 15
minutes
Then clears - with Calculate still there in the status bar which I think
is OK.
Everyone in the office runs Excel 2003, but some have less PC's than
maybe
P-4's - with 1MB Ram - Can someone furnish me some info on the things I
need
To consider - I'm unsure of what I have at this point..
TIA,
 
D

Don Guillett

Perhaps consider using a macro to calculate and leave just the value without
ever using the fomrula itself. I do this often to keep end users from
deleting formulas.

x = Evaluate("SUMPRODUCT((D1:D4=""dd"")*(E1:E4=4))")
MsgBox x
 
J

Jim May

Don:
Thanks for the input.

I understand tihe 2 VBA lines, but don't quite understand the substitute for
the
MsgBox -- are you saying I could use in a cell =x ?

I just created a new NameRange My Test with (in the refersTobox):

=SUMPRODUCT((Sheet1!B7:B17="xyz")*(Sheet1!C7:C17))

then I can use the =MyTest to get the answer..

Is this the route you are suggesting and will this save memory?

Thanks --appreciate all your help on this an other Excel NG Sites

Jim May
 
Top