INDEX+MATCH=slow?

  • Thread starter Thread starter andrew
  • Start date Start date
A

andrew

Hi, i have the following formula running in a column up to 800 rows (column
AT):

=IF(I$7="","",IF(AS7="H",INDEX(I$7:I$16,MATCH($AP$7,E$7:E$16,0))-INDEX(K$7:K$16,MATCH($AP$7,E$7:E$16,0)),INDEX(K$7:K$16,MATCH($AP$7,H$7:H$16,0))-INDEX(I$7:I$16,MATCH($AP$7,H$7:H$16,0))))

and the following in the adjacent column, also 800 rows (column AU):

=IF(F$7="","",IF(AS7="H",INDEX(F$7:F$16,MATCH($AP$7,E$7:E$16,0)),INDEX(G$7:G$16,MATCH($AP$7,H$7:H$16,0))))

I noticed that the calculations performed can be a little taxing, BUT yet to
see the permanent 'Calculation' word appearing at the bottom bar of the Excel
2003 file.

Both formulas are basically looking for specific text/number result, and
working. Anyone knows if its possible to shorten or use alternatives? Is
INDEX and MATCH the best formula to use?
 
The slow bits are probably the MATCH formulae.
It looks like you are asking Excel to calculate MATCH($AP$7,E$7:E$16,0) 2400
times
and MATCH($AP$7,H$7:H$16,0) also 2400 times.

Why not use 2 helper cells somewhere else to contain these 2 formulae so
that they are calculated once and then refer to the results 2400 times?

That way Excel will calculate the slow bits 2 times instead of 4800 times.

regards
Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com
 
could you elaborate further about 'helper' cells? Sorry but i'm just a novice
learning Excel...need more exposure to know how to optimize formulas...
 
in 2 cells somewhere (lets assume AX1 and AY1) enter the Match formulae
AX1:
=MATCH($AP$7,E$7:E$16,0)
AY1:
=MATCH($AP$7,H$7:H$16,0)

then change your AT formula to
=IF(I$7="","",IF(AS7="H",INDEX(I$7:I$16,$AX$1)-INDEX(K$7:K$16,$AX$1),INDEX(K$7:K$16,$AY$1)-INDEX(I$7:I$16,$AY$1)))

and your AU formula to
=IF(F$7="","",IF(AS7="H",INDEX(F$7:F$16,$AX$1),INDEX(G$7:G$16,$AY$1)))

Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com
 
Back
Top