How to create the equivalent of Excel Solver valueof function?

user_13687648 0
Viewed 258

I have the following equation:
x/0,2 * (0,2+1)+y/0,1*(0,1+1) = 26.34

The initial values of X and Y are set as 4.085 and 0.17 respectively. I need to find the values of X and Y which satisfy the equation and have the lowest common deviation from initially set values. In other words, sum of |4.085 - x| and |0.17 - y| is minimized.

With Excel Solver Valueof Function this easy to find: we insert x and y as variables to be changed to reach 26 in the formula result enter image description here

Here is my python code (I am trying to use sympy for that)

        x,y = symbols('x y')
        eqn = solve([Eq(x/0.2*(0.2+1)+y/0.1*(0.1+1),26)],x,y)
        print(eqn)

I am getting however strange result {x: 4.33333333333333 - 1.83333333333333*y}

Can anyone help me solve this equation?

sympy python
1 Answers

The answer you are obtaining is not strange, it is just the answer to what you ask. You have an equation on two variables x and y, the solution to this problem is in general not unique (sometimes infinite). Now, you can either add an extra condition (inequality for example) or change the numeric Domain in which solutions are possible (like in Diophantine equations). You can do either of them in Sympy, in the following example I find the solution on x to your problem in the Real domain, using solveset:

from sympy import symbols, Eq, solveset

x,y = symbols('x y') 
eqn = solveset(Eq(1.2 * x / 0.2 + 1.1 * y / 0.1, 26), x, Reals) 
print(eqn)

Output:

Intersection(FiniteSet(4.33333333333333 - 1.83333333333333*y), Reals)

As you can see the solution on x is a finite set, that is the intersection between a straight line on y and the Reals. Any particular solution can be found by direct evaluation of y.

This is equivalent to say x = 4.33333333333333 - 1.83333333333333 * y if you evaluate this equation in the guess value y = 0.17, you obtain x = 4.0216 (close to your x = 4.085 guess value).

Edit:

After analyzing the new information added to your question, I think I have finally understood it: your problem is a constrained optimization. Now, I don't use Excel frequently, but it would be my bet that under the hood this optimization is carried out there using Lagrange multipliers. In your particular case, the target function represents the deviation of the solution (x, y) from the point (4.085, 0.17). For convenience, I have chosen this function to be the Euclidean distance between them (absolute values as you suggested can be problematic due to discontinuity of the derivatives). The constraint function is simply the equation you provided. To solve this problem with Sympy, one could use something like this:

import sympy as sp

# Define symbols and functions
x, y, lamb = sp.symbols('x, y, lamb', real=True)
func = sp.sqrt((x - 4.085) ** 2 + (y - 0.17) ** 2)  # Target function
const = 1.2 * x / 0.2 + 1.1 * y / 0.1 - 26  # Constraint function 

# Define Lagrangian
lagrang = func - lamb * const

# Compute gradient of Lagrangian
grad_lagrang = [sp.diff(lagrang, var) for var in [x, y, lamb]]

# Solve the resulting system of equations
spoints = sp.solve(grad_lagrang, [x, y, lamb], dict=True)

# Print stationary points
print(spoints)

Output:

[{x: 4.07047770700637, lamb: -0.0798086884467563, y: 0.143375796178345}]

Since in our case only one stationary point was found, this is the optimal solution (although this is only a necessary condition). The value of the lamb multiplier can be ditched, so x, y = 4.070, 0.1434. Hope this helps.

user_56610010
Related