Memory and File Size
 

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,

Memory and File Size
 

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
--
Don Guillett
SalesAid Software

"Jim May" wrote in message
news:bLuog.43626$fG3.9678@dukeread09...
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,

Memory and File Size
 

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


"Don Guillett" wrote:

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
--
Don Guillett
SalesAid Software

"Jim May" wrote in message
news:bLuog.43626$fG3.9678@dukeread09...
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,

Memory and File Size
 

range("a1")= Evaluate("SUMPRODUCT((D1:D4=""dd"")*(E1:E4=4))")


--
Don Guillett
SalesAid Software

"Jim May" wrote in message
...
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


"Don Guillett" wrote:

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
--
Don Guillett
SalesAid Software

"Jim May" wrote in message
news:bLuog.43626$fG3.9678@dukeread09...
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,