On Thu, 01 Jan 2009 23:21:53 GMT, Lars-Åke Aspelin
wrote:

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

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

=SUMPRODUCT(L10:N10,D1:F1)

/ Lars-Åke