[FIXED] Differences between Excel Solver & PuLP solver in python,

Issue

Solver in excel is more efficient like PuLP Solver, or I made some mistake. Please can you help me?

Python PuLP (Not correct results):

from pulp import *
p = (120, 72, 80)
o = (0.85, 0.85, 0.80)

lp= LpProblem("Linka1_2_3", LpMaximize)
print("OBJECTIVE1: ",lp.objective)

x1 = LpVariable(name="Time:Line1Ref1",lowBound=0)
x2 = LpVariable(name='Time:Line2Ref1',lowBound=0)

print("Constrains: ",lp.constraints)

# Objective function
lp += lpSum(102*x1 + 61.2*x2)

# Constrains
lp += 102*x1 <= 500
lp += 61.2 *x2  <=500
lp += 102*x1 + 61.2*x2  <=500
lp += x1 <= 3.5
lp += x2 <= 3.5

status = lp.solve()
print("Status:", status, "   ---1:optimal, 2:not solved, 3:infeasible, 4:unbounded, 5:undef")

#Print solution
i=0
for var in lp.variables():
    print(f" ",var, "=", value(var), "Tzn. ",((p[i]*o[i]))," PxO a cas straveny", value(var)*(p[i]*o[i])," pcs")
    i += 1
print("OPT =", value(lp.objective))

Result:
Time:Line1Ref1 = 2.8019608 Tzn.  102.0  PxO a cas straveny 285.8000016  pcs
Time:Line2Ref1 = 3.5 Tzn.  61.199999999999996  PxO a cas straveny 214.2  pcs

Time X1 + X2 = 6.3019....

Correct result should be: 5.836601 like in Excel (6.3 is too high & not correct solution)

Where in code I have fault?

Here is XLSX table with formulas:

Solution

Your problem is under-determined, so there are multiple solutions that produce the optimal value of 500 right at its bound. PuLP is more than capable of producing such a solution. More importantly, though, you've mis-constructed your problem, and need to replace your constraints and objective based on what it is you really want.

import pandas as pd
import pulp

idx = ('Lin1Ref1', 'Lin2Ref1')
ConstrainsX12 = (3.5, 3.5)
ConstrainsX = (*ConstrainsX12, max(ConstrainsX12))
X12 = [
    pulp.LpVariable(
        name=f'X_{i}', cat=pulp.LpContinuous,
        lowBound=0, upBound=bound)
    for i, bound in zip(idx, ConstrainsX12)
]
X = (*X12, pulp.lpSum(X12))
P = (120, 72)
O = (0.85, 0.85)
PxO = [pi*ji for pi, ji in zip(P, O)]
XxPxO12 = [xi*poi for xi, poi in zip(X, PxO)]
XxPxO = (*XxPxO12, pulp.lpSum(XxPxO12))
Constrains_formula = (500,)*3

lp = pulp.LpProblem(name='Line1_2_3', sense=pulp.LpMinimize)
lp.setObjective(X[-1])

lp.addConstraint(XxPxO[-1] == Constrains_formula[-1])

print(lp)
status = lp.solve()
assert status == pulp.LpStatusOptimal

df = pd.DataFrame(
    index=(*idx, 'B4 + B5:'),
    data={
        'X': [pulp.value(x) for x in X],
        'P': (*P, ''),
        'O': (*O, 'SUMRef1:'),
        'PxO': (*PxO, ''),
        'Solv: XxPxO': [pulp.value(p) for p in XxPxO],
        'Constrains formula': Constrains_formula,
        'Constrains[X]': ConstrainsX,
    },
)
pd.options.display.width = 200
pd.options.display.max_columns = 9
print(df)

                 X    P         O    PxO  Solv: XxPxO  Constrains formula  Constrains[X]
Lin1Ref1  3.500000  120      0.85  102.0        357.0                 500            3.5
Lin2Ref1  2.336601   72      0.85   61.2        143.0                 500            3.5
B4 + B5:  5.836601       SUMRef1:               500.0                 500            3.5

Answered By - Reinderien