1

I have a rather complex spreadsheet, with several hundred computationally intensive formulas (indirect(), sumproduct() and so on). I would like to add some optimization for one of the rows of this spreadsheet.

The optimization is a fairly trivial constrained minimax, nothing more complex than matrix addition and multiplication. Items of one of these matrices are calculated with the slow-to-compute formulas, but for the purposes of the optimization values of the formulas remain constant (they do not depend on the optimization variables in any way). Unfortunately, Excel Solver does not seem to take this into account, as it hangs and/or fails to produce meaningful results; but if I copy and paste-as-values the cells into a new spreadsheet, the optimization runs fine.

So, my question: is there a way to make Excel or Excel Solver treat certain cells as constants and not recalculate them during the optimization process?

Improve this question

1 Answer 1

Reset to default
1

One approach is to use macros to do what you sugget:

  1. have the first macro copy the problem cells and PasteSpecialValue back into them
  2. run Solver (or have the macro run Solver)
  3. use another macro to re-enter the formulas in the problem cells
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .