PDA

View Full Version : Which function uses Minimum Resource, Minimum Calculation Time?



artds
04-07-2008, 08:19 PM
HI! I need to check which method has a better system resource use, and will calculate at a faster rate. If less system resource is not possible to gauge, then at least which function will calculate faster?:think:
I am running on Intel Centrino Mobile Technology. Not dual core mind you. With a 1 GB memory. I'm using office 2003.

scenario:
I have 3 sheets of Raw Data in one workbook("DataScrs.xls")from 3 different source which covers a Quarter. Each sheet has probably about 30-40 thousands Rows and approximately 30 Columns.

And I have another workbook("RebateCalc.xls") which required access data from all 3 sheets to do a Quarterly Business Partner Rebate calculations. In here is where all the dashboards are.

Method 1:
Do 3 Pivot table for all 3 sheets, in the workbook ("DataScrs.xls") and from workbook("RebateCalc.xls") use the "getpivotdata" Excel function.

or
Method 2:
From workbook("RebateCalc.xls") make use of the different array functions, such as, "sumif", "countif", "Sum(if())".

do take note, for both method, the function will repeat for about maximum 200 rows and 20 columns, depending on the Business Partners of the Country, in roughly 10 sheets in workbook("rebateCalc.xls"). All the rows in this workbook is Dynamic.

Any one has any thoughts on this. All Comments are greatly appreciated. Thank you in advance.


regards,
art.ds.

Dave
04-07-2008, 11:08 PM
In time trials previous, I've use something like A=Now() at the start of the code, and B=Now() at the end of the code and then just msgboxed the formatted time change (B-A). It may work in your for instance? I think speed is probably a pretty good indication of your utilization of system resources. HTH. Dave

artds
04-07-2008, 11:30 PM
Hi Dave, I'm sorry, but I wasn't really referring to VBA codes here. My intention is to use the Excel Function directly into the cells.

I'm about to start on a new Dashboard proects, so I need to know which method uses less resource and/or time. I know that Resource and Time are directly proportional to each other, but based on personal expericened of other users, which method, to them is better than the other. Thank you.


regards
art.ds

Dave
04-08-2008, 10:26 PM
art.ds it seems like you haven't got the help you need yet, so I thought that I would give this a bump. Perhaps some more learned folks with more experience for your defined needs may be able to offer a solution and/or comments. HTH. Dave

artds
04-15-2008, 03:07 AM
HI Dave. I've read somewhere (www.ozgrid.com- i think) about making "Efficient Excel Spreadsheet Designs. Speed up Slow Calculations in Excel". In there, It was recommended that for Spreadsheets with lots of Rows and Columns, its best I avoid using Array Formulas, UDFs, and Volotile functions like offset(), today().

An alternative to for Array Formulas will be the Database Functions, like Dsum(), Dget(), or PivotTables. Since I'm a SuperHopeless guy with Database Functions, I'm going to use PivotTables instead.


It also point out not to use "Manual Calculation". I can understand the reasoning behind this. But for me running on a Think Pad T42, single processor, 1GB ram, it became a one on one fight club between the machine and me.

Every cell that was edited, will activate the auto calculation. And with 3 sheets of about 30 000 rows each, will roughly take about 40sec - 60sec, on a really blessed day. So most of the time, I have to set it to manual. So as to complete my task faster.


regards
art.ds

Paul_Hossler
04-15-2008, 10:39 AM
Stepping out of the box here, but if I had that much data, I'd think about seperating the data collection from the data processing.

I.e. Calc = manual, and use a VBA sub to process the raw data onto the final sheet, and avoid formulas altogether. (As a hybred approach, I've also used VBA to summarize a lot of data onto a sheet and then used that sheet for end-user reporting)

Everyone says that intrinsic Excel worksheet functions are faster (and they are) but if you have 40,000 rows of SUMIF( ... ) that's still a lot.

Also depends on the format of the data (e.g. sorted or not). I've written a VBA "SumIf" but tested to see if the criteria value was the same as the previous, and if so, just use the previous total instead of recalculating the same numbers and arriving at the same answer.

Paul

artds
04-17-2008, 02:16 AM
HI Paul, i understand what you are trying to say. However, most likely I won't be following in the direction which you are suggesting.

First of all, My VBA expericence is still in the 'novice' state. Most of the codes that I'm using, I got it from this Site, and many others.

Secondly, the Raw data will be updated every week and likewise the report will be presented by Region, countries, partners, products, split by weeks, Revenue and Volume. So back to the first point, it will take almost forever for me to figure out the codes, test thru trial and error. Which to me is almost unproductive. For people like fireftyr and Xld, its probably a breeze and even find it challenging. If I have more time, i'll probably give it a try.

so thats why, I'm going with Pivot Table. So from these 3 Raw data sheets, I will generate 2 Pivot tables each to a separate workbook. And have another workbook for the end user reporting tool. This end user tool on itself will be Formula and VB intensive that will generate peformance report, rebate calculation,...etc.

It might not be the best solutions out there, but its the safest that I can think off, in terms of resource and/or memory performance for lower end machines.

Thanks for the heads-up. regards
artds