Solver: How to maximize a mark average (non-linear problem) in a linear way?

Hi everyone,

It's my first post here. I will try to be clear on my problem by giving you a simple example .

My problem is a problem of linearity with the Excel Solver and I don't know how to handle formulas and restrictions in order to make it linear.

Let's say we have to buy two items k =1,2 and we have two suppliers i=1,2. We give a mark m_ik for each item k provided by supplier i.
Supplier 1 item 1: 9/10
Supplier 1 item 2: 4/10
Supplier 2 item 1: 3/10
Supplier 2 item 2: 8/10

I create a decision variable x_ik that takes the value 1 if the item k is allocated to supplier i, 0 otherwise.

My objective is to maximize the average note of the allocation, that is to say SUM i=1,2;j=1;2 (x_ikm_ik)/SUM i=1,2;j=1;2 (x_ik)

The problem is that if I divide by SUM(x_ik). My problem becomes non-linear and I don't know how to do.

Does anyone have a solution please?

Thank you a lot for your time and help

I let you the model to help you understand my problem
exemple.JPG

Here is the sample model

Thank you for your response.

I wanted to make the problem linear because it doesn't work when I try to solve it with a non-linear resolution method (even if I eliminate the div/0 error). I am using the Open Solver add-in.
What solver and resolution method are you using please?

And even if it works with a non-linear approach, I would rather prefer a linear one, if it is possible.

Thank you.

As the OpenSolver (CBC engine) is a linear solver it will not work on your problem as you do a division. To run a solve a non-linear problem you can use "NOMAD", "Bonmin" or "Couenne".

See link:

https://opensolver.org/guide-to-solvers/

or just the excel solver that comes with solver 2016 but you must use the "GRG nonlinear" engine.

If you really wish to run OpenSolver you must run it as a two stage rocket using a macro.

To test you can run macro "OpenSolver_loop" but first you must set a reference to "OpenSolver" in VBA.

Select "Developers tab -> Click Visual Basic Icon -> click "Tools" -> click "References" and tick box marked "OpenSolver"

OpenSolv_ref.jpg

The macro "Excel_Solver_loop" is for the Excel "Simplex LP" also there a reference to the "Excel" solver must be set.

Alf