Approximately solve system of equations

[zmalone]

I have a 3 variable system of equations (no solution) and am trying to solve for variables of an Ax = b to be as close to b as possible without changing A.

28446757643x + 82500000y + z = 13557300
283009432x + 283009432y + z = 10264100
14180045548x + 82500000y + z = 3651510

I am fiddinling around trying to solve in R but there are no exact solutions and I cannot figure out how to approximate the variables to give the closest values to the other side of the equation. Eventually this is all going in excel, so if there is a Solver way to handle this that might just be easier to use.

Any help is appreciated, thanks!

 

[HallsofIvy]

Those are linear equation with integer coefficients- there certainly are "exact solutions". The solutions are rational numbers so can be written as fractions. Subtracting the second equation from the first gives 25606748211x- 200509432y= 3293200. Subtracting the second equation from the third gives 13897036116x- 200509432y= -6612590. The arithmetic is tedious but very basic.

 

[zmalone]

well...

So I was just trying to see if there was a trend in this 3x3 subest of the entire matrix which is 266x3

So I guess to rephrase my problem, I will have 266 equations with 3 variables and that is why I want to approximate the variables as close as possible and examine the outliers. Is there a way in excel or R to automate this process that anyone could think of? Sorry if this sounds all over the place, it's been a while since I've had to do real math outside of school without being given equations haha. Thanks!

 

[SteamKing]

So I was just trying to see if there was a trend in this 3x3 subest of the entire matrix which is 266x3

So I guess to rephrase my problem, I will have 266 equations with 3 variables and that is why I want to approximate the variables as close as possible and examine the outliers. Is there a way in excel or R to automate this process that anyone could think of? Sorry if this sounds all over the place, it's been a while since I've had to do real math outside of school without being given equations haha. Thanks!

It's not clear from where you develop these 266 equations, but you have an overdetermined system of linear equations. Such a system arises often in doing a linear regression on a set of data. You write an equation in the unknown linear coefficients for each data point in your data set, and then you want to find the linear coefficients which minimize the error between the regression line and the data points.

http://en.wikipedia.org/wiki/Overdetermined_system

The traditional method to solve such systems is to form the so-called 'normal equations'.

You start with the matrix equation:

Ay = b,

where A is an n x m rectangular matrix, and y and b are n x 1 column vectors. To form the normal equation, multiply both sides by the transpose of matrix A:

$A^{T}A y = A^{T}b$

The product $A^T A$ is a matrix with n rows and n columns [edit: corrected size of normal matrix], and in theory the resulting normal equations could be solved using standard techniques, like Gaussian elimination, for example.

However, for reasons too technical to discuss here, solving the normal equations can lead to inaccurate results due to round off error in floating point calculations. Fortunately, different numerical methods have been developed which help to minimize loss of accuracy due to floating point errors. One of these is called the QR factorization or QR decomposition:

http://en.wikipedia.org/wiki/QR_factorization

The QR method allows you to factor the original overdetermined system without needing to form the normal equations. It's one of the more useful developments made by numerical analysis in the 20th century, IMO.