Sumproduct Slow

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

wx4usa - 26 Jan 2007 12:21 GMT

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.

Reply to this Message

Roger Govier - 26 Jan 2007 13:40 GMT

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/flashfiles/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

Signature

Regards

Roger Govier

>I have 13 columns of data with 30000 rows. Most data is numeric some is
> 2 words at the most.
[quoted text clipped - 17 lines]
>
> Any help would be greatly appreciated.

Reply to this Message

wx4usa - 27 Jan 2007 14:18 GMT

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" <r...@technologyNOSPAM4u.co.uk>
wrote:

> Hi
>
[quoted text clipped - 36 lines]
>
> > Any help would be greatly appreciated.

Reply to this Message

Roger Govier - 27 Jan 2007 19:05 GMT

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.

Signature

Regards

Roger Govier

> Thanks for teh reply Roger,
>
[quoted text clipped - 51 lines]
>>
>> > Any help would be greatly appreciated.

Reply to this Message

Bob Phillips - 27 Jan 2007 11:11 GMT

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

Signature

HTH

Bob Phillips

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

> I have 13 columns of data with 30000 rows. Most data is numeric some is
> 2 words at the most.
[quoted text clipped - 16 lines]
>
> Any help would be greatly appreciated.

Reply to this Message