Dynamically update cell range for MINVERSE( )

[a3a]

I needs some help on this. It may or may not be possible, but so far I can't find anyone who understands question let alone answer it.

Lets say you want to use MINVERSE(A1:C3). Traditionally you would *manually* type (or select) the cell range when you write the formula. With me?

Take this senario. In another cell I have "generated" a cell range by using the CONCATENATE( ) for several cells that contain both letters and numbers. In other words, I have used CONCATENATE(selected cells) to "build" the cell range that I would like to put in the MINVERSE( ). Example: A10=A, B10=1, A11=C, B11=3. D10=CONCATENATE(A10,B10,":",A11,B11), so now D10 *displays* the desired range of A1:C3. D10 is not constant because other specifications by the user cause A10, B10, A11, and B11 to change.

The two problems I'm having are below.

1. How do I get this generated cell range into the MINVERSE function and WORK. I would like to basically do this: =MINVERSE(D10), but MINVERSE will only recognize D10 as the range and not A1:C3.


2. Lets say that I did get MINVERSE recognize that D10 is the cell range A1:C3. Now, if D10 changes there is another problem because you have to use CONTROL+SHIFT+ENTER to initiate the MINVERSE() (something to do with and array). I just want to have MINVERSE( ) use the cell range in D10 and not have to stop to select somthing or do a CONTROL+SHIFT+ENTER.

My objective is to to make a template that will solve any size matrix the user specifies. So the process will be: specify the number of row and columns; enter coefficients and constants in the matrix; and have the solution vector displayed either automatically after the last matrix entry was entered OR use a macro to make the approprate adjustments to the MINVERSE( equation). Still with me? I hope so!

Please don't suggest, "use mathcad or another math program," I would like to do it in Excel because the idea can be applied to other projects i've needed to do it on.


Thanks to anyone that can get me started.

Note: Excel 97. I can do basic macro recording, but can't write VB code.

 

[dsi]

Why don't you assign a name to the matrix?

Highlight the matrix (A1:C3)
Insert > Name > Define
Call it MyMatrix or something of the sort
Then use =Minverse(MyMatrix)

Then, if you change the size of the matrix:
Insert > Name > Define
Select MyMatrix from the list
Change the (Refers to) value to reflect the new matrix
If you hit the little box to the right of the
Refers to box, you can select the new range with
the mouse.

Hope this helps!

 

[yakpol]

a3a,
It's a late respond to your question and you, probably, have found all the answers. But in the case if you have not and for public interest I'll write what I know about it.

Problem #1 is simple. Use function INDIRECT(string) to convert a string into a range. So your formula will be =MINVERSE(INDIRECT(D10)).

Problem #2. dsi answered it. You have to have in the worksheet only initial matrix, all the derivitive, like MINVERSE() can be stored as a formula and never shown on the screen. For example, define names under Insert/Name/Define

InvertMatrix = MINVERSE(INDIRECT(D10))
LoadVector = INDIRECT(E10)
FinalVector = MMULT(InvertMatrix,LoadVector)

Then highlight one column range, type =FinalVector and press ctrl-shift-enter!