Choosing Options out of array

[moshiq]

Hi guys,

I have a problem that I find hard to solve and I will thank you for you advise:

-I have X options and I need to pick 3 out of them for 1st, 2nd and 3rd place.
-Each option is valued as: rate*place probability.
-If I chose option A in the 1st place it's unavailable for the 2nd and 3rd place
-I need the 3 highest (in rate*probability) combinations of those options.

here is an example of 5 options (it can varies):

[TABLE="width: 400"]
<colgroup><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD][TABLE="width: 266"]
<colgroup><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Price[/TD]
[TD]1st Place[/TD]
[TD]2nd Place[/TD]
[TD]3rd Place[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]40%[/TD]
[TD="align: right"]20%[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]90%[/TD]
[TD="align: right"]50%[/TD]
[TD="align: right"]35%[/TD]
[/TR]
[TR]
[TD="align: right"]2.5[/TD]
[TD="align: right"]80%[/TD]
[TD="align: right"]50%[/TD]
[TD="align: right"]40%[/TD]
[/TR]
[TR]
[TD="align: right"]3.5[/TD]
[TD="align: right"]70%[/TD]
[TD="align: right"]60%[/TD]
[TD="align: right"]50%[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]60%[/TD]
[TD="align: right"]20%[/TD]
[TD="align: right"]40%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

the solution to this example is:
1) B,D,E
2) D,B,E
3) B,A,E

What do you recommend I should do?
I prefer formula upon VBA.

Thanks

 

[tusharm]

The Solver add-in is ideally suited for this kind of work. If you are not familiar with it, the time you spend learning about it will be well spent.

Hi guys,

I have a problem that I find hard to solve and I will thank you for you advise:

-I have X options and I need to pick 3 out of them for 1st, 2nd and 3rd place.
-Each option is valued as: rate*place probability.
-If I chose option A in the 1st place it's unavailable for the 2nd and 3rd place
-I need the 3 highest (in rate*probability) combinations of those options.

here is an example of 5 options (it can varies):

[TABLE="width: 400"]
<colgroup><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD][TABLE="width: 266"]
<colgroup><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Price[/TD]
[TD]1st Place[/TD]
[TD]2nd Place[/TD]
[TD]3rd Place[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]40%[/TD]
[TD="align: right"]20%[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]90%[/TD]
[TD="align: right"]50%[/TD]
[TD="align: right"]35%[/TD]
[/TR]
[TR]
[TD="align: right"]2.5[/TD]
[TD="align: right"]80%[/TD]
[TD="align: right"]50%[/TD]
[TD="align: right"]40%[/TD]
[/TR]
[TR]
[TD="align: right"]3.5[/TD]
[TD="align: right"]70%[/TD]
[TD="align: right"]60%[/TD]
[TD="align: right"]50%[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]60%[/TD]
[TD="align: right"]20%[/TD]
[TD="align: right"]40%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

the solution to this example is:
1) B,D,E
2) D,B,E
3) B,A,E

What do you recommend I should do?
I prefer formula upon VBA.

Thanks

 

[Joyner]

Hello Tushar,

I was originally thinking about the What-if Analysis in Excel but I was pretty sure, based on my understanding of these, that it would not work on this type of problem. I am not familiar with Solver, so I have added and will try to figure out a solution for this for my own education. I did watch one video, but the solver at first look does not seem to be very intuitive for applying here, but as soon as I can I'll give it a go...

Thanks

 

[tusharm]

Solver is *ideal* for this problem. In fact, set up the problem correctly and you can use the 'Simplex' method, which is guaranteed to yield what is known as a "global optimum," i.e., a solution that cannot be improved upon.

Suppose the data are in A1:D6, with row 1 containing headings.

We will let Solver change the values in F2:H6. A 1 in a cell means that the item in the row is in that place. So, if F2 is one then it means that A is in place 1. Similarly, if H6 is one then it means that E is in place 3.

Then, in I2 enter the formula =SUM(F2:H2) and copy it down to I3:I6.

In F7 enter the formula =SUM(F2:F6) and copy it to G7:H7.

In K2 enter the formula =SUMPRODUCT(F$2:F$6,$A$2:$A$6,B$2:B$6) and copy it to L2:M2.

In M3 enter the formula =SUM(K2:M2).

Now, in Solver, create a model that maximizes M3 by changing F2:H6 subject to F2:H6 being binary variables, I2:I6 <=1 and F7:H7=1.

Select the Simplex LP as the method.

Solver will give B, D, E as the optimal solution.

The solver model, when saved to the worksheet will look like:

=MAX($M$3)
=COUNT($F$2:$H$6)
=($F$2:$H$6=0)+($F$2:$H$6=1)=1
=$F$7:$H$7=1
=$I$2:$I$6<=1
={32767,32767,0.000001,0.01,TRUE,FALSE,TRUE,1,1,1,0.0001,TRUE}
={0,0,2,100,0,FALSE,TRUE,0.075,0,0,FALSE,30}

Hello Tushar,

I was originally thinking about the What-if Analysis in Excel but I was pretty sure, based on my understanding of these, that it would not work on this type of problem. I am not familiar with Solver, so I have added and will try to figure out a solution for this for my own education. I did watch one video, but the solver at first look does not seem to be very intuitive for applying here, but as soon as I can I'll give it a go...

Thanks

 

[moshiq]

Hi Tushar,

It worked like a charm. I was able to process the data and define the highest combination outcome.
Thank you for presenting the solver tool and its abilities.

Do you know how can I reach the highest three combinations, and not only the highest one?

 

[XOR LX]

Hi,

Try this array formula**:

=LARGE(MMULT(IF(MMULT(IFERROR(FIND({1,2,3,4,5},ROW($123:$543))^0,0),{1;1;1;1;1})=3,N(OFFSET($A$1,MID(ROW($123:$543),{1,2,3},1),{1,2,3})),0),{1;1;1}),ROWS($1:1))

Copy down to get the 2nd, 3rd, etc. highest total.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).</SPAN></SPAN>

 

[moshiq]

Hi,

Try this array formula**:

=LARGE(MMULT(IF(MMULT(IFERROR(FIND({1,2,3,4,5},ROW($123:$543))^0,0),{1;1;1;1;1})=3,N(OFFSET($A$1,MID(ROW($123:$543),{1,2,3},1),{1,2,3})),0),{1;1;1}),ROWS($1:1))

Copy down to get the 2nd, 3rd, etc. highest total.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Hi XOR LX,

Thank you for the answer.
I was not able to get any outcome from the formula you wrote.
Can you guide me with more details how to implement the formula in my sheet (where to place it) and what do do after I place it?

Thanks.

 

[XOR LX]

Hi,

Sorry. I should have clarified that it assumes, like tusharm, that your data are in A1:D6, with row 1 containing the headers, and also that I was basing the formula on the table that you gave in post #4

Place the formula in any cell you wish. Make sure you enter it as an array formula, as I described. Then copy down to get the 2nd, 3rd, etc. highest possible totals.

Regards

 

[moshiq]

Hi,

Sorry. I should have clarified that it assumes, like tusharm, that your data are in A1:D6, with row 1 containing the headers, and also that I was basing the formula on the table that you gave in post #4

Place the formula in any cell you wish. Make sure you enter it as an array formula, as I described. Then copy down to get the 2nd, 3rd, etc. highest possible totals.

Regards

Dear XOR LX,

It worked like a charm thank you for reply me back.
It's great result.

Do you know how can I see the names and order of the products for the top 3 results.
(In our example: 1.e,b,c ; 2. e,c,b 3.a,b,d)

Thanks again,

 

[XOR LX]

Sure.

Based still on the table you gave in post #4, and assuming you put your answers for the 1st, 2nd, 3rd largest totals in F2:F4, put this (again, array) formula in G2:

=INDEX($A$2:$A$6,MID(INDEX(ROW($123:$543),MATCH(TRUE,MMULT(IF(MMULT(IFERROR(FIND({1,2,3,4,5},ROW($123:$543))^0,0),{1;1;1;1;1})=3,N(OFFSET($A$1,MID(ROW($123:$543),{1,2,3},1),{1,2,3})),0),{1;1;1})=$F2,0)),COLUMNS($A:A),1))

Copy down as required and also to the right a further two columns.

Note: this may give incorrect results if any of your largest three totals totals are identical. Let me know if that's a possibility and I'll make a correction.

Regards

 

[moshiq]

Sure.

Based still on the table you gave in post #4, and assuming you put your answers for the 1st, 2nd, 3rd largest totals in F2:F4, put this (again, array) formula in G2:

=INDEX($A$2:$A$6,MID(INDEX(ROW($123:$543),MATCH(TRUE,MMULT(IF(MMULT(IFERROR(FIND({1,2,3,4,5},ROW($123:$543))^0,0),{1;1;1;1;1})=3,N(OFFSET($A$1,MID(ROW($123:$543),{1,2,3},1),{1,2,3})),0),{1;1;1})=$F2,0)),COLUMNS($A:A),1))

Copy down as required and also to the right a further two columns.

Note: this may give incorrect results if any of your largest three totals totals are identical. Let me know if that's a possibility and I'll make a correction.

Regards

Perfect!

Thanks a lot.