+ Reply to Thread
Results 1 to 2 of 2

Need to create a dynamic array of cell references for MINVERSE

  1. #1
    Forum Contributor
    Join Date
    08-29-2011
    Location
    Atlanta
    MS-Off Ver
    Excel 2007
    Posts
    171

    Need to create a dynamic array of cell references for MINVERSE

    For some background, here is a thread that I posted on another issue related to this one: http://www.excelforum.com/excel-gene...-indirect.html

    I am trying to solve a system of equations using the MMULT & MINVERSE and then copying and pasting the formula to thousands of cells. Currently, I am 90% of the way to my solution, but I can't figure out one final part.

    In using the MINVERSE function, I need to reference two ranges of data to be multipled together. Here is the formula I have right now:
    TRANSPOSE(MMULT(MINVERSE(CHOOSE({1;2},B$7:C$7,VLOOKUP($A8,Data2012,MATCH(B$6:C$6,'2012 Stock Data'!$A$1:$AE$1, 0),0))),Target))

    The issue in creating this formula was that, normally, an MINVERSE function uses four cells right next to each other. However, in this situation, one set of cells was to remain constant within a row and only change as it was dragged along the columns while the other set was going to change in every cell. The formula above achieves this goal, however, there is another element I need to add.

    Based on a condition, either B7 or C7 needs to be made negative (in each case, one or the other will always need to be negative based on the answer in another corresponding cell). I do not know how to add this into the array. I have not had any luck using the INDRECT formula to create the same notation and I have also tried to the replicate the VLOOKUP & MATCH formulas that you see in the second part of the equation as HLOOKUP & MATCH (however, this didn't work since I was looking for two look-up values and their corresponding answers instead of one look-up value in two columns, which is what is happening in the equation already).

    Any thoughts?

  2. #2
    Forum Contributor
    Join Date
    08-29-2011
    Location
    Atlanta
    MS-Off Ver
    Excel 2007
    Posts
    171

    Re: Need to create a dynamic array of cell references for MINVERSE

    Also, within the formula, "Target" is a named range.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1