Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time Sheet Calculation | Excel Discussion (Misc queries) | |||
#VALUE! in Time Calculation | Excel Worksheet Functions | |||
perform calculation only if current time is after 10 am | Excel Worksheet Functions | |||
"Manual calculation" takes less time. Why? | Excel Discussion (Misc queries) | |||
time calculation excel | Excel Worksheet Functions |