• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Speeding up Excel

Pablo

Pablo

New Member
Hello Chandoo and Excel community,

I am currently using “SUMIFS” to compile data using 3 different criteria (Business Unit, Account and Department). The formula covers over 1,000 lines for different results. The raw data that I am pulling from has 40,000 lines. I have set up named ranges and made them dynamic with index & count. Before switching to SUMIFS, I was using Sumproducts, which was causing the file to run at incredibly slow speeds, every time I needed to update a calculation, it took 10 minutes. I have the file in manual calculation. When I switched to SUMIFS, the file sped up considerably, but anytime raw data is added or taken away, the file takes a minute or 2 to recalculate.

Is there any other option that would take the calculation time down even further? The file is currently set up with manual calculation, but of course when I go to refresh the data, it takes a couple minutes to calculate.

Thanks for your suggestions.

Pablo
 
One method might be to actually put all the data into a PivotTable, and then use several GETPIVOTDATA functions. That way, all the calculations would be done up front, and the lookup formulas would be very light in terms of calculation speed.


Or, I'd suspect your dynamic formulas. I've been learning from fellow member JeffreyWeir that the OFFSET function is volatile, while INDEX is not. If your dynamic ranges use OFFSET, that would lead to performance lag.
 
Good day Pablo


You are pulling data from 40,000 lines with formulas covering over 1,000 of results and you think a couple of minutes is slow!!!!
 
Hi, Pablo!

Tried running on an multi-CPU (4 or better 8) Intel i7-4930MX with 24Gb 2MHz RAM?

Regards!

PS: If you did it yet, would you please send my that machine by DHL? Just to play a little...
 
Hi Luke,

Excellent suggestion, thanks. I added a calculation field to the pivot table and then extracted the data that I need by using GETPIVOTDATA and the results are impressive, everything works very fast. Now I don't need to have the spreadsheet in manual calculation mode anymore and I deleted all the name formulas, dynamic ranges and SUMIFS.

Thanks again.

Pablo


PS: In this world of instance gratification and microwave mentality, every minute counts.

PS 2: Really funny SirB7, I will keep that in mind.
 
You must log in or register to reply here.
Back
Top