Best Combination for the Price

[dclasen]

I'm trying to determine a way (maybe without VBA) to find the best combination of product for the price.
It seems like this Knapsack formula I did some research on, but I was hoping for something in the existing formulas/solvers available standard in Excel. Any help is appreciated.

 

[Gary's Student]

Help us to help you.........give more details.

 

[dclasen]

Hi, The picture isn't helpful?

 

[dclasen]

If the picture isn't helpful - here is the excel sheet with the explanation of the expected outcome for one instance.
https://drive.google.com/open?id=0BzWE2rhHRQCfb0Z4M3hNbXczTms

Thanks for the quick reply

 

[mikerickson]

The link isn't helping me much in understanding your question.

I see controller type, presumably the first column is the different types of controller, A,B, C, D, E with the other columns being the various features of that particular controller, including cost in Column H.

Your Desired Amount row shows how many controllers with what features are desired.

But your desired result leaves me confused. Your Desired Amount row calls for a total of 10 controllers.
But your expected result (1 each A & B, 1 each E & B) totals to only 4 controllers.

 

[dclasen]

I see - there are 5 controllers (A-E). The columns are characteristics of the controllers, including the cost.
Therefore, Controller A has 8UI, 2DI, 2AO, 4DORelay, 4DO Triac

If I'm looking for (Desired) 9UI and 1AO - it means I need to select two controllers to get >= UI and still provide at least 1 AO.

When you look at price A and E are the same price so this is why the lowest cost result was two possible combinations.

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][td="bgcolor:#C0C0C0"]

G​

[/td][td="bgcolor:#C0C0C0"]

H​

[/td][td="bgcolor:#C0C0C0"]

I​

[/td][td="bgcolor:#C0C0C0"]

J​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

3​

[/td][td="bgcolor:#F3F3F3"]

Type​

[/td][td="bgcolor:#F3F3F3"]

UI​

[/td][td="bgcolor:#F3F3F3"]

DI​

[/td][td="bgcolor:#F3F3F3"]

Pressure​

[/td][td="bgcolor:#F3F3F3"]

AO​

[/td][td="bgcolor:#F3F3F3"]

DO Relay​

[/td][td="bgcolor:#F3F3F3"]

DO Triac​

[/td][td="bgcolor:#F3F3F3"]

Cost​

[/td][td="bgcolor:#F3F3F3"]

Qty​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

A​

[/td][td]

8​

[/td][td]

2​

[/td][td]

0​

[/td][td]

2​

[/td][td]

4​

[/td][td]

4​

[/td][td]

$ 2.00​

[/td][td]

1​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

B​

[/td][td]

8​

[/td][td]

12​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

$ 1.00​

[/td][td]

1​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

C​

[/td][td]

6​

[/td][td]

0​

[/td][td]

0​

[/td][td]

6​

[/td][td]

5​

[/td][td]

0​

[/td][td]

$ 3.00​

[/td][td]

0​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

D​

[/td][td]

8​

[/td][td]

2​

[/td][td]

0​

[/td][td]

2​

[/td][td]

4​

[/td][td]

4​

[/td][td]

$ 4.00​

[/td][td]

0​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

E​

[/td][td]

7​

[/td][td]

0​

[/td][td]

1​

[/td][td]

4​

[/td][td]

5​

[/td][td]

2​

[/td][td]

$ 2.00​

[/td][td]

0​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]Avail / Cost[/td][td="bgcolor:#E5E5E5"]

16​

[/td][td="bgcolor:#E5E5E5"]

14​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

2​

[/td][td="bgcolor:#E5E5E5"]

4​

[/td][td="bgcolor:#E5E5E5"]

4​

[/td][td="bgcolor:#E5E5E5"]

$ 3.00​

[/td][td][/td][td]B9 and across: =SUMPRODUCT(B4:B8, $I$4:$I$8)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]Req'd[/td][td]

9​

[/td][td]

0​

[/td][td]

0​

[/td][td]

1​

[/td][td]

0​

[/td][td]

0​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]


Use Solver (engine Simplex LP) to minimize H9 by changing I4:I8 subject to the constraints:

I4:I8 >=0

I4:I8 integer

B9:G9 >= B10:G10

It will give only a single solution.

 

[mikerickson]

So the problem is
Find combinations of ABCDE such that the sum of their characteristics (UI, DI, etc) meets or exceeds the desired characteristics list

And then choose the combination(s) that have minimum total price.

Is that the problem?

 

[mikerickson]

One more question.
Are the number or characteristics fixed?
I'm guessing that there might be more than 5 controller types(A,B,C,D,E) in actual situation, but will there be more than the 5 listed characteristics (UI, DI, etc.)?

 

[dclasen]

First question is yes.
The controller characteristics are fixed so there are only five types.
EDIT: Actually, the next things to figure out is the option to use UI as DI or all DO's, but I thought if I'm pushed a little to understand some of these solutions I will work out the rest.

For SHG - thanks for that - I'll review it.