I have 13 columns of data with 30000 rows. Most data is numeric some is
2 words at the most.

The computer calculates cells very slowly and sometimes will not save
due to insufficient resources. I have 1gig ram.

An example formula might be.

=sumproduct((month=a1)*(year=a2)*(salesperson=a3)* (sales)

I may have as many as 120 of these formulas on a sheet referencing 12
different salespeople and 10 other key perfromance measures.

Is there anything I should look for or change to speed it up?
What about save and calculation options on the tools/options tab. I
dont know about these.
Would it be faster if my dbase and my worksheets are in separate books?
or does sumproduct "LOOK" anyway?

Any help would be greatly appreciated.

Hi

I would take a look at using a Pivot Table instead of these Sumproduct
formulae.
In my experience, Pivot Tables are faster.

For more information on Pivot Tables take a look at the following sites
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html

http://www.datapigtechnologies.com/f...es/pivot1.html

http://www.edferrero.com/Tutorials.aspx


also, to look at ways of speeding things up in general, go to Charles
Williams site
http://www.decisionmodels.com/optspeedb.htm
--
Regards

Roger Govier


"wx4usa" wrote in message
ups.com...
I have 13 columns of data with 30000 rows. Most data is numeric some is
2 words at the most.

The computer calculates cells very slowly and sometimes will not save
due to insufficient resources. I have 1gig ram.

An example formula might be.

=sumproduct((month=a1)*(year=a2)*(salesperson=a3)* (sales)

I may have as many as 120 of these formulas on a sheet referencing 12
different salespeople and 10 other key perfromance measures.

Is there anything I should look for or change to speed it up?
What about save and calculation options on the tools/options tab. I
dont know about these.
Would it be faster if my dbase and my worksheets are in separate
books?
or does sumproduct "LOOK" anyway?

Any help would be greatly appreciated.

Consider doing some of the repeated calculations in helper cells and
reference those.

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"wx4usa" wrote in message
ups.com...
I have 13 columns of data with 30000 rows. Most data is numeric some is
2 words at the most.

The computer calculates cells very slowly and sometimes will not save
due to insufficient resources. I have 1gig ram.

An example formula might be.

=sumproduct((month=a1)*(year=a2)*(salesperson=a3)* (sales)

I may have as many as 120 of these formulas on a sheet referencing 12
different salespeople and 10 other key perfromance measures.

Is there anything I should look for or change to speed it up?
What about save and calculation options on the tools/options tab. I
dont know about these.
Would it be faster if my dbase and my worksheets are in separate books?
or does sumproduct "LOOK" anyway?

Any help would be greatly appreciated.

Thanks for teh reply Roger,

Can I use pivit tables to pull my data from the database and then
within the table perform other calculations?

Example, I pulll salesperson, number of customers from the dbase to
the pivot table, then average revenue per customer from that data?



On Jan 26, 7:40 am, "Roger Govier"
wrote:
Hi

I would take a look at using a Pivot Table instead of these Sumproduct
formulae.
In my experience, Pivot Tables are faster.

For more information on Pivot Tables take a look at the following siteshttp://peltiertech.com/Excel/Pivots/pivotstart.htmhttp://www.contextures.com/xlPivot02.html

http://www.datapigtechnologies.com/f...es/pivot1.html

http://www.edferrero.com/Tutorials.aspx

also, to look at ways of speeding things up in general, go to Charles
Williams sitehttp://www.decisionmodels.com/optspeedb.htm
--
Regards

Roger Govier

"wx4usa" wrote in oglegroups.com...

I have 13 columns of data with 30000 rows. Most data is numeric some is
2 words at the most.

The computer calculates cells very slowly and sometimes will not save
due to insufficient resources. I have 1gig ram.

An example formula might be.

=sumproduct((month=a1)*(year=a2)*(salesperson=a3)* (sales)

I may have as many as 120 of these formulas on a sheet referencing 12
different salespeople and 10 other key perfromance measures.

Is there anything I should look for or change to speed it up?
What about save and calculation options on the tools/options tab. I
dont know about these.
Would it be faster if my dbase and my worksheets are in separate
books?
or does sumproduct "LOOK" anyway?

Any help would be greatly appreciated.

Yes you can do all sorts of calculations in the PT itself, Sum, Count,
Average, Max, Min etc. as well as set up calculated fields and
calculated items.

You can also extract data from the PT to perform further calculations
using the GetPivotData function.

--
Regards

Roger Govier


"wx4usa" wrote in message
ups.com...
Thanks for teh reply Roger,

Can I use pivit tables to pull my data from the database and then
within the table perform other calculations?

Example, I pulll salesperson, number of customers from the dbase to
the pivot table, then average revenue per customer from that data?



On Jan 26, 7:40 am, "Roger Govier"
wrote:
Hi

I would take a look at using a Pivot Table instead of these
Sumproduct
formulae.
In my experience, Pivot Tables are faster.

For more information on Pivot Tables take a look at the following
siteshttp://peltiertech.com/Excel/Pivots/pivotstart.htmhttp://www.contextures.com/xlPivot02.html

http://www.datapigtechnologies.com/f...es/pivot1.html

http://www.edferrero.com/Tutorials.aspx

also, to look at ways of speeding things up in general, go to Charles
Williams sitehttp://www.decisionmodels.com/optspeedb.htm
--
Regards

Roger Govier

"wx4usa" wrote in
oglegroups.com...

I have 13 columns of data with 30000 rows. Most data is numeric some
is
2 words at the most.

The computer calculates cells very slowly and sometimes will not
save
due to insufficient resources. I have 1gig ram.

An example formula might be.

=sumproduct((month=a1)*(year=a2)*(salesperson=a3)* (sales)

I may have as many as 120 of these formulas on a sheet referencing
12
different salespeople and 10 other key perfromance measures.

Is there anything I should look for or change to speed it up?
What about save and calculation options on the tools/options tab. I
dont know about these.
Would it be faster if my dbase and my worksheets are in separate
books?
or does sumproduct "LOOK" anyway?

Any help would be greatly appreciated.