Solver: Long computation time
PATR974
Posted messages
4
Status
Member
-
PATR974 Posted messages 4 Status Member -
PATR974 Posted messages 4 Status Member -
Hello,
I have a small problem with running the Excel solver (2003) on my Excel workbook. It actually takes a very long time to find a solution when I run it in my workbook (which has 9 sheets), whereas when I run it on a separate workbook with no other sheets (and my original workbook closed), it is much faster.
In my original workbook, my calculations were initially made on a sheet that had links to other cells present on other sheets in my workbook. So I thought I would isolate my calculations on a separate sheet in my workbook so that there would be no influence from other cells on the sheet. I also disabled automatic calculation so that I would only recalculate on this isolated sheet, but after this attempt, the calculation time remained almost unchanged compared to the previous case where my solver was running on a sheet with links.
I would just like my calculations to only apply to the cells addressed by the solver.
How can I do that, please?
(Hoping to be clear)
Thank you very much.
I have a small problem with running the Excel solver (2003) on my Excel workbook. It actually takes a very long time to find a solution when I run it in my workbook (which has 9 sheets), whereas when I run it on a separate workbook with no other sheets (and my original workbook closed), it is much faster.
In my original workbook, my calculations were initially made on a sheet that had links to other cells present on other sheets in my workbook. So I thought I would isolate my calculations on a separate sheet in my workbook so that there would be no influence from other cells on the sheet. I also disabled automatic calculation so that I would only recalculate on this isolated sheet, but after this attempt, the calculation time remained almost unchanged compared to the previous case where my solver was running on a sheet with links.
I would just like my calculations to only apply to the cells addressed by the solver.
How can I do that, please?
(Hoping to be clear)
Thank you very much.
2 answers
What is the problem you need to solve with the solver? Many variables? Constraints? Complicated objective function?
In this type of model, the number of calculations to perform increases exponentially with the complexity of the initial statement. It may be necessary to consider dividing the problem into sub-modules...
--
Remember remember the fifth of November
Gunpowder, treason and plot.
I see no reason why gunpowder, treason
Should ever be forgot...
In this type of model, the number of calculations to perform increases exponentially with the complexity of the initial statement. It may be necessary to consider dividing the problem into sub-modules...
--
Remember remember the fifth of November
Gunpowder, treason and plot.
I see no reason why gunpowder, treason
Should ever be forgot...
It is a nonlinear problem that requires iterative calculation.
- I have a "target cell" that I want to contain a specific number
- I let the solver modify 5 cells that model a degree 4 curve, and I use iterative calculation to link this degree 4 curve to my "target cell."
Actually, I was also thinking about dividing it into modules, but I can't because the target cell involves an iteration that cannot be separated. The iteration takes into account 100 cells; it's true that overall it will take some time, but I would like to solve the "workbook problem" and not change my solver conditions.
As I mentioned, isolating my calculation in another workbook works better... and I don't know why...
Thank you.
- I have a "target cell" that I want to contain a specific number
- I let the solver modify 5 cells that model a degree 4 curve, and I use iterative calculation to link this degree 4 curve to my "target cell."
Actually, I was also thinking about dividing it into modules, but I can't because the target cell involves an iteration that cannot be separated. The iteration takes into account 100 cells; it's true that overall it will take some time, but I would like to solve the "workbook problem" and not change my solver conditions.
As I mentioned, isolating my calculation in another workbook works better... and I don't know why...
Thank you.
To add some details:
- when I apply my solver on my workbook and my original sheet (workbook with 9 sheets and a working sheet with other cells containing formulas but not included in the solver): solver calculation time = 4min and number of "trial solutions" = 16;
- when I work on the same workbook by isolating my solver and the cells involved in the solver calculation on a separate sheet: solver calculation time = 4min and number of "trial solutions" = 16;
- when I open a new workbook with blank sheets and run my solver on this workbook WITH MY ORIGINAL WORKBOOK OPEN (the one from the first two cases above): solver calculation time = 4min and number of "trial solutions" = 16;
- finally, when I close my original workbook and restart the solver on the new workbook and the new blank sheets: solver calculation time = 1min and number of "trial solutions" = 16;
So the fact that my original workbook is open slows down the solver (but does not increase its number of attempts)!
I would like to point out that all my calculations are done with the manual calculation option.
I'm having a bit of trouble solving my problem.....
- when I apply my solver on my workbook and my original sheet (workbook with 9 sheets and a working sheet with other cells containing formulas but not included in the solver): solver calculation time = 4min and number of "trial solutions" = 16;
- when I work on the same workbook by isolating my solver and the cells involved in the solver calculation on a separate sheet: solver calculation time = 4min and number of "trial solutions" = 16;
- when I open a new workbook with blank sheets and run my solver on this workbook WITH MY ORIGINAL WORKBOOK OPEN (the one from the first two cases above): solver calculation time = 4min and number of "trial solutions" = 16;
- finally, when I close my original workbook and restart the solver on the new workbook and the new blank sheets: solver calculation time = 1min and number of "trial solutions" = 16;
So the fact that my original workbook is open slows down the solver (but does not increase its number of attempts)!
I would like to point out that all my calculations are done with the manual calculation option.
I'm having a bit of trouble solving my problem.....