Large Workbook Calculations

Copper Contributor

I have a question about equations using a large amount of data in a workbook (containing over 100 spreadsheets). Basically I have wage data for over 100 different agencies (each with it's own spreadsheet), including rank, years of service, hourly wage, etc. I have created "summery" type of spreadsheets to go through the data and calculate averages, means, and identify maximums and minimums depending on locations (so the summaries break down by state, county, and region). The equations are long and arduous, so just to "run" the calculations (like when I add or change info), it takes several minutes each time I look or save it. (I recognize that this much data is probably better suited for MS Access, but I'm sharing the completed workbook with a lot of people, and many of them aren't familiar with database programs.) I guess my question is this: is there a better way to construct the workbook and/or the equations to make it quicker and more reliable?

 

An example of one of the equations (used to average certain cells of wages IF the spreadsheet matches the appropriate county): =SUMPRODUCT(SUMIF(INDIRECT("'"&$A$110:$A$273&"'!G1"),"Belknap",INDIRECT("'"&$A$110:$A$273&"'!J3")))/SUMPRODUCT(COUNTIFS(INDIRECT("'"&$A$110:$A$273&"'!G1"),"Belknap", INDIRECT("'"&$A$110:$A$273&"'!J3"),"<>"))

 

Any help will be MUCH appreciated!

 

Tara

5 Replies

@tlaurent4624 

The best solution may be to consolidate the 100+ sheets into 1 central sheet where the analysis becomes much easier with formulas or a pivot.

 

With 100+ sheets and a summary, my guess is the INDIRECT is the biggest problem.  If you're not able to consolidate the sheets, VSTACK is capable of consolidating the sheets but that may be pushing it (Dependent on the number of rows across those 100+ sheets).

http://e.anyoupin.cn/EData/?p=tools.consolidate.consolidateshts/consolidateWithNewF

online tool to consolidate sheets to one


https://b23.tv/cnrAjDk
then run SQL to analysis data.

@tlaurent4624 

It very depends on which Excel version platform you are. Technique you use was practically the only one for Excel which doesn't support dynamic arrays.

If you are on Excel 365 follow @Patrick2788 suggestion. In particular, instead of first part of your formula

SUMPRODUCT(SUMIF(INDIRECT("'"&$A$110:$A$273&"'!G1"),"Belknap",INDIRECT("'"&$A$110:$A$273&"'!J3")))

that could be something like

=SUM( ( HSTACK(FirstSheet:LastSheet!G1)="Belknap" )*HSTACK(FirstSheet:LastSheet!J3)  )

@peiyezhuboth links go to websites in another language ... ?

Yes.
Also some English hints.
You can translate to your language by Edge or Chrome browser.