Analyzing Large Amounts of Data

**roasthawg** · 07-04-2012, 05:38 PM

I use excel for a lot of sports statistics and tracking. It's great for ease of calculation via sumifs and other functions but I find myself constantly running into a problem when it comes to calculation time. If I'm performing calculations on say 20,000 rows of data and I have rows referencing other rows I quickly get into a situation where the spreadsheet is unmanageable... it simply can not complete the desired calculations and gives me a "resources not available" error. I cringe at having to learn something new but I am open to suggestions is anyone has any in terms of where I should go from here... would storing the data in a database help at all or is there any other viable options or workarounds? Thanks.

**FDibbins** · 07-04-2012, 05:45 PM

if you are performing the same calcs on "old" data, and the answers wont ever change, what i have done inthe paste with that situation, is to value those formulas.

Also, check if you have any array formulas that use entire rows or columns, as these become very resource intensive, as does conditional formatting over a wide range of cells.

Another option is to have 1 file as a data file, and create another file as a "summary" file

try thos suggestions and let me know how you make out?

**Richard Buttrey** · 07-04-2012, 05:47 PM

What are you doing when you get the error? Are you copying or filtering data? If so try putting the Excel App into Manual calculation mode first and only calculate after the process.

What sorts of formula are you using? If you have lots of array formula it's possible these are taking lots of resource. What version of Excel do you have? (Incidentally when you get a moment would you add that information to your profile so that it shows in the post) It's useful to know since there are be more friendly SUMIFS & COUNTIFS formulae available in XL 2007-->

**roasthawg** · 07-04-2012, 05:56 PM

Doing a bunch of sumifs which I believe are efficient formulas in that there's little to no difference between say sumif(a2:a20000, c1, b2:b2000) and (a:a, a1, b:b). Please correct me if I am mistaken on that however.

Going to paste over some of the sumifs who's values don't change and also set it to manual calculation mode and see if that helps. Having big problems using the percentile formula... very slow.