I’m using the built-in matrix inverse function from Excel to solve a system of three equations and I’m getting some outrageous values, +/- 10^12 or so, when they should be about between +1 and 2. Here’s a screenshot of what I’m attempting to do: http://www.wireframewebdesign.com/ss.jpg
My code has worked before, and is the following:
“=INDEX(MMULT(MINVERSE(D83:F85),G83:G85),1,1)” (for the x value)
Where D83:F85 is the 3x3 matrix containing the x, y, and z coefficients and G83:G85 is the 3x1 matrix containing the constants.
I should also note that the numbers shown in the screenshot are rounded to four significant figures, but I’m pretty sure Excel uses many more decimal places when doing its calculations.
Perhaps there could be something inherently awry with the coefficients, but isn’t it counter-intuitive to think that it should have such an enormous difference?
Anything I can do to remedy the situation?
I can’t pretend to understand the Matrix-inverse function or what it does, but I noticed you appeared to have valid numbers in the cells above. Can’t you just fill the formula down?
Here is my online calculator for solving three unknowns:
http://www.1728.com/unknwn3.htm
It uses no matrix functions or anything similar.
It employ’s Cramer’s Rule for solving systems of equations.
Basically, you will need to evaluate third order determinants.
Here is one site:
http://www.okc.cc.ok.us/maustin/Cramers_Rule/Cramer’s%20Rule.htm
Scroll down half-way down that page for solving for 3 unknowns.
It probably would be easier if you were able to use an Excel built-in function. But by doing this by the brute force method (Cramer’s Rule), at least you can see precisely what is occurring.
Are you remembering Ctrl-Shift-Enter (IIRC, for Excel)? If so, try executing one function at a time. First do the MINVERSE and see how that looks. Then, perform the MMULT operation and see what you end up with there. Then try the INDEX thing (the only function that I don’t know what it does).
Your system of equations is noninvertible, or at least very nearly so. It’s as if you’re trying to solve the pair of equations
x+y=1
x+y=2
When you try to calculate a numerical inverse for such a matrix, you end up dividing by a very small number (hence the large results). Depending on the coefficients (in column G) you may be able to find a partial solution, or the equations may be inconsistent (as in the example above).
You can test for invertibility by computing the determinant (MDETERM in Excel); if it’s zero, or very small, MINVERSE will probably return inaccurate results.
Okay, I’ll try that URL once again:
Cramer’s Rule
Scroll down to the middle of the page for a 3 unknown example.
Also, have you tried using the MDETERM function in Excel? Basically, it evaluates determinants. (Just a thought).
Omphaloskeptic
You recommended the “MDETERM in Excel”. Hmmm great minds think alike … but some type faster than others LOL.
Also, have you tried using MDETERM? 
Omphaloskeptic
I tried using that function but then I got some message saying that MDETERM wasn’t insatalled , etc. I really don’t want to bother with that because I don’t need it. I wrote this calculator: www.1728.com/unknwn3.htm
At the moment I am writing an Excel spreadsheet just using the “brute force method” (Cramer’s Rule). At this point I have it solving for the so-called “Delta Determinant” correctly. If gregongie wants me too, I can write another set of instructions, whereby the ‘X’ term will be solved. That’s over half the battle so to speak.
Hey Gregongie, you want a complete spreadsheet for solutions for X, Y and Z? I’d be happy to help out a fellow “Doper”. Hell, I’ve had plenty of help from others. (Quid pro quo Clarice - as Hannibal Lecter says).
It doesn’t matter* whether you use Cramer’s Rule, MINVERSE, or do it by hand. The problem is that the particular numbers he’s using give a noninvertible set of equations. (Try changing one of the nine numbers in D83:F85; the solutions should change to a more reasonable size.)
(*) It might matter a little because of different numerical stability of the different inversion methods, but none of the methods will give “reasonable” results because the underlying equations don’t define a unique solution.
Okay, what you are saying is that the equations are inconsistent? (At least I think that is what the term is).
Well, the equations do seem solvable. Entering those numbers into my online calculator, I get
X= -1.7709690474923343
Y= 9.51850714247701
Z= -0.002443839836717413
Entering those numbers into my Excel™ Spreadsheet I get
the same exact answers for X & Y (I haven’t written the ‘Z’ routine yet).
So, to me, unless “Cramer” gave me some bad advice, the equations are consistent.
When the coefficient matrix for a system of equations is noninvertible, it means that the system of equations is either inconsistent (having no solutions) or indeterminate (having an infinite number of solutions). Which it is depends on the vector of constants; for example,
x+y=1
x+y=2
is inconsistent, while
x+y=1
x+y=1
is indeterminate.
In this case the system of equations appears to be indeterminate (at least, to four digits of precision), so there are an infinite number of solutions.
Another way to see this is to notice that the second equation of the system (row 2) appears to be the average of the first and third equations. (In fact, the spreadsheet seems to be set up this way, with the coefficients all changing piecewise-linearly from row to row; so this will be the case for all of the 3x3 systems following the pattern of rows 83 onward.)
Okay, I actually averaged the first and third rows of the equations (rows 83, 84 amd 85) and came up with these values:
0.72665 0.24665 0.02665 1.0608
Whereas the spreadsheet states the second row values as:
0.7267 0.2467 0.0267 1.0606
Granted these are slight differences but enough to make the equations solvable.
Granted the D. E and F columns appear to consist of averages of the row above and the row below. However, this relationship breaks down at rows 83, 84 and 85.
As a matter of fact I did input sets of numbers for 3 consecutive rows (e.g. 67, 68 and 69) and these were NOT solvable.
Even as far down as rows 80, 81 and 82 no roots could be found.
But there are roots for the figures in rows 83, 84 and 85.
It seems (as you mentioned) that at least 5 significant figures of calculating precision is required.
And what happened to gregongie anyway?
Assuming the matrices are the numbers outlined in ink in your cite my Mathcad gives the answers as the column matrix:
6.054
-13.957
3.903
Just to make sure the 3 x 3 I used was:
0.75--------0.25----------0
0.7267-----0.2467-------0.0267
0.7033-----0.2433-------0.0533[/table]
and the column was:
1.0514
1.0606
1.0702
I don’t know why I dick around.
Here is theMatchcad worksheet
Yes, but the Excel screenshot only shows the numbers to four digits of precision. This matrix is at best ill-conditioned—it’s within 1.e-4 of a singular matrix—so taking the inverse of the rounded values will not in general come anywhere close to the value Excel calculates using the full-precision values. (For example, if the values rounded to .xx33 actually repeat .xx33333… and the values rounded to .xx67 actually repeat .xx66666…, this gives the matrix
3/4 1/4 0
109/150 37/150 2/75
211/300 73/300 4/75
which will display, to four digits of precision, exactly as the spreadsheet screenshot but which is clearly singular. Try changing the values slightly (in the fifth decimal place) in your worksheet; you will sometimes see the result change drastically.) Because Excel is giving him very large answers ~1e12, I’d guess that the actual matrix is pretty close to singular (though it might not be the exact matrix shown above).
It looks to me like the rows above 82 are being interpreted in pairs as 2x2 systems of equations (the coefficient of “z” is always zero), and the rows below 83 are being interpreted in triples as 3x3 systems. The problem is that the coefficients are apparently being incremented by the same value (within 1e-4) from row to row, so any set of three equations will be singular (since subtracting any two coefficient vectors gives a vector proportional to the coefficient increment [-0.0233,-0.0033,0.0267]). This is probably not right, but without knowing what the spreadsheet is supposed to be doing I can’t tell what the right thing to do would be.
That’s true. The ratio of the first column entries to the second column entries are:
3, 2.946 and 2.891
and this causes difficulty. Mathcad computes to 15 places but that’s to no avail if the input is only to 4.
David Simmons
I decided to go to a “neutral party” on this matter:
www.quickmath.com
I input the same values as you and got
X= 6.0542
Y= -13.957
Z= 3.9028
Which is in agreement with YOUR ANSWERS. SO…
I decided to give my calculator
http://www.1728.com/unknwn3.htm
one more try and got:
NEW VALUES OF:
X= 6.05420000001428
Y= -13.957000000036405
Z= 3.9028000000481464
I must have input a wrong number for both the calculator entries and an offline Excel spreadsheet I wrote. :smack:
When I wrote the Excel spreadsheet I was testing it by copying the input numbers from the online calculator (which MUST have been input wrong) instead of the original data !!! (No wonder they agreed) :smack:
FINALLY,
it looks as if you, www.quickmath.com, my online calculator AND the offline spreadsheet are all in agreement !!!
Damn - I can’t believe I made an input error :smack:
Okay so what was the question again?
And where the heck has greg gone ?
