# Calculation Time estimation

Z

#### Zemarko

Hello,
I am trying to get my IBM T23 Laptop (1 Go RAM, Pentium III 1,13Ghz) t
calculate about 400K formulas of this kind

=IF(ISERROR(IF(OR(\$F2<>(VLOOKUP(\$A2;'06'!A:H;6;FALSE));ISTEXT(VLOOKUP(\$A2;'Tf
07'!C:H;5;FALSE)));\$G2/100;0));0;IF(OR(\$F2<>(VLOOKUP(\$A2;'06'!A:H;6;FALSE));ISTEXT(VLOOKUP(\$A2;'Tf
07'!C:H;5;FALSE)));\$G2/100;0))

and obviously the last time I tried, it stayed stuck for more than a
hour, so I stopped it. Does anybody has an idea about how long I woul
need to let it work to get this done? Am I insane to try this or do
just have a crappy computer?
Cheer

B

#### broro183

hi,
I have no idea on expected timeframe but have suggestions for speeding
up the calculation of your formula:

1) Change the vlookups to only lookup the used range, eg instead of:
""=VLOOKUP(\$A2;'06'!A:H;6;FALSE)"" something like
""=VLOOKUP(\$A2;'06'!A1:H560;6;FALSE)""

2) Split the repeated vlookups out into separate cells as this will
half the # of times the calculation is done ie, assuming column I &
onwards are empty & the final formula is in column K:
* in column I: ""=VLOOKUP(\$A2;'06'!A:H;6;FALSE)""
* in column J: ""=VLOOKUP(\$A2;'tfr 07'!C:H;5;FALSE)""
* in column K:
""=IF(ISERROR(IF(OR(\$F2<>\$I2;ISTEXT(\$J2));\$G2/100;0));0;IF(OR(\$F2<>\$I2;ISTEXT(\$J2));\$G2/100;0))""

3) Taking the above principle one step further you could enter it as:
* in column I: ""=VLOOKUP(\$A2;'06'!A:H;6;FALSE)""
* in column J: ""=VLOOKUP(\$A2;'tfr 07'!C:H;5;FALSE)""
* in column K: ""=IF(OR(\$F2<>\$I2;ISTEXT(\$J2));\$G2/100;0)""
* in column L: ""=IF(iserror(\$k2;0;\$k2)""

My internet connection crashed & after a restart Pete had beaten me to
this but sometimes repitition comes in handy, so...
For other suggestions of speeding up your calculation, see:
http://www.decisionmodels.com/optspeedb.htm (& related pages)

Suggestion one & two will have the biggest effect on performance but
three may also make a noticeable difference.

hth,
Rob Brockett
NZ
Always learning & the best way to learn is to experience...