Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search for maximum set of values
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search for maximum set of values
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search for maximum set of values
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search for maximum set of values
Lars-Ã…ke,
Great! Thank you so much. It works for the example as well as for a large set of data. Many thanks. "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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search for maximum set of values
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. "Lars-Ã…ke Aspelin" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Graphing the maximum and minimum values | Charts and Charting in Excel | |||
Maximum Absolute values | Excel Worksheet Functions | |||
Formula with minimum & maximum values | Excel Worksheet Functions | |||
Maximum & Minimum values in a coulmn | Excel Discussion (Misc queries) | |||
Finding Maximum value while excluding some values | Excel Worksheet Functions |