| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Very Slow Calculating
My formula calculating is very slow, formula is as under & my active cell is
E5 ! E5 12345 J5 =IF($E5="","",IF(ISNA(VLOOKUP($E5,sl,11,)),"",VLOO KUP($E5,sl,11,))) L5 6,200 (this cell has unit price) M5 115.00 (this cell has exchange rate) N5 =$L5*IF($M5=0,1,$M5) O5 =SUMPRODUCT(($E$5:$E5=$E5)*($L$5:$L5<0)*($L$5:$L5) ) Even C5 doesn't have any formula just I am putting date but still taking long time than normal. Any idea to make it faster ? Thanks in advance |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Very Slow Calculating
You showed the formulas/data in row 5.
How far down do these formulas/data go? The greater the number of rows, the longer =sumproduct() will take to calc. If sl has a lot of rows, then the =vlookup()'s will take a long time, too. Tufail wrote: My formula calculating is very slow, formula is as under & my active cell is E5 ! E5 12345 J5 =IF($E5="","",IF(ISNA(VLOOKUP($E5,sl,11,)),"",VLOO KUP($E5,sl,11,))) L5 6,200 (this cell has unit price) M5 115.00 (this cell has exchange rate) N5 =$L5*IF($M5=0,1,$M5) O5 =SUMPRODUCT(($E$5:$E5=$E5)*($L$5:$L5<0)*($L$5:$L5) ) Even C5 doesn't have any formula just I am putting date but still taking long time than normal. Any idea to make it faster ? Thanks in advance -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Very Slow Calculating
all ranges are 5 ~ 4000 and i am using MO 2007.
"Dave Peterson" wrote: You showed the formulas/data in row 5. How far down do these formulas/data go? The greater the number of rows, the longer =sumproduct() will take to calc. If sl has a lot of rows, then the =vlookup()'s will take a long time, too. Tufail wrote: My formula calculating is very slow, formula is as under & my active cell is E5 ! E5 12345 J5 =IF($E5="","",IF(ISNA(VLOOKUP($E5,sl,11,)),"",VLOO KUP($E5,sl,11,))) L5 6,200 (this cell has unit price) M5 115.00 (this cell has exchange rate) N5 =$L5*IF($M5=0,1,$M5) O5 =SUMPRODUCT(($E$5:$E5=$E5)*($L$5:$L5<0)*($L$5:$L5) ) Even C5 doesn't have any formula just I am putting date but still taking long time than normal. Any idea to make it faster ? Thanks in advance -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Very Slow Calculating
4000 =sumproduct() formulas sound like a lot to me. Especially since each uses
more rows than the last. Maybe you could use helper columns and alternative formulas--or live with the delay??? Tufail wrote: all ranges are 5 ~ 4000 and i am using MO 2007. "Dave Peterson" wrote: You showed the formulas/data in row 5. How far down do these formulas/data go? The greater the number of rows, the longer =sumproduct() will take to calc. If sl has a lot of rows, then the =vlookup()'s will take a long time, too. Tufail wrote: My formula calculating is very slow, formula is as under & my active cell is E5 ! E5 12345 J5 =IF($E5="","",IF(ISNA(VLOOKUP($E5,sl,11,)),"",VLOO KUP($E5,sl,11,))) L5 6,200 (this cell has unit price) M5 115.00 (this cell has exchange rate) N5 =$L5*IF($M5=0,1,$M5) O5 =SUMPRODUCT(($E$5:$E5=$E5)*($L$5:$L5<0)*($L$5:$L5) ) Even C5 doesn't have any formula just I am putting date but still taking long time than normal. Any idea to make it faster ? Thanks in advance -- Dave Peterson -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Very Slow Calculating
Ok i will try to reduce range, anyway thank you very much for your attention.
"Dave Peterson" wrote: 4000 =sumproduct() formulas sound like a lot to me. Especially since each uses more rows than the last. Maybe you could use helper columns and alternative formulas--or live with the delay??? Tufail wrote: all ranges are 5 ~ 4000 and i am using MO 2007. "Dave Peterson" wrote: You showed the formulas/data in row 5. How far down do these formulas/data go? The greater the number of rows, the longer =sumproduct() will take to calc. If sl has a lot of rows, then the =vlookup()'s will take a long time, too. Tufail wrote: My formula calculating is very slow, formula is as under & my active cell is E5 ! E5 12345 J5 =IF($E5="","",IF(ISNA(VLOOKUP($E5,sl,11,)),"",VLOO KUP($E5,sl,11,))) L5 6,200 (this cell has unit price) M5 115.00 (this cell has exchange rate) N5 =$L5*IF($M5=0,1,$M5) O5 =SUMPRODUCT(($E$5:$E5=$E5)*($L$5:$L5<0)*($L$5:$L5) ) Even C5 doesn't have any formula just I am putting date but still taking long time than normal. Any idea to make it faster ? Thanks in advance -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Slow Excel Navigation with Up / Down Arrow and slow scrolling | Excel Discussion (Misc queries) | |||
| Calculating is very slow | Excel Discussion (Misc queries) | |||
| Microsoft request for slow-calculating workbooks | Excel Discussion (Misc queries) | |||
| Microsoft request for slow-calculating workbooks | Excel Worksheet Functions | |||
| Calculating recurring date in following month, calculating # days in that period | Excel Worksheet Functions | |||
Hybrid Mode
