CODEWITHSUNDEEP
excel-formulasolvernamed-ranges
user3697176
user3697176

Reputation: 231

Non-contiguous ranges in Excel solver

Can the excel solver handle non-contiguous ranges in a constraint? For example, I want to have decision variables in A1:A5,A7:A10. The solver interface accepts this as a range for decision variables, but if I then want to add a constraint that these variables should be explicitly made nonzero (or any other restriction), the "Add constraint" validator claims that "Cell Reference box is empty or contents are not valid." I get the same error if I use a named range reference instead of the direct string "A1:A5,A7:A10". Do I really have to split the constraint into two separate constraints?

Upvotes: 0

Views: 1370

Answers (1)

Celine
Celine

Reputation: 46

Hello I also faced this issue, And as a work around, you can make the the combination in a different cell then use it in solver as your cell reference for your constraint. you can refer to this link. https://www.solver.com/solver-tutorial-defining-constraints

"General Constraints Suppose that cells A1:A5 contain the percentage of funds to be invested in each of 5 stocks. We would want the sum of these cells to equal 1 (or 100%). To accomplish this, in cell B1 you might calculate the sum of the percentages as =SUM(A1:A5) and then use solver to define a constraint to require that cell B1 = 1."

Upvotes: 2

Related Questions