Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Slow Calculation
hello Everyone,
This small formula over 300 rows and 16 columns takes about 20 mins, can I minimize that? =IF(ISERROR(VLOOKUP($A7,'CHE all sku'!$A:$Z,'MTL aux'!B7,FALSE)),0,VLOOKUP($A7,'CHE all sku'!$A:$Z,'MTL aux'!B7,FALSE))-IF(ISERROR(VLOOKUP($A7,'CHE MBE sku'!$A:$Z,'MTL aux'!B7,FALSE)),0,VLOOKUP($A7,'CHE MBE sku'!$A:$Z,'MTL aux'!B7,FALSE))-IF(ISERROR(VLOOKUP($A7,'CHE MEX sku'!$A:$Z,'MTL aux'!B7,FALSE)),0,VLOOKUP($A7,'CHE MEX sku'!$A:$Z,'MTL aux'!B7,FALSE)) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Slow Calculation
If you know the maximum number of rows, I would recommend not to use the full
columns in the VLOOKUP ranges ($A:$Z), but use a fixed range instead ($A$1:$Z$5000, for example). Even better, create names for those ranges and use the names instead. One other thing that you may try is using the MATCH formula in the IF part of the formula, if possible also on a limited range. For example of the first part of the formula could look like: =IF(ISERROR(MATCH($A7,all_codes,0)),0,VLOOKUP($A7, all_data,'MTL aux'!B7,FALSE)) where "all_codes" is the range 'CHE all sku'!$A$1:$A$5000, and "all data" is 'CHE all sku'!$A$1:$Z$5000. Hope this helps, Miguel. "JavyD" wrote: hello Everyone, This small formula over 300 rows and 16 columns takes about 20 mins, can I minimize that? =IF(ISERROR(VLOOKUP($A7,'CHE all sku'!$A:$Z,'MTL aux'!B7,FALSE)),0,VLOOKUP($A7,'CHE all sku'!$A:$Z,'MTL aux'!B7,FALSE))-IF(ISERROR(VLOOKUP($A7,'CHE MBE sku'!$A:$Z,'MTL aux'!B7,FALSE)),0,VLOOKUP($A7,'CHE MBE sku'!$A:$Z,'MTL aux'!B7,FALSE))-IF(ISERROR(VLOOKUP($A7,'CHE MEX sku'!$A:$Z,'MTL aux'!B7,FALSE)),0,VLOOKUP($A7,'CHE MEX sku'!$A:$Z,'MTL aux'!B7,FALSE)) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Slow Calculation
Good advice from Miguel!
In addition: you can halve the number of VLOOKUPs by adding an extra cell, in which you do the VLOOKUP; in the next cell you test it for ISNA, and return 0 or the result of the VLOOKUP. Next point: 4th argument FALSE is very, very slow, because the table has to be searched from top to bottom sequentially. Of course shortening the range, as Miguel suggested, may help a lot. If it still is too slow: can you sort your data? If so, you can use True as 4th argument (or omit it) and check yourself that the key returned is the right one. Fastest of all: Sort the data, use MATCH to locate the row (4th argument = 1), ISNA() to check for presence, INDEX() to pick out the right item. -- Kind regards, Niek Otten "Miguel Zapico" wrote in message ... | If you know the maximum number of rows, I would recommend not to use the full | columns in the VLOOKUP ranges ($A:$Z), but use a fixed range instead | ($A$1:$Z$5000, for example). Even better, create names for those ranges and | use the names instead. | One other thing that you may try is using the MATCH formula in the IF part | of the formula, if possible also on a limited range. For example of the | first part of the formula could look like: | =IF(ISERROR(MATCH($A7,all_codes,0)),0,VLOOKUP($A7, all_data,'MTL | aux'!B7,FALSE)) | where "all_codes" is the range 'CHE all sku'!$A$1:$A$5000, and "all data" is | 'CHE all sku'!$A$1:$Z$5000. | | Hope this helps, | Miguel. | | "JavyD" wrote: | | hello Everyone, | | This small formula over 300 rows and 16 columns takes about 20 mins, can I | minimize that? | | =IF(ISERROR(VLOOKUP($A7,'CHE all sku'!$A:$Z,'MTL | aux'!B7,FALSE)),0,VLOOKUP($A7,'CHE all sku'!$A:$Z,'MTL | aux'!B7,FALSE))-IF(ISERROR(VLOOKUP($A7,'CHE MBE sku'!$A:$Z,'MTL | aux'!B7,FALSE)),0,VLOOKUP($A7,'CHE MBE sku'!$A:$Z,'MTL | aux'!B7,FALSE))-IF(ISERROR(VLOOKUP($A7,'CHE MEX sku'!$A:$Z,'MTL | aux'!B7,FALSE)),0,VLOOKUP($A7,'CHE MEX sku'!$A:$Z,'MTL aux'!B7,FALSE)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Slow Calculation speeds in big spreadsheet | Excel Worksheet Functions | |||
Does this slow down calculation? | Excel Discussion (Misc queries) | |||
Calculation Setting in Excel | Excel Discussion (Misc queries) | |||
How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions | |||
How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions |