Search for maximum set of values

[kaholynn]

Dear All,

I have a weight matrix and a function matrix. The function matrix value are
to be chosen from a set of archived data, in such a way that for a given row
of weights the multiplication of a row of weights and a row functions produce
maximum value (let us name it as 'Sum'). I have tried to explain with a small
example.


Example of weight matrix;

W1 W2 W3
1 0 0
0 1 0
0 0 1


Example of Function Matrix (Initial Values)

F1 F2 F3 Sum(=W1*F1 + W2*F2 + W3*F3)
0 0 0 0
0 0 0 0
0 0 0 0


Function Matrix to be chosen from a set of archived data
Set F1 F2 F3
1 5 9 12
2 7 12 3
3 10 5 2
4 6 10 5
5 4 8 13

(Note: Set is just a reference column)


Final Selection of Function Matrix ( to be chosen from the above archived
data)

Set F1 F2 F3 Sum
3 10 5 2 10
2 7 12 3 12
5 4 8 13 13

The problem I am facing is how to select a particular row of functions (F1,
F2, and F3) from achieved data of function matrix such that the
multiplication of a row of weights and corresponding function (one row)
produce the maximum 'Sum'.

I tried to work with inbuilt functions such as INDEX, LOOKUP, CHOOSE but
could not figure out how the search of maximum 'Sum' will occur. I tried to
explain the problem, my apologies if I am not clear. I will appreciate any
suggestions. Many thanks.

 

[Lars-Åke Aspelin]

Dear All,

I have a weight matrix and a function matrix. The function matrix value are
to be chosen from a set of archived data, in such a way that for a given row
of weights the multiplication of a row of weights and a row functions produce
maximum value (let us name it as 'Sum'). I have tried to explain with a small
example.


Example of weight matrix;

W1 W2 W3
1 0 0
0 1 0
0 0 1


Example of Function Matrix (Initial Values)

F1 F2 F3 Sum(=W1*F1 + W2*F2 + W3*F3)
0 0 0 0
0 0 0 0
0 0 0 0


Function Matrix to be chosen from a set of archived data
Set F1 F2 F3
1 5 9 12
2 7 12 3
3 10 5 2
4 6 10 5
5 4 8 13

(Note: Set is just a reference column)


Final Selection of Function Matrix ( to be chosen from the above archived
data)

Set F1 F2 F3 Sum
3 10 5 2 10
2 7 12 3 12
5 4 8 13 13

The problem I am facing is how to select a particular row of functions (F1,
F2, and F3) from achieved data of function matrix such that the
multiplication of a row of weights and corresponding function (one row)
produce the maximum 'Sum'.

I tried to work with inbuilt functions such as INDEX, LOOKUP, CHOOSE but
could not figure out how the search of maximum 'Sum' will occur. I tried to
explain the problem, my apologies if I am not clear. I will appreciate any
suggestions. Many thanks.

Put your W matrix in cells D1:F3
Put your F matrix in cells A1:C5 (5 can be changed to fit your data)

Put the following formula where you want your output table to be
located, e.g. in cell K10

=MATCH(MAX(MMULT(MMULT($A$1:$C$5,TRANSPOSE($D$1:$F$3)),--(ROW($1:$3)=ROW(1:1)))),MMULT(MMULT($A$1:$C$5,TRANSPOSE($D$1:$F$3)),--(ROW($1:$3)=ROW(1:1))),0)

Note: This is an array formula that should be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER.

Put the following in cell L10:

=INDEX(A$1:A$5,$K10)

Copy cell L10 thru M10 and N10

Put the following in cell O10:

=SUM(L10:N10)

Copy cells K10:O10 down to cover K10:O12

If you have more than 5 rows of archived data, change the 5 in all
formulas above to cover all your data.

Hope this helps / Lars-Åke

 

[Lars-Åke Aspelin]

Put your W matrix in cells D1:F3
Put your F matrix in cells A1:C5 (5 can be changed to fit your data)

Put the following formula where you want your output table to be
located, e.g. in cell K10

=MATCH(MAX(MMULT(MMULT($A$1:$C$5,TRANSPOSE($D$1:$F$3)),--(ROW($1:$3)=ROW(1:1)))),MMULT(MMULT($A$1:$C$5,TRANSPOSE($D$1:$F$3)),--(ROW($1:$3)=ROW(1:1))),0)

Note: This is an array formula that should be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER.

Put the following in cell L10:

=INDEX(A$1:A$5,$K10)

Copy cell L10 thru M10 and N10

Put the following in cell O10:

=SUM(L10:N10)

Copy cells K10:O10 down to cover K10:O12

If you have more than 5 rows of archived data, change the 5 in all
formulas above to cover all your data.

Hope this helps / Lars-Åke

The formula in O10 was wrong, here is a better one:

=MMULT(L10:N10,TRANSPOSE(D1:F1))

Note: This is an array formula that should be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER

/ Lars-Åke

 

[Lars-Åke Aspelin]

The formula in O10 was wrong, here is a better one:

=MMULT(L10:N10,TRANSPOSE(D1:F1))

Note: This is an array formula that should be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER

/ Lars-Åke

Or even simpler (without having to bother with CTRL+SHIFT+ENTER):

=SUMPRODUCT(L10:N10,D1:F1)

/ Lars-Åke

 

[kaholynn]

Lars-Ã…ke,

Great! Thank you so much. It works for the example as well as for a large
set of data.

Many thanks.

 

[kaholynn]

You are great. I did try the function SUMPRODUCT formula in O10 when I first
saw only SUM function. But it was very minor. You were spot on in getting the
problem (question) and your response was wonderful.

Million thanks.