Search for maximum set of values
On Thu, 01 Jan 2009 21:45:37 GMT, Lars-Åke Aspelin
wrote:
On Thu, 1 Jan 2009 12:32:03 -0800, kaholynn
wrote:
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,TRAN SPOSE($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
|