Calculations Very Slow

**alcharbonneau** · 10-24-2012, 11:19 AM

Hi

I have been analyzing data using the great recommendations made by Excel Forum participants. These formulae have worked very well analyzing data from 500-1000 companies. However, when I moved up to 4000-50000 companies the worksheets have slowed tremendously. I am wondering if you would review the formulae used on the attached sheet to see if there is a more "efficient" formula that might speed up worksheet? Alternatively, I have begun to explore using Access 2010 to finish the analysis.

Thank you for taking the time to read and consider this request.

Al Charbonneau

**Miraun** · 10-24-2012, 11:28 AM

The use of OFFSET in G2:G524 is what's causing the problem.

Offset is a volatile function... long story short, it takes forever to calculate... could you explain what you're trying to do with the function in G2:G524? Perhaps there's another way we can write it without using a volatile function that'll increase calculation speed.

**alcharbonneau** · 10-24-2012, 11:46 AM

Thank you for your response.

Here is an example: G2:G524 is checking to see if the sum of values in lines 00300 to 09600 (Line # in Column D, Column # in E) equal the sum of values for line 10100 colmn 1 and line 10100 column 2. This calculation is carried out for the total number of lines of data for each company. Normally, each company submits 100-200 lines of data.

Thanks again for your response.

Al

**Miraun** · 10-24-2012, 01:15 PM

Sorry about the delay... had a nice lunch break.. Here's what I came up with:
=SUMIFS($F$2:$F$524,$A$2:$A$524,A2,$E$2:$E$524,E2)/2=SUMIFS($F$2:$F$524,$A$2:$A$524,A2,$D$2:$D$524,10100,$E$2:$E$524,E2)

Here's whats up:

Your Column D and E are stored as text, not numbers, so normally I'd try to throw a function in there for <10100 to capture values 00300 to 09600. However, since that's not the case, I'm just capturing all values, including 10100, and then dividing it by two to compare to the 10101 value (its easier to find because 10100=10100 whether it's text or a number!). Since a total will always be half of all components plus a total, the equation should work.

Does that work? I assumed that it was checking the 10100 entry based on Record # and CLMN#... but could easilly be adjusted if Company# needs to be taken into account as well.

**alcharbonneau** · 10-24-2012, 09:24 PM

Hi

First of all it worked and I think faster that the original. Although it should be noted that another valued forum contributor wrote the original formula for a much smaller array of companies. I tried to click on the lower right hand corner of your formula to automate the formula rather than dragging over some 350 K rows. Is there some command that I should be using or is that "automation" function not applicable.

At any rate, thank you for responding to my request.

By the way, I clicked on the star and wanted to add a positive comment but my not so nimble 9:20 PM finger shut the box before I could add the comment.

Al