INDEX+MATCH=slow?

[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?

 

[Charles Williams]

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

 

[andrew]

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

 

[Charles Williams]

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