Calculations slowing performance

Hi,
I need to get this workbook working without it taking 13 minutes to open, close and calculate.
Would running the calculations with a macro speed up the performance of this? I am sending you a copy with a few lines, there are actually 1250 lines on the worksheet and it takes 13 minutes to open.
I know it needs to calculate only to the end of the data, but I don't know VBA enough to figure this out.
thanks is advance.

If the actual worksheet has 1250 lines, why are you using 60000 lines in the formulas?

How about dynamic named ranges? http://www.contextures.com/xlNames01.html

I have to say even with 1250 calculations each referencing 60000 rows the performance should not be that bad - it should in fact be pretty instantaneous so I suspect there are other issues (your external VLOOKUP perhaps ?)

Is this the only sheet in the file ?


The below outlines some basics of improving performance but per the above I'm not sure it will prove to be that relevant.


Regards the use of Named Ranges - it can be useful to use INDEX rather than OFFSET to create it (avoid Volatility in functions utilising the name), eg:

Name: =_Data
RefersTo: =Sheet1!$A$2:INDEX(Sheet1!$U:$U,MATCH(REPT("Z",255),Sheet1!$A:$A))

At which point your formula becomes:

V2: =SUMIFS(INDEX(_Data,0,10),INDEX(_Data,0,1),A2,INDEX(_Data,0,3),C2)

There is an overhead in using names so if the range is relatively stable at say 1250 rows, perhaps increasing incrementally then I'd suggest persisting with a hard wired range but reducing it such that it incorporates a little excess capacity but not an excessive amount.

V2: =SUMIFS($J$2:$J$2500,$A$2:$A$2500,$A2,$C$2:$C$2500,$C2)
copied down

There are other methods which would improve performance beyond those mentioned above...

1 - sort your data

In this case by Col A & C (this is implied as being the case by your sample)

sorted data invariably allows for more efficient formulae...

V2: =IF(AND($A2=$A1,$C2=$C1),$V1,SUMIF($C2:INDEX($C:$C,MATCH($A2,$A:$A)),$C2,$J2:$J$60000))
copied down

By sorting the data we improve performance in the above by

a) only calculating the SUMIF once per combination of A & C (subsequent repetitions simply use the already calculated result)

b.1) we need only conduct a SUMIF rather than a SUMIFS
this is because by sorting the data we can quickly determine the range in which all instances of our A value are found (see MATCH)

b.2) for the same reasons as above we're now only processing a very small subset of the range in our SUMIF (ie only the range containing our A value)


2 - Concatenation

If sorting the data is not viable the next best thing is to add concatenation key such that we can still dispense with SUMIFS requirement and revert to a more efficient alternative like SUMIF

Using the sample file:

Y2: =$A2&"@"&$C2
copied down

Our calculation now becomes:

V2: =SUMIF($Y:$Y,$A2&"@"&$C2,$J:$J)
copied down

Note: I'm using large ranges but performance will still be pretty quick - this is down to the way SUMIF (and other standard functions) handle the referenced range - they will generally only process the "used range" so the excess is not really an issue.