# Excel Workout 8 - Solver

Welcome to Excel Workout #8!
Difficulty Level:

This week’s challenge is designed to test your knowledge on Solver What-If Analysis Add-In.

Solver
Solver is a What-If Analysis tool in Microsoft Excel that helps you find the optimal solution for a specific problem by changing multiple variables in a spreadsheet. It is used when you have a complex problem with multiple variables and constraints and want to find the best possible solution. Solver is a tool that uses mathematical algorithms to analyze data and generate solutions based on certain criteria or constraints.

Goals

• Minimize the delivery expenses by devising a plan for distributing goods from 2 warehouses to stores (D24).
• This plan should consider both the quantity of goods required by each store and the capacity of the warehouses.

Submission

Period
This workout will be released on Thursday April 20, 2023 , and the author’s solution will be posted on Wednesday April 26, 2023 .

Challenge #8.xlsx (8.5 MB)

Good luck,

Ilgar Zarbaliyev

1 Like

Formula

``` =LET(de,C11:G12,req,C22:G22,cap,J18:J19,REDUCE(de*0,SEQUENCE(COUNT(de)),LAMBDA(u,w, LET(r,req-MMULT(SEQUENCE(,ROWS(u))^0,u),c,cap-MMULT(u,SEQUENCE(COLUMNS(u))^0), b,IF((u=0)*r*c,de*10^6+r+c),m,b=MIN(b),IF(m,MIN(IF(m,r),IF(m,c)),u)))))```

Solver as in Pic

Summary

Challenge #8.xlsx (219.5 KB)

2 Likes

Created a report for it.

The cost of delivering goods to stores (Euro per item)
Store A Store B Store C Store D Store E
Warehouse - 1 150.00 € 120.00 € 90.00 € 30.00 € 45.00 €
Warehouse - 1 120.00 € 150.00 € 24.00 € 96.00 € 90.00 €
Delivery routes
Store A Store B Store C Store D Store E Total Capacity
Warehouse - 1 0 pcs 240 pcs 0 pcs 960 pcs 1200 pcs 2400 pcs 2400 pcs
Warehouse - 1 900 pcs 450 pcs 450 pcs 0 pcs 0 pcs 1800 pcs 2100 pcs
Total 900 pcs 690 pcs 450 pcs 960 pcs 1200 pcs
Requried, units 900 pcs 690 pcs 450 pcs 960 pcs 1200 pcs
Total Shipping Cost 2,97,900.00 €

Challenge #8.xlsx (8.5 MB)

3 Likes

@borydobon , We can’t expect less from the Excel Wizard. Presenting it with a formula is amazing.

2 Likes

Solver solves. Thanks!
Challenge #8.xlsx (8.5 MB)

1 Like

This was definitely a challenging one for me. That’s because i have not used it before. I had to watch some tutorials on YouTube to fully grasp it. Thanks for this challenge @IlgarZarbaliyev . I learnt something new. Excel wizard @borydobon , I have seen your formula approach and it’s quite phenomenal.

Challenge #8.xlsx (8.5 MB)

2 Likes

1 Like

I’ve never used Solver before but reviewing some youtube video. I think I got the right answer.
File is attached

Thanks for the workout.

Keith

2 Likes

great workout. I did get some weird number in cell C18, but if I change format to number its 0, but I don’t know.

Solution
``````Cell D24:
=SUMPRODUCT(C11:G11;C18:G18)+SUMPRODUCT(C12:G12;C19:G19)
``````

Screenshot:

1 Like

Thank you for participating in the Excel Challenge related to Solver Add-In!
I hope you found this challenge to be a fun and engaging way to improve your Excel skills and learn more about how to work with Solver in Excel.

Here is my solution to Workout #8:

Select cell D24.
Enter the next formula:
=SUM(C11:G11C18:G18)+SUM(C12:G12C19:G19)
Click on File Tab. Then, select More, Options, Add-Ins, Manage, Excel Add-Ins and click on Go. Check Solver.

Go to Data Tab. Select Solver from Analyze Group.

• Set Objective - \$D\$24

• Check Min

• By changing variable cells: \$C\$18:\$G\$19

• Subject to the Constraints:

1. \$C\$18:\$G\$19

2. \$C\$18:\$G\$19

3. \$C\$18:\$G\$19

Click Solve.

Challenge #8 with Solution.xlsx (8.5 MB)

Once again, thank you for participating in this Excel Challenge, and I look forward to seeing you in future challenges and learning opportunities!

When i select solve i m getting error message ( objective Cell contents must be a formula.

1 Like

That’s right.
In the cell enter your formula that will calculate delivery cost.
Then apply Solver.

htis is my entry \$D\$24

1 Like

still getting same error message d24 has the formula and objective cell is set to \$d\$24.

I m going to record a video on this

1 Like

Great workout. Such a unique scenario

1 Like

Thank you, @sam.mckay .