Forum Discussion

Lost_inExcel's avatar
Lost_inExcel
Copper Contributor
May 09, 2024
Solved

Finding the best combination of values using solver

Question: How do I use Excel built in solver to get the correct combination? Objective: To purchase parts at lowest cost. Constraints: Divvy up among 3 suppliers/out of 5 suppliers ; a total of 19 ...
excel
  • djclements's avatar
    djclements
    May 11, 2024

    Lost_inExcel I'm not entirely sure, without seeing what went wrong. If you could save and attach the file (or provide a screenshot), I could possibly offer something more concrete. What version of Excel are you using? The formulas I used will only work with Excel for MS365. This is what it looks like when I input $1 for all parts for Sup1:

     

    Min Cost via Data Table

     

    As long as you input any Row_ID with the lowest cost (1 thru 6) in cell F13, it should assign all 19 parts to Sup1.

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
May 13, 2024

Lost_inExcel 

The attached works by exhaustive search.

=LET(
    distinct,       UNIQUE(part),
    supplierH,      TOROW(UNIQUE(supplier)),
    costArray,      MINIFS(cost, part, distinct, supplier, supplierH),
    combinations,   Combinationsλ(N, m),
    totalCosts,     BYROW(
        combinations,
        LAMBDA(combin, SUM(BYROW(FILTER(costArray, combin), MIN)))
    ),
    selected,       XLOOKUP(MIN(totalCosts), totalCosts, combinations),
    filtered,       FILTER(costArray, selected),
    bestCost,       BYROW(filtered, MIN),
    partBySupplier, IF(filtered = bestCost, distinct, ""),
    partList,       TOCOL(BYCOL(partBySupplier, LAMBDA(p, TEXTJOIN(", ", , p)))),
    return,         VSTACK(
        HSTACK("Best cost:", SUM(bestCost)),
        HSTACK(TOCOL(FILTER(supplierH, selected)), partList)
    ),
    CHOOSE(line, distinct, supplierH, costArray, combinations, totalCosts, selected, filtered, bestCost, partBySupplier, partList, return)
)

Just for fun, the workbook allows you to step through the formula line by line

Lost_inExcel's avatar
Lost_inExcel
Copper Contributor
May 14, 2024

PeterBartholomew1 

 

When I changed the combination to 5C2, I get the following result, please help.

 

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    May 14, 2024

    Lost_inExcel 

    That is odd.  This is what I get

    It appears to be a problem with the Lambda function Combinationsλ

    Combinationsλ
= LAMBDA(n, m,
    LET(
        binary, BASE(SEQUENCE(2 ^ n), 2, n),
        bits, --MID(binary, SEQUENCE(1, n), 1),
        criterion, BYROW(VALUE(bits), SUM) = m,
        FILTER(bits, criterion)
    )
);

    It is a brute force approach that starts with any number of objects taken from N and only then filters for the m objects required.  Now as I look, I think the problem is that I have used an eta-reduced Lambda function and you are not using the insiders beta version of Excel.  Either switching to insiders channel or introducing an anonymous Lambda function to perform the summation by row should work

    Combinationsλ
= LAMBDA(n, m,
    LET(
        binary,    BASE(SEQUENCE(2 ^ n), 2, n),
        bits,    --MID(binary, SEQUENCE(1, n), 1),
        criterion, BYROW(VALUE(bits), LAMBDA(x, SUM(x))) = m,
        FILTER(bits, criterion)
    )
);

    Sorry about setting traps for you :sad:.  

Resources