Calculation Time estimation

[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

 

[Pete_UK]

Have a look at this site:

http://www.decisionmodels.com/optspeede.htm

for many examples of re-writing formulae to increase speed.

Hope this helps.

Pete

 

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