# Problems with Solving a System of Three Equations and Excel **URL:** \ **Category:** Factual Questions\ **Created:** [June 16, 2004, 8:36pm UTC](https://boards.straightdope.com/t/problems-with-solving-a-system-of-three-equations-and-excel/250729 "2004-06-16T20:36:49Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![gregongie](https://avatars.discourse-cdn.com/v4/letter/g/919ad9/32.png) [@gregongie](https://boards.straightdope.com/u/gregongie)\ **Post date:** [June 16, 2004, 8:36pm UTC](https://boards.straightdope.com/t/problems-with-solving-a-system-of-three-equations-and-excel/250729/1 "2004-06-16T20:36:49Z")
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](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? ---
**Author:** ![Jpeg\_Jones](https://avatars.discourse-cdn.com/v4/letter/j/4bbf92/32.png) [@Jpeg\_Jones](https://boards.straightdope.com/u/Jpeg_Jones)\ **Post date:** [June 16, 2004, 9:42pm UTC](https://boards.straightdope.com/t/problems-with-solving-a-system-of-three-equations-and-excel/250729/2 "2004-06-16T21:42:40Z")
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? ---
**Author:** ![wolf\_meister](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/wolf_meister/32/15202_2.png) [@wolf\_meister](https://boards.straightdope.com/u/wolf_meister)\ **Post date:** [June 16, 2004, 10:28pm UTC](https://boards.straightdope.com/t/problems-with-solving-a-system-of-three-equations-and-excel/250729/3 "2004-06-16T22:28:44Z")
Here is my online calculator for solving three unknowns: [http://www.1728.com/unknwn3.htm](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](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. ---
**Author:** ![Strainger](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/strainger/32/244_2.png) [@Strainger](https://boards.straightdope.com/u/Strainger)\ **Post date:** [June 16, 2004, 10:58pm UTC](https://boards.straightdope.com/t/problems-with-solving-a-system-of-three-equations-and-excel/250729/4 "2004-06-16T22:58:06Z")
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). ---
**Author:** ![Omphaloskeptic](https://avatars.discourse-cdn.com/v4/letter/o/bcef8e/32.png) [@Omphaloskeptic](https://boards.straightdope.com/u/Omphaloskeptic)\ **Post date:** [June 17, 2004, 12:17am UTC](https://boards.straightdope.com/t/problems-with-solving-a-system-of-three-equations-and-excel/250729/5 "2004-06-17T00:17:48Z")
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. ---
**Author:** ![wolf\_meister](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/wolf_meister/32/15202_2.png) [@wolf\_meister](https://boards.straightdope.com/u/wolf_meister)\ **Post date:** [June 17, 2004, 12:24am UTC](https://boards.straightdope.com/t/problems-with-solving-a-system-of-three-equations-and-excel/250729/6 "2004-06-17T00:24:27Z")
Okay, I’ll try that URL once again: [Cramer’s Rule](http://www.okc.cc.ok.us/maustin/Cramers_Rule/Cramer's%20Rule.htm) 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). ---
**Author:** ![wolf\_meister](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/wolf_meister/32/15202_2.png) [@wolf\_meister](https://boards.straightdope.com/u/wolf_meister)\ **Post date:** [June 17, 2004, 12:28am UTC](https://boards.straightdope.com/t/problems-with-solving-a-system-of-three-equations-and-excel/250729/7 "2004-06-17T00:28:19Z")
**Omphaloskeptic** You recommended the “MDETERM in Excel”. Hmmm great minds think alike … but some type faster than others LOL. ---
**Author:** ![Omphaloskeptic](https://avatars.discourse-cdn.com/v4/letter/o/bcef8e/32.png) [@Omphaloskeptic](https://boards.straightdope.com/u/Omphaloskeptic)\ **Post date:** [June 17, 2004, 12:32am UTC](https://boards.straightdope.com/t/problems-with-solving-a-system-of-three-equations-and-excel/250729/8 "2004-06-17T00:32:13Z")
Also, have you tried using MDETERM? 😃 ---
**Author:** ![wolf\_meister](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/wolf_meister/32/15202_2.png) [@wolf\_meister](https://boards.straightdope.com/u/wolf_meister)\ **Post date:** [June 17, 2004, 1:04am UTC](https://boards.straightdope.com/t/problems-with-solving-a-system-of-three-equations-and-excel/250729/9 "2004-06-17T01:04:47Z")
**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](http://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). 😃 ---
**Author:** ![Omphaloskeptic](https://avatars.discourse-cdn.com/v4/letter/o/bcef8e/32.png) [@Omphaloskeptic](https://boards.straightdope.com/u/Omphaloskeptic)\ **Post date:** [June 17, 2004, 1:15am UTC](https://boards.straightdope.com/t/problems-with-solving-a-system-of-three-equations-and-excel/250729/10 "2004-06-17T01:15:39Z")
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. ---
**Author:** ![wolf\_meister](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/wolf_meister/32/15202_2.png) [@wolf\_meister](https://boards.straightdope.com/u/wolf_meister)\ **Post date:** [June 17, 2004, 1:40am UTC](https://boards.straightdope.com/t/problems-with-solving-a-system-of-three-equations-and-excel/250729/11 "2004-06-17T01:40:06Z")
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. ---
**Author:** ![Omphaloskeptic](https://avatars.discourse-cdn.com/v4/letter/o/bcef8e/32.png) [@Omphaloskeptic](https://boards.straightdope.com/u/Omphaloskeptic)\ **Post date:** [June 17, 2004, 3:21am UTC](https://boards.straightdope.com/t/problems-with-solving-a-system-of-three-equations-and-excel/250729/12 "2004-06-17T03:21:51Z")
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. ---
**Author:** ![Omphaloskeptic](https://avatars.discourse-cdn.com/v4/letter/o/bcef8e/32.png) [@Omphaloskeptic](https://boards.straightdope.com/u/Omphaloskeptic)\ **Post date:** [June 17, 2004, 3:31am UTC](https://boards.straightdope.com/t/problems-with-solving-a-system-of-three-equations-and-excel/250729/13 "2004-06-17T03:31:57Z")
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.) ---
**Author:** ![wolf\_meister](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/wolf_meister/32/15202_2.png) [@wolf\_meister](https://boards.straightdope.com/u/wolf_meister)\ **Post date:** [June 17, 2004, 5:00am UTC](https://boards.straightdope.com/t/problems-with-solving-a-system-of-three-equations-and-excel/250729/14 "2004-06-17T05:00:11Z")
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? ---
**Author:** ![David\_Simmons](https://avatars.discourse-cdn.com/v4/letter/d/9de053/32.png) [@David\_Simmons](https://boards.straightdope.com/u/David_Simmons)\ **Post date:** [June 17, 2004, 5:48am UTC](https://boards.straightdope.com/t/problems-with-solving-a-system-of-three-equations-and-excel/250729/15 "2004-06-17T05:48:08Z")
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 ---
**Author:** ![David\_Simmons](https://avatars.discourse-cdn.com/v4/letter/d/9de053/32.png) [@David\_Simmons](https://boards.straightdope.com/u/David_Simmons)\ **Post date:** [June 17, 2004, 5:54am UTC](https://boards.straightdope.com/t/problems-with-solving-a-system-of-three-equations-and-excel/250729/16 "2004-06-17T05:54:50Z")
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 ---
**Author:** ![David\_Simmons](https://avatars.discourse-cdn.com/v4/letter/d/9de053/32.png) [@David\_Simmons](https://boards.straightdope.com/u/David_Simmons)\ **Post date:** [June 17, 2004, 6:06am UTC](https://boards.straightdope.com/t/problems-with-solving-a-system-of-three-equations-and-excel/250729/17 "2004-06-17T06:06:05Z")
I don’t know why I dick around. Here is the[Matchcad worksheet](http://f1.pg.photos.yahoo.com/ph/dwsimm2000/detail?.dir=/St+Dp&.dnm=c153.jpg) ---
**Author:** ![Omphaloskeptic](https://avatars.discourse-cdn.com/v4/letter/o/bcef8e/32.png) [@Omphaloskeptic](https://boards.straightdope.com/u/Omphaloskeptic)\ **Post date:** [June 17, 2004, 10:47am UTC](https://boards.straightdope.com/t/problems-with-solving-a-system-of-three-equations-and-excel/250729/18 "2004-06-17T10:47:58Z")
> [@David Simmons](#): > > 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 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 ```auto 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. ---
**Author:** ![David\_Simmons](https://avatars.discourse-cdn.com/v4/letter/d/9de053/32.png) [@David\_Simmons](https://boards.straightdope.com/u/David_Simmons)\ **Post date:** [June 17, 2004, 1:48pm UTC](https://boards.straightdope.com/t/problems-with-solving-a-system-of-three-equations-and-excel/250729/19 "2004-06-17T13:48:52Z")
> [@Omphaloskeptic](#): > > which will display, to four digits of precision, exactly as the spreadsheet screenshot but which is clearly singular. 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. ---
**Author:** ![wolf\_meister](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/wolf_meister/32/15202_2.png) [@wolf\_meister](https://boards.straightdope.com/u/wolf_meister)\ **Post date:** [June 17, 2004, 2:30pm UTC](https://boards.straightdope.com/t/problems-with-solving-a-system-of-three-equations-and-excel/250729/20 "2004-06-17T14:30:07Z")
**David Simmons** I decided to go to a “neutral party” on this matter: [www.quickmath.com](http://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](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](http://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 ? [Next page](https://boards.straightdope.com/t/problems-with-solving-a-system-of-three-equations-and-excel/250729.md?page=2)