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

 

[C Moore]

I can understand nothing in the way this is written. Where are the options? What does rate have to do with your table? What does your solution choose?

 

[Joyner]

Hello Moshiq and welcome to the board.

I have to agree wtih C Moore, it is difficult to dechiper what you want here. From what you do have, it looks like something we can help you with, but you need to try a better explaination of what you need. You mention a couple thinks that are not in your table. If you can, please give another attempt at your problem and include all of the variables we need to consider.

Thank you

 

[moshiq]

Sorry guys for not being clear enough. I will try again from the beginning.

I have x amount of products. x is a variable between 1-10.
I can choose to present only 3 products (out of x) in a single offer.
I profit from each product, based on the place in the offer.

Here is an example for table of profit (based on x=5):

[TABLE="width: 304"]
<tbody>[TR]
[TD] [TABLE="width: 391"]
<tbody>[TR]
[TD][/TD]
[TD]1st Place[/TD]
[TD]2nd Place[/TD]
[TD]3rd Place[/TD]
[/TR]
[TR]
[TD]Product A[/TD]
[TD]3[/TD]
[TD]1.2[/TD]
[TD]0.6[/TD]
[/TR]
[TR]
[TD]Product B[/TD]
[TD]3.6[/TD]
[TD]2[/TD]
[TD]1.4[/TD]
[/TR]
[TR]
[TD]Product C[/TD]
[TD]2[/TD]
[TD]1.25[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Product D[/TD]
[TD]2.45[/TD]
[TD]2.1[/TD]
[TD]1.75[/TD]
[/TR]
[TR]
[TD]Product E[/TD]
[TD]3.5[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

if I choose A,B and C:
the profit will be 3+2+1 = 6
changing the order of the offer will effect the profit:
B,A,C is 3.6+1.2+1 = 5.8
In this example the number of different offers are 60 (5*4*3).


Now, the solution I seek is optimization for the offers' profit.
Out of possible 60 offers I want to know what are the top 3 offers with the highest profit.

thank you for your help.

 

[C Moore]

Much better! Let me think.

 

[Joyner]

Moshiq,

That does pose a bit of a challenge. Below see my solution for calculating the number and the list of the three products. Formulas are for data to be postioned at top left of spreadheet.

I am sure someone else could come up with a much better array formula or statisical formula, or a more creative/better way especially with code/VBA. My formula DOES NOT handle if there is a tie for any of the amounts in any column. It will select the first value that is the highest where the product has not already been used. I am not sure of your data or if there are other iterations where a lower value could be selelcted in column A or B to select different values in column B or C, if that makes sense. For that, I would think this should be handled by code. However, if the data you show is typical, I think the below method may work, except for handling a tie in the columns, which could certainly throw all of my formula solution off.

For the number calculation:

=MAX(B2:B6)+AGGREGATE(14,6,(B2:B6<>MAX(B2:B6))*C2:C6,1)+AGGREGATE(14,6,(B2:B6<>MAX(B2:B6))*(C2:C6<>AGGREGATE(14,6,(B2:B6<>MAX(B2:B6))*C2:C6,1))*D2:D6,1)

For the Product Names:

=CONCATENATE(INDEX(A2:A6,MATCH(MAX(B2:B6),B2:B6,0)),",",INDEX(A2:A6,MATCH(AGGREGATE(14,6,(B2:B6<>MAX(B2:B6))*C2:C6,1),C2:C6,0)),",",INDEX(A2:A6,MATCH(AGGREGATE(14,6,(B2:B6<>MAX(B2:B6))*(C2:C6<>AGGREGATE(14,6,(B2:B6<>MAX(B2:B6))*C2:C6,1))*D2:D6,1),D2:D6,0)))

 

[Joyner]

Okay, another attempt, using the first row values in order from highest to lowest to get the outcome. This is closer but still not perfect, I would think this would net the highest combination to pick the top three. Put this formula anywhere and copy down same number of rows in your table:

For the number calculation:

=LARGE($B$2:$B$6,ROW(A1))+AGGREGATE(14,6,($B$2:$B$6<>LARGE($B$2:$B$6,ROW(A1)))*$C$2:$C$6,1)+AGGREGATE(14,6,($B$2:$B$6<>LARGE($B$2:$B$6,ROW(A1)))*($C$2:$C$6<>AGGREGATE(14,6,($B$2:$B$6<>LARGE($B$2:$B$6,ROW(A1)))*$C$2:$C$6,1))*$D$2:$D$6,1)

For the Product Names:

=CONCATENATE(INDEX($A$2:$A$6,MATCH(LARGE($B$2:$B$6,ROW(A1)),$B$2:$B$6,0)),",",INDEX($A$2:$A$6,MATCH(AGGREGATE(14,6,($B$2:$B$6<>LARGE($B$2:$B$6,ROW(A1)))*$C$2:$C$6,1),$C$2:$C$6,0)),",",INDEX($A$2:$A$6,MATCH(AGGREGATE(14,6,($B$2:$B$6<>LARGE($B$2:$B$6,ROW(A1)))*($C$2:$C$6<>AGGREGATE(14,6,($B$2:$B$6<>LARGE($B$2:$B$6,ROW(A1)))*$C$2:$C$6,1))*$D$2:$D$6,1),$D$2:$D$6,0)))

Again copied down alongside the table anywhere

Please try and let me know. I still need to look at, and know an array formula will be the answer (although cose still the best option probably)

 

[xenou]

For a small number of permutations (and even larger ones, as long as we are not in the millions) it seems easiest to me to calculate them all then take the largest. The check result on the formulas should be: E, B, D with a total of 7.25.

<a href="http://northernocean.net/etc/mrexcel/20140401_Book1.zip">SAMPLE WORKBOOK</a>
sha256 checksum (zip file): 3017976cf778f31a6c6604059ce1f50eb6c3a48775a051cb45099e1c15774610

 

[moshiq]

@joyner,
Thank you very much for your time and efforts.
I appreciate your formula.

but as @xenou said, the right answer is e,b,d which results 7.25

 

[moshiq]

For a small number of permutations (and even larger ones, as long as we are not in the millions) it seems easiest to me to calculate them all then take the largest. The check result on the formulas should be: E, B, D with a total of 7.25.

SAMPLE WORKBOOK
sha256 checksum (zip file): 3017976cf778f31a6c6604059ce1f50eb6c3a48775a051cb45099e1c15774610

Hi @xenou,

Thank you for your answer, I thought about it in the beginning, but I wanted to find a solution without writing all the possibilities.
one cell solution.
In any case, I'll take your answer and use it for my table.

thanks.