Need help with MINVERSE and INDIRECT

[ATLGator]

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 figure out how to use 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})")), but this just gives me a #REF! error.

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&"}"))

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

Thanks!

 

[ATLGator]

I have also tried:
=MINVERSE(CONCATENATE("{",A1,",",B1,";",A9,",",B9,"}"))

This gives me a #VALUE! error.

 

[mikerickson]

It would be easiest if you put formulas in some remote cells (like AA1:AB2) with the appropriate formulas and then used =MININVERSE(AA1:AB2)

Another approach, that uses names, would be to define a name like

Name: MatrixString RefersTo: ="{" & A1 & "," & B1 & ";" & A9 & "," & B9 & "}"
Name: Matrix RefersTo: =EVALUATE(MatrixString)

and then use the formula =MININVERS(Matrix)

 

[ATLGator]

Well, although puting them in a remote location would work, I am trying to make this work multiple times on an entire page of data.

My true end formula will look like this:
=TRANSPOSE(MMULT(MINVERSE([solution]),TARGET))
where TARGET is a named range.

This formula will get me the quantites of two stocks needed, listed horizontally, and then this will be dragged down 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.

 

[mikerickson]

In that case, the Names solution would be the way to go. Since Names react to absolute/relative addressing.

 

[ATLGator]

I can't name the ranges because there will be over 12,000 instances of the formula, each one using one absolute reference and one unique reference (which means I would have to name over 12,000 ranges).

 

[ATLGator]

I got my answer.

In case anyone is interested:
=MINVERSE(SUMIF(INDIRECT({"A1","B1";"A9","B9"}),"<>0"))

 

[mikerickson]

No you wouldn't. Names work just like formulas in regard to addressing. Here's an example.

In a new worksheet, Select cell Sheet1!C1 and define the name

Name:testName RefersTo: =Sheet1!$A$1+Sheet1!B1

Put some test (numerical) data in columns A and B and put the formula =testName in some cells of column C. Drag or copy the formula around the sheet and see that the second address is relative while the A1 part stays absolute.

(Mixed forms, like Sheet1!$A1 are also a possibility)