Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
nXn matrix creation
Hi All,
I have a need to create a matrix based on some user choices. The requirement is described below: User Inputs 5 options Opt1, Opt2, Opt3, Opt4, Opt5. Then runs the macro to generate the matrix. The matrix should look like below Opt1 Opt2 Opt3 Opt4 Opt5 Opt1 1 Opt2 choice21 1 Opt3 choice31 choice32 1 Opt4 choice41 choice42 choice43 1 Opt5 choice51 choice52 choice53 choice54 1 The choices is the drop down menu created using Data Validation List. In this matrix user will make certain choices. The cells above the diagonal need to have a formula, which is the reciprocal of the choice for a corresponding combination below the diagonal, that is, if intersection of RowValue = Opt2 and ColumnValue = Opt1 is choice21 then intersection of RowValue = Opt1 and ColumnValue = Opt2 will be a formula = 1/choice21. The number of options a user can provide could be anything it could be as small as 2 and as large as 20-30 options. So the matrix need to be sized accordingly. I am totally clueless on this. Any help on this will me much appreciated. Thanks in advance. Regards, AG |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
nXn matrix creation
AG formulated the question :
Hi All, I have a need to create a matrix based on some user choices. The requirement is described below: User Inputs 5 options Opt1, Opt2, Opt3, Opt4, Opt5. Then runs the macro to generate the matrix. The matrix should look like below Opt1 Opt2 Opt3 Opt4 Opt5 Opt1 1 Opt2 choice21 1 Opt3 choice31 choice32 1 Opt4 choice41 choice42 choice43 1 Opt5 choice51 choice52 choice53 choice54 1 The choices is the drop down menu created using Data Validation List. In this matrix user will make certain choices. The cells above the diagonal need to have a formula, which is the reciprocal of the choice for a corresponding combination below the diagonal, that is, if intersection of RowValue = Opt2 and ColumnValue = Opt1 is choice21 then intersection of RowValue = Opt1 and ColumnValue = Opt2 will be a formula = 1/choice21. The number of options a user can provide could be anything it could be as small as 2 and as large as 20-30 options. So the matrix need to be sized accordingly. I am totally clueless on this. Any help on this will me much appreciated. Thanks in advance. Regards, AG The example you gave for the construction of the formula is easy enough to do since it only involves 1 choice. How do you want to construct the formula for the remaining rows below Rows(Opt2)? I can assume it would include all choices in the respective row, but I'd rather you explicitly state exactly what results you want. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
nXn matrix creation
Hi Garry,
The choices given to the users below the diagonal is a drop down with 5 options - numbers 1, 2, 3, 4, 5. And the users will choose one of the those choices. So, once they have made the choice, say for Row (Opt3) Col (Opt2) = choice32 (lets assume the user chooses 5) then the formula I need in the cell at the intersection of Row (Opt2) Col (Opt3) is 1/choice32 (or 1/5). I am not sure if that was sufficient info. I could create something in a spreadsheet and send it over if that helps. Regards, AG |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
nXn matrix creation
AG expressed precisely :
Hi Garry, The choices given to the users below the diagonal is a drop down with 5 options - numbers 1, 2, 3, 4, 5. And the users will choose one of the those choices. So, once they have made the choice, say for Row (Opt3) Col (Opt2) = choice32 (lets assume the user chooses 5) then the formula I need in the cell at the intersection of Row (Opt2) Col (Opt3) is 1/choice32 (or 1/5). I am not sure if that was sufficient info. I could create something in a spreadsheet and send it over if that helps. Regards, AG Hi AG, Your reply covers what you want if the user picks choice32 in Col(Opt2), but my Q was what do you want when they also pick choice31 in Col(Opt1)? The issue lies in that the cells below the diagonal are filled, whereas the cells above are blank except where you want the formula. Also, what purpose does the cells with '1' in them serve in this? Perhaps you should attach a spreadsheet to your reply, that shows examples of how this matrix needs to be constructed and demonstrate where/what goes in the formula cell. This sounds like a spreadsheet design issue more than a VBA solution issue. Are you open to either/both? -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
nXn matrix creation
On 3 Jun., 15:32, AG wrote:
Hi All, I have a need to create a matrix based on some user choices. The requirement is described below: User Inputs 5 options Opt1, Opt2, Opt3, Opt4, Opt5. Then runs the macro to generate the matrix. The matrix should look like below * * * * * * * * * * Opt1 * * * * *Opt2 * * * * *Opt3 Opt4 * * * * *Opt5 Opt1 * * * * * * * 1 Opt2 * * * * * choice21 * * * * 1 Opt3 * * * * * choice31 * * *choice32 * * * * 1 Opt4 * * * * * choice41 * * *choice42 * *choice43 * * * *1 Opt5 * * * * * choice51 * * *choice52 * *choice53 * * choice54 1 The choices is the drop down menu created using Data Validation List. In this matrix user will make certain choices. The cells above the diagonal need to have a formula, which is the reciprocal of the choice for a corresponding combination below the diagonal, that is, if intersection of RowValue = Opt2 and ColumnValue = Opt1 is choice21 then intersection of RowValue = Opt1 and ColumnValue = Opt2 will be a formula = 1/choice21. The number of options a user can provide could be anything it could be as small as 2 and as large as 20-30 options. So the matrix need to be sized accordingly. I am totally clueless on this. Any help on this will me much appreciated. Thanks in advance. Regards, AG Hello, Normally I would like to do this with a macro. If you need to use worksheet functions only I would search to the left for the diagonal "1", then downwards for the other diagonal "1" and according to their position look up the corresponding element. This search needs to be a bit tricky because you do not want to find a "1" which is part the of data, not the diagonal. Hmm, actually I think I would prefer an unused character in the diagonal which cannot be part of the data (the tricky bit would otherwise need something like a name HasFormula with Excel4 macro commands, I guess). Regards, Bernd |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
nXn matrix creation
Hi Gary,
Firstly, thanks for spending time on this and replying to me. I think giving a sample spreadsheet is the best option but I am not sure how to attach a file here on these posts. Can I send it directly to you? Please let me know. Thanks again. Regards, AG |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
nXn matrix creation
Hi Bernd,
I am not sure if I understood your question. Regards, AG |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
nXn matrix creation
AG wrote on 6/4/2010 :
Hi Gary, Firstly, thanks for spending time on this and replying to me. I think giving a sample spreadsheet is the best option but I am not sure how to attach a file here on these posts. Can I send it directly to you? Please let me know. Thanks again. Regards, AG You need to be using a newsreader (like Outlook Express or something) to be able to send attachments. You can send a sample to me if: You use the same title as this thread The sample contains the expected results (done manually) My mailbox is: gesansom at netscape dot net -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
form creation | Excel Discussion (Misc queries) | |||
CALENDAR CREATION | Excel Discussion (Misc queries) | |||
How can I transpose nXm matrix to mXn Matrix | Excel Worksheet Functions | |||
Matrix Creation | Excel Programming | |||
Matrix Multiplication Function creation | Excel Programming |