Excel Solver - Limit Number of Variables

Question

Here is the problem Excel solver need to change the values of variables a, b, c, d, e, f in a way that it gives the optimization solution considering a constraint that Count of Non Zero values for variables a, b, c, d, e, f should be less than 3

Read through many online articles but didn't find a solution

Please provide some input about the constraint, or is the only constraint that the count of non-zero values should be less than 3? If that's the case there are too many optimal solutions... — Luuk
– Luuk, Commented Jul 10, 2020 at 17:24
Each variable has different weightage, A variable has a weightage 0.1 similarly others variables has weightage i.e B (0.2), C (0.3), D(0.4),E(0.5),F(0.6). Values for variables need to change in a way that sumproduct value * weightage should be equal to 12 but only 3 variables need to be non zero. — Anto
– Anto, Commented Jul 12, 2020 at 16:16

Luuk · Accepted Answer · 2020-07-12 17:14:23Z

0

In Excel, you should have the Solver Add-In (see: this is you do not have it)

Next I created a sheet like this (using control+backquoot to show the formulas)

After make field G3 active, and click on Data/Solver via the menu.

In Dutch 'Solver' is 'Oplosser', the rest of the text in this screenshot is also in Dutch (you can say thanks to Microsoft for that! (i do not know where you can do that!))

Anyway, after entering the correct values, and hitting the 'Solve' (or 'Opslossen') button, I do get:

A correct solution to your problem is: A=4; E=4; F=16 and (B=0; C=0; D=0)

I did assume the numbers in A...F should not have decimals, be: 'geheeltallig'

answered Jul 12, 2020 at 17:14

Luuk

15.4k5 gold badges28 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel Solver - Limit Number of Variables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related