Excel Solver: Solving matrix values to match row/column sum according to reference

Question

I've been trying to program Excel Solver to fill the values of a matrix so that the sum of rows and columns match preestablished values. See image:

Excel Problem

I understand this problem has infinite solutions that's why I want to constraint the problem by having a reference matrix whose weights (percentage of total sum) I want to match. I've tried to accomplish this by summing the differences between the weighted reference matrix and the solution matrix and solving to minimize the absolute value:

=((SUM(B19:F31))^2)^(1/2)

Excel sheet \
Solver Parameters

Unfortunately excel cannot find a feasible solution. Any ideas why?

Answer 1

The problem is feasible only if the sum of the rows equals the sum of the columns. That's true for your Reference table, but not for the other tables.

I'm not clear about what your objective function is intended to do relative to the Reference table. But your formula =((SUM(B19:F31))^2)^(1/2) makes no sense. Perhaps you intend to sum the squared differences between each element of the problem table and the reference table? If so, then calculate each squared difference in another table, then sum them and set that as your objective function. Don't include the square root, as that is difficult for the solver to handle, and it won't change the solution.

If Excel's Solver still doesn't work, then download the advanced version of the free add-in OpenSolver https://opensolver.org/installing-opensolver, and use the Bonmin or Couenne non-linear solver.