Speeding up Excel 2007

[StevenEdmonton]

Can anyone please advise me as to the best way to speed up the calculations in Excel 2007. The spreadsheet I am using has approx. 200,000 rows by 60 columns and takes about 25 seconds for a recalculation of the entire spreadsheet. I am using the following functions within the spreadsheet:

-VALUE, SEARCH, ISNUMBER, OFFSET, SUM, SIGN, MATCH


After some testing it seems that the nested OFFSET and MATCH functions are causing most of the slow downs. The following is the formula in question:


=OFFSET($AY$14,(MATCH(BH14,$BB$14:$BB$200000,0)-1)-OFFSET($BC$14,MATCH(BH14,$BB$14:$BB$200000,0)-1,0)+1,-50)

Is there a more efficient way of doing this formula to have less drag on my cpu to make the recalculation faster? And is there any setting in Excel that would increase the speed of its calculations. I already have check off the 'Use all processors on this computer' set to 8. I am not too sure what else I can do.

I am using a Dell first gen i7 @2.93 GHz, 4 Cores, 8 Logical Processors, running with VISTA with a 64 bit operating system and 8GB of RAM. The version of Excel I am using is Excel 2007 (12.0.6654.5003) SP3 MSO (12.0.6607.100).


Any help would be greatly appreciated.


Thanks, Steven

 

[Jonmo1]

Without getting into any detail of what the formula is actually doing, just strictly reducing calculations...

since the formula duplicates a match twice, it would improve performance to put the match in a cell by itself, then have the formula refer to that cell..

Say for example, $Z14 contains the match

$Z14: =MATCH(BH14,$BB$14:$BB$200000,0)-1
Then your formula is
=OFFSET($AY$14,$Z14-OFFSET($BC$14,$Z14,0)+1,-50)

Now the match is only performed once for the formula...