[Solved] How Do I Get Solver to Optimize Multiple Variables in This Sample Worksheet?

[Jackdoe]

How do I get solver to optimize to the maximum, cell B3, in the attached worksheet, and give me the upper and lower limits for columns H and K through AA that produce the maximum number in cell B3?

Thank you,
Jack

 

[Yousuf Shovon]

How do I get solver to optimize to the maximum, cell B3, in the attached worksheet, and give me the upper and lower limits for columns H and K through AA that produce the maximum number in cell B3?

Hello Jack,

Welcome to ExcelDemy Forum! I understand you want the Solver analysis tool to optimize multiple variables in the given sample worksheet. You also want to set the upper and lower limits for each variable in the Solver Parameters constraints. Well, to do so, you have to manually input each constraint one by one.

Steps:
I am guessing you have the Solver Add-in installed in your Excel. If not, see the below article.

Find Optimal Solution with Linear Programming in Excel​

• Select B3 in the Set Objective field >> H5:AA427 in By Changing Variable Cells >> Click Add to add constraints >> Solve.​

​

• After clicking Add in the Solver Parameters, you need to add constraints for lower and upper limits individually.​

For lower limit of PR data:

$H$5:$H$427 >= =$AD$6

​

For Upper limit of PR data:

$H$5:$H$427 <= =$AE$6

​

• Do the same for other limits.

After clicking Solve, I got the following error message from Excel.



I think it means we have too many variables for analysis. What you can do in this situation are:

• Reduce the number of variables keeping the important ones, or
• Break the problem into smaller parts.

I am attaching the workbook. Let me know if you have further queries.

Regards,
Yousuf Shovon

 

[Jackdoe]

Thank you, Yousuf!

That's what I thought, but I wanted to be sure I wasn't missing something.

Jack

 

[Yousuf Shovon]

Thank you, Yousuf!

That's what I thought, but I wanted to be sure I wasn't missing something.

Jack

Hello Jack,

Thank you for your feedback. It's always good to know we're on the same page. Please do not hesitate to reach out if you have any further questions.

Regards.