|
Sumproduct Slow
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. |
Sumproduct Slow
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. |
Sumproduct Slow
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. |
Sumproduct Slow
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. |
Sumproduct Slow
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. |
| All times are GMT +1. The time now is 06:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com