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

 

[XOR LX]

You're welcome!

Remember to get back to me re the point about identical totals if it becomes an issue.

Regards

 

[moshiq]

You're welcome!

Remember to get back to me re the point about identical totals if it becomes an issue.

Regards

You were right.

If the 1st and the 2nd values are equals, it shows the first string twice.


Another question (more important):

Can you teach me how to tweak the formula?
1. for have more than 5 products (let's say 7)
2. placing the table not from the first cell (A1)

Thanks again for your help.

 

[XOR LX]

Hi,

A1 just needs to be replaced with whatever is the top-left (unused) cell in your table.

The formula for seven products would be (now assuming your table is in A1:D8):

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

You can compare this to the version for 5 products and hopefully work out what's happening.

Based on that formula for seven products, this new formula in G2 should eliminate the issue of duplicate totals:

=INDEX($A$2:$A$8,MID(INDEX(ROW($123:$765),SMALL(IF(MMULT(IF(MMULT(IFERROR(FIND({1,2,3,4,5,6,7},ROW($123:$765))^0,0),{1;1;1;1;1;1;1})=3,N(OFFSET($A$1,MID(ROW($123:$765),{1,2,3},1),{1,2,3})),0),{1;1;1})=$F2,ROW($123:$765)-MIN(ROW($123:$765))+1),COUNTIF($F$2:$F2,$F2))),COLUMNS($A:A),1))

Hope that helps.

Regards