×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Dynamically update cell range for MINVERSE( )

Dynamically update cell range for MINVERSE( )

Dynamically update cell range for MINVERSE( )

(OP)
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.  

RE: Dynamically update cell range for MINVERSE( )

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!

RE: Dynamically update cell range for MINVERSE( )

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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Resources

Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Design for Additive Manufacturing (DfAM)
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a part’s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close