1

I am looking for an Excel table with formulas (no VBA, no Solver) that can solve LP problems:

Ax=b

0<=x

cx->min

With Solver and VBA, I can do it but I would like a solution which builds only on Excel Formulas. Or a proof that no such system consisting of formulas is possible. Efficiency is not critical.

z32a7ul
  • 3,695
  • 3
  • 21
  • 45
  • 1
    You asked about the simplex algorithm (which is a specific algorithm, which trivially can't be implemented in Excel formulas alone) but then ask simply for solving LP problems (which can be solved in various ways, not just via the simplex algorithm). Which is it? – John Coleman Sep 14 '17 at 14:20
  • @JohnColeman: I don't insist on the simplex algorithm. – z32a7ul Sep 14 '17 at 14:40
  • I don't see how a general solution would be possible, but you could probably come up with solutions that work in special cases (e.g. 3 decision variables together with 3 inequality constraints) by doing a brute-force enumeration of corner points (all of which correspond to solutions of linear systems that can be found in Excel using matrix inverses). Even for just 3 decision variables this would be a lot of work. Why would you want to? – John Coleman Sep 14 '17 at 14:55
  • @JohnColeman: And how can I enumerate corner points? (I suppose corner point means extreme point but I did not read too much math in English.) – z32a7ul Sep 14 '17 at 15:14
  • If you have 3 inequalities, together with the nonnegativity constraints, each extreme value is at the intersection of 3 planes, drawn from the planes corresponding to those inequalities, together with the coordinate planes x=0, y=0, z = 0. There are 6 choose 3 = 20 ways you can pick 3 planes to try to intersect. For each of those 20 choices, there is a 3x3 linear system to solve. Not all systems will be solvable and not all solutions will be feasible, so you would need to check. Borderline possible -- but a lot of work even for this case. – John Coleman Sep 14 '17 at 15:26
  • 1
    Its probably possible since Felienne Hermans has shown that Excel formulas are Turing complete http://www.felienne.com/archives/2974: but presumably you don't actually want to do this? – Charles Williams Sep 14 '17 at 17:57
  • Can you show me an example with a 2×2 A matrix? I think this must be the simplest case but if I saw an example, I could adjust it to my needs. – z32a7ul Sep 14 '17 at 23:24
  • @JohnColeman: If I have 7 unknowns in, 4 equalities and no inequalities beyond the the non-negativity of the 7 unknowns, then its enough to check 7 cases: all the 4 equalities are true + 1 of the 7 unknowns is 0. Is that correct? – z32a7ul Sep 16 '17 at 23:15
  • @z32a7ul That wouldn't follow. The 4 equations would leave you with (at least) 3 degrees of freedom in determining a corner point. There would be 7 choose 3 = 35 ways to get a 7x7 linear system which includes those 4 equations. This assumes that those 4 equations are linearly independent. If not, some corner points might correspond to e.g. 2 of those equations and setting 5 variables equal to 0. The simplex algorithm can handle complications like that, as well as other things (such as what to do when the feasible set is unbounded). Practically, I don't see how mere spreadsheet formulas could. – John Coleman Sep 18 '17 at 10:27

0 Answers0