Need help with MINVERSE and INDIRECT

I am trying to use MINVERSE (and then MMULT) to solve a system of equations, but I am having difficulty because the array I need to use in the MINVERSE function are cells that are not next to each other, for example:

Instead of having:
A1 = 5; B1 = 8
A2 = 3; B2 = 7
and MINVERSE(A1:B2)

I need:
A1 = 5; B1 = 8

A9 = 3; B9 = 7
and something like MINVERSE(A1:B1,A9:B9)

However, this does not seem to work, but if I were to enter MINVERSE({5,8;3,7}), it does work.

So now, I am trying to replicate this by using the INDIRECT formula to pull in the cells from the different locations but to calculate the values inside the MINVERSE formula. I have tried MINVERSE(INDIRECT("{5,8;3,7}")) and MINVERSE(INDIRECT("{"&A1&","&B1&";"&A9&","&B9&"}")), but this just gives me a #REF! error.

I have also tried MINVERSE(CONCATENATE("{",A1,",",B1,";",A9,",",B9,"}")) and MINVERSE(CONCATENATE("{",5,",",8,";",3,",",7,"}")), but this gives me a #VALUE! error. This error is coming from the fact that result from the formula inside MINVERSE is surrounded by quotation marks (" ").

My ultimate goal is to pull the formulas that are being used to calculate the numbers above into the INDIRECT formula, so that I have something like:
MINVERSE(INDIRECT("{"&formula&","&formula&";"&formula&","&formula&"}"))

and then add this into a MMULT formula to get:
=TRANSPOSE(MMULT(MINVERSE(INDIRECT([solution])),Target))
where Target is a named range.

This formula will get me the quantites of two stocks needed, listed horizontally (because of TRANSPOSE), and then this will be copied and pasted over a list of dates.

Also, I am not very good with VBA yet, so that is not really an option for me right now.


Any thoughts on how I can get these numbers (or formulas) into the INDIRECT formula and get MINVERSE to work?


Thanks!

Haseeb,

That is closer to what I'm looking for - now the cell references will change as I drag it down. However, I need to figure out how to replace the cell references with formulas (the formulas that are currently in the cells the formula will be referencing) and still get the same result.

Thanks.

Here is a sample file with what I am trying to do.

Notice that the Beta number are held constant within each column and the price for the day being pulled from another tab (which are the INDEX/MATCH formulas)

The complete formula is added into the first column, but without the "=" because it doesn't actually calculate anything the way it is written now.

To give you an idea of what the solution should look like, it would be:
MINVERSE(Beta1:Beta2;Price1:Price2)
where the Betas are retrieved from the top of the column and the Prices are retrieved using INDEX/MATCH, referencing another tab

When its complete, you should be able to drag the cells (two at a time since it's an array) and the cell references will change to pull in the prices for each date down column A. I have added the prices for the days and stocks listed in the Data tab

Please let me know if this is unclear.

Thanks again!

Haseeb,

That did the trick. However, now I have a different problem. For =TRANSPOSE(MMULT(MINVERSE(CHOOSE({1;2},B$7:C$7 , either B7 or C7 needs to be changed to a negative, based a condition. The reference that is made to be a negative has the potential to change in each individual cell (no commonalities in rows or columns). Is there a way to add this to the formula?

And btw, using a range of cells in MATCH was brilliant. I'ver never seen that done before.

Thanks!