Calculation Time estimation
 

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

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

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


--
Zemarko
------------------------------------------------------------------------
Zemarko's Profile: http://www.excelforum.com/member.php...o&userid=37866
View this thread: http://www.excelforum.com/showthread...hreadid=574120

Calculation Time estimation
 

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

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

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

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


--
Zemarko
------------------------------------------------------------------------
Zemarko's Profile: http://www.excelforum.com/member.php...o&userid=37866
View this thread: http://www.excelforum.com/showthread...hreadid=574120

Calculation Time estimation
 

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


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=574120