Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculation time
Hello,
I have a large data file (10.000 rows / 10 columns) from wich I have to process data. I use a lot of index match formula's, lookup tables and data validations, in multiple sheets Now the calculation of the sheet takes about 25 seconds after each input. How can I speed up the calculation time, or calculate only the cells that are actualy changed after input. Any help or information about this matter would be appreciated very much, Thanks in advance -- There are only 10 types of people in the world: Those who understand binary and those who don''t. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculation time
Excel only calculates a certain number of cells, any that change, or are
dependent upon cells that change. To reduce the calculation times, you need to be smart, reduce the number of cells dependent upon a change. There are some hints and tips at http://www.decisionmodels.com/calcsecrets.htm -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "BKO" wrote in message ... Hello, I have a large data file (10.000 rows / 10 columns) from wich I have to process data. I use a lot of index match formula's, lookup tables and data validations, in multiple sheets Now the calculation of the sheet takes about 25 seconds after each input. How can I speed up the calculation time, or calculate only the cells that are actualy changed after input. Any help or information about this matter would be appreciated very much, Thanks in advance -- There are only 10 types of people in the world: Those who understand binary and those who don''t. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculation time
When there are too many dependencies, every cell gets recalculated whether it
is in a dependency list or not. Macro functions can be used to reduce the effective number of dependencies. (they can also increase the dependencies when done wrong) "Bob Phillips" wrote: Excel only calculates a certain number of cells, any that change, or are dependent upon cells that change. To reduce the calculation times, you need to be smart, reduce the number of cells dependent upon a change. There are some hints and tips at http://www.decisionmodels.com/calcsecrets.htm -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "BKO" wrote in message ... Hello, I have a large data file (10.000 rows / 10 columns) from wich I have to process data. I use a lot of index match formula's, lookup tables and data validations, in multiple sheets Now the calculation of the sheet takes about 25 seconds after each input. How can I speed up the calculation time, or calculate only the cells that are actualy changed after input. Any help or information about this matter would be appreciated very much, Thanks in advance -- There are only 10 types of people in the world: Those who understand binary and those who don''t. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculation time
I don't know if I understand everyting right, So to decrease the calculation time it is better to split the formula's up in more cells, in stead of using a complex formula (and get a direct result), I already use dynamic ranges, but it seems to make no difference. It is not possible for me to use sorted ranges, Are there any other tips to decrease the calculation time Thanks for the info so far -- There are only 10 types of people in the world: Those who understand binary and those who don''''t. "bj" wrote: When there are too many dependencies, every cell gets recalculated whether it is in a dependency list or not. Macro functions can be used to reduce the effective number of dependencies. (they can also increase the dependencies when done wrong) "Bob Phillips" wrote: Excel only calculates a certain number of cells, any that change, or are dependent upon cells that change. To reduce the calculation times, you need to be smart, reduce the number of cells dependent upon a change. There are some hints and tips at http://www.decisionmodels.com/calcsecrets.htm -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "BKO" wrote in message ... Hello, I have a large data file (10.000 rows / 10 columns) from wich I have to process data. I use a lot of index match formula's, lookup tables and data validations, in multiple sheets Now the calculation of the sheet takes about 25 seconds after each input. How can I speed up the calculation time, or calculate only the cells that are actualy changed after input. Any help or information about this matter would be appreciated very much, Thanks in advance -- There are only 10 types of people in the world: Those who understand binary and those who don''t. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculation time
Do your sheets work back and forth?
if each sheet is independant, you could have a macro which would make only formulas in that sheet calculate when you change something in that sheet. if you have to make several changes at a time to the master data sheet you could turn off calculations when that sheet is active. "BKO" wrote: I don't know if I understand everyting right, So to decrease the calculation time it is better to split the formula's up in more cells, in stead of using a complex formula (and get a direct result), I already use dynamic ranges, but it seems to make no difference. It is not possible for me to use sorted ranges, Are there any other tips to decrease the calculation time Thanks for the info so far -- There are only 10 types of people in the world: Those who understand binary and those who don''''t. "bj" wrote: When there are too many dependencies, every cell gets recalculated whether it is in a dependency list or not. Macro functions can be used to reduce the effective number of dependencies. (they can also increase the dependencies when done wrong) "Bob Phillips" wrote: Excel only calculates a certain number of cells, any that change, or are dependent upon cells that change. To reduce the calculation times, you need to be smart, reduce the number of cells dependent upon a change. There are some hints and tips at http://www.decisionmodels.com/calcsecrets.htm -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "BKO" wrote in message ... Hello, I have a large data file (10.000 rows / 10 columns) from wich I have to process data. I use a lot of index match formula's, lookup tables and data validations, in multiple sheets Now the calculation of the sheet takes about 25 seconds after each input. How can I speed up the calculation time, or calculate only the cells that are actualy changed after input. Any help or information about this matter would be appreciated very much, Thanks in advance -- There are only 10 types of people in the world: Those who understand binary and those who don''t. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculation time
Yes, in a manner such that a partial formula is only effected once by a
calculation, and if the result doesn't change, all its dependent cells aren't recalculated. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "BKO" wrote in message ... I don't know if I understand everyting right, So to decrease the calculation time it is better to split the formula's up in more cells, in stead of using a complex formula (and get a direct result), I already use dynamic ranges, but it seems to make no difference. It is not possible for me to use sorted ranges, Are there any other tips to decrease the calculation time Thanks for the info so far -- There are only 10 types of people in the world: Those who understand binary and those who don''''t. "bj" wrote: When there are too many dependencies, every cell gets recalculated whether it is in a dependency list or not. Macro functions can be used to reduce the effective number of dependencies. (they can also increase the dependencies when done wrong) "Bob Phillips" wrote: Excel only calculates a certain number of cells, any that change, or are dependent upon cells that change. To reduce the calculation times, you need to be smart, reduce the number of cells dependent upon a change. There are some hints and tips at http://www.decisionmodels.com/calcsecrets.htm -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "BKO" wrote in message ... Hello, I have a large data file (10.000 rows / 10 columns) from wich I have to process data. I use a lot of index match formula's, lookup tables and data validations, in multiple sheets Now the calculation of the sheet takes about 25 seconds after each input. How can I speed up the calculation time, or calculate only the cells that are actualy changed after input. Any help or information about this matter would be appreciated very much, Thanks in advance -- There are only 10 types of people in the world: Those who understand binary and those who don''t. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculation time
That looks interesting,
I have one large sheat with data (import, 10.000 rows), one sheet with extrexted information (Data, 5000 rows), and a sheet where the extrexted data is combined with manual input(input (5000 rows) I only need calculation after an import of the txt file, and after the manual input is completed At the end the information is combined in 4 or 5 sheets neccesary to generate a report. Can You give me some help by achiving this. Thank a lot for your input, -- There are only 10 types of people in the world: Those who understand binary and those who don''''t. "bj" wrote: Do your sheets work back and forth? if each sheet is independant, you could have a macro which would make only formulas in that sheet calculate when you change something in that sheet. if you have to make several changes at a time to the master data sheet you could turn off calculations when that sheet is active. "BKO" wrote: I don't know if I understand everyting right, So to decrease the calculation time it is better to split the formula's up in more cells, in stead of using a complex formula (and get a direct result), I already use dynamic ranges, but it seems to make no difference. It is not possible for me to use sorted ranges, Are there any other tips to decrease the calculation time Thanks for the info so far -- There are only 10 types of people in the world: Those who understand binary and those who don''''t. "bj" wrote: When there are too many dependencies, every cell gets recalculated whether it is in a dependency list or not. Macro functions can be used to reduce the effective number of dependencies. (they can also increase the dependencies when done wrong) "Bob Phillips" wrote: Excel only calculates a certain number of cells, any that change, or are dependent upon cells that change. To reduce the calculation times, you need to be smart, reduce the number of cells dependent upon a change. There are some hints and tips at http://www.decisionmodels.com/calcsecrets.htm -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "BKO" wrote in message ... Hello, I have a large data file (10.000 rows / 10 columns) from wich I have to process data. I use a lot of index match formula's, lookup tables and data validations, in multiple sheets Now the calculation of the sheet takes about 25 seconds after each input. How can I speed up the calculation time, or calculate only the cells that are actualy changed after input. Any help or information about this matter would be appreciated very much, Thanks in advance -- There are only 10 types of people in the world: Those who understand binary and those who don''t. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time calculation (Subraction of Idle Time) | Excel Discussion (Misc queries) | |||
Time calculation | Excel Worksheet Functions | |||
Ignoring Time in a Date Time Calculation | Excel Worksheet Functions | |||
time calculation with military time | Excel Worksheet Functions | |||
Time Calculation | New Users to Excel |