Allocation Optimization using Solver

In a nutshell, Solver can not work with CountIf and If functions in the
model. This is because these are discontinuous functions. Solver is
not capable of determining a "finite difference" to help with a
derivative. Solver will often give up without warning at the first sign
of confusion.

As this model is set up as a Simplex LP neither Simplex nor GRG Nonlinear will work. I did a run using the "Evolutionary" engine an lo and behold solver managed to find a solution.

What I did changed range F5:F54 to integers and set a min value for this range >= 1 and a max value <= 3, and I also specified a constraint for K5 => 21

Solver seemed happy with this a found a solution.

Evolut_solver.jpg

To see image properly right click on it and select "open in new tab"

Alf

Assuming you are minimizing the difference between the average and the sum for each month, the problem can be solved exactly with Simplex LP.

I have added some binary variables and used the function SUMPRODUCT to override Solver limits. Moreover, there are three dummy variables as a workaround for the (non-linear) ABS function.

Solver.jpg

I have set integer optimization to 0% as a test for my new PC, but I wouldn't suggest it as a rule. 0.5% or 1% should be fine in most situations.