Solver - SolverAdd using variable in variable constrains doesn't work

[drrazor]

Hello I've got problem with the following code using Solver add. The problem concerns first two SolverAdd lines - they just doesn't add a thing in constrains window, and I try to figure out why:

Please Login or Register  to view this content.

Any ideas why first and second constrains doesn't add in into Solver and doesn't work? Adding values without variables makes them work, but it's not desirable:

Please Login or Register  to view this content.

But still I can't figure out first one. Any ideas?

Thanks for all suggestions!
Regards,
P.

[drrazor]

Any ideas?

[shg]

Maybe something closer to ...

Please Login or Register  to view this content.

[MrShorty]

I could be wrong, but I don't think Solver can accept a "formula" like E69*F65 in the constraint fields. I think if you want this kind of constraint, you will want to add a helper cell to calculate the constraint (maybe G65=E69*F65), then reference G65 in your Solver constraint.

[shg]

It's not a formula, MrS, it resolves to a value at runtime. It doesn't change over the course of Solver's progress.

[MrShorty]

My mistake.

[shg]

Not necessarily -- that may have been the OP's intent, it's just not the way it works.

[drrazor]

Yes, I've put there a formula, because a.Offset(-12, -3) is one of the variables that should be changed to find optimal solution.

When I enter it manually like this V is for VALUE, F for FORMULA, but formula it's not complicated it's like VLOOKUP and SUM, only in last $J$81 this formula is more complex:
Capture.GIF

[shg]

"(cell) = integer" means nothing to Solver, nor does "(cell) = (formula)"

See http://www.solver.com/content/basic-...ete-constraint

[drrazor]

Thanks both of you guys! That solution worked really well, I replaced function from constraint, put it into cell and added that cell to variable.

The question now is how to speed it up? Calculation takes a lot of time, something like 25 seconds multiplied by 1000 cases equals 'forever'.

Regards,
P

[drrazor]

http://www.excelforum.com/excel-prog...ml#post3723643

[shg]

You're welcome.