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

Jackdoe

New member
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
 

Attachments

  • Trading data - Sample.xlsx
    72.9 KB · Views: 3
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.

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

Jack-2.png
  • 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

Jack-4.png

For Upper limit of PR data:

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

Jack-5.png

  • Do the same for other limits.
After clicking Solve, I got the following error message from Excel.

Jack-3.png

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
 

Attachments

  • Trading data - Sample.xlsx
    74 KB · Views: 4
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.
 

Online statistics

Members online
0
Guests online
12
Total visitors
12

Forum statistics

Threads
278
Messages
1,198
Members
473
Latest member
tu2815
Top