Forum Discussion

tlaurent4624's avatar
tlaurent4624
Copper Contributor
Aug 18, 2023

Large Workbook Calculations

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

Formulas and Functions
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Aug 18, 2023

    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).

      • peiyezhu's avatar
        peiyezhu
        Bronze Contributor
        Aug 21, 2023
        Yes.
        Also some English hints.
        You can translate to your language by Edge or Chrome browser.
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Aug 19, 2023

    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)  )

Resources