Forum Discussion
Finding the best combination of values using solver
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.
Lost_inExcel I was able to solve this with Excel for MS365 using array functions and a Data Table. The first step was to generate a list of all possible combinations of 3 suppliers out of 5 using a custom LAMBDA function (PERMA). The second step was to create a MINIFS-based summary report to calculate the minimum cost for the first combination of suppliers using the Row_ID as a single input variable. The third step was to use a Data Table (Data > What-If Analysis > Data Table...) to generate the minimum costs for all combinations of suppliers (for every Row_ID variable) and identify the combination with the lowest total cost. Lastly, the results of the combination with the lowest total cost were filtered to generate the desired output.
Min Cost via Data Table
Please see the attached workbook to view all of the formulas used to generate the final results...
Additional resources:
Thank you very much! Calculation is good.
Had question if :
I artificially put $1/- for each part for Sup1, the by default, it does not assign 19 parts to supplier1.
Why would that be the case
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.
- Thank you, I confirmed your spreadsheet works perfectly in MSoffice365 laptop. So I must use Excel for MS365.
