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

"andrew" wrote in message
...
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?

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" wrote:

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" wrote in message
...
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?

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),IND EX(G$7:G$16,$AY$1)))

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


"andrew" wrote in message
...
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" wrote:

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" wrote in message
...
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?