Excel Solver - variables not changing or solution not correct

Hi

I am trying to solve an equation using Excel Solver. I am new to this function and have been self teaching but I think I'm at an impasse and looking for some help.

In the attached file there are 9 variables in row 2 - these are what I am looking to solve. The formula is in column E. It is the variables multiplied by a value + the next variable multiplied by the next value....

Like so....OA = (CON*A)+(TAL*B)+(EXP*C)....

I Know the values for OA, CON, TAL, as these are in the data set. I am trying to calculate A, B, C... I know most of the variables (H2:O2) are between 0.05 and 0.20 and that the variable in R2 is between -0.05 and -0.20.

So Far I have tried using solver to find a value of 0 when we subtract the calculated OA using my formula from the actual OA. I believe this should work in theory but solver is saying it has a solution and that solution doesn't end up being close to 0. I have tried different solving methods and I understand the solution is linear so mainly trying the LP Simplex engine with constraints added but either the solution isn't near 0 or the variable values don't change.

Please let me know your thoughts and / or any other information that could help you help me

I think this is the wrong approach and very hard for solver to perform. Try analysis tools regression. Analysis is an add in which comes with excel, but may or may not be installed on your computer. It can be done with formula on excel, but this is simpler

http://cameron.econ.ucdavis.edu/exce...egression.html
https://medium.com/magnimetrics/mult...l-6f1436acd896

OA = (CON*A)+(TAL*B)+(EXP*C) is the same as a multiple regression equation with the exception of a constant and you can exclude a constant when it calculates so the same!

move the columns so all your Xs (independent variables) are in a continuous range (eg put Wei in the column next to the rest of the indpendent variables)

Select constant is 0

I get Coefficients as below
Intercept 0 'as I said you do not want one
CON 0.192261848
TAL 0.175138974
EXP 0.160960841
AGG 0.173103678
TEI 0.193572168
STA 0.144270823
CHA 0.092976868
MOT 0.095110747
WEI -0.120752538


R-squared is 1 so it is a perfect fit!

It seems to me that the value returned from Solver of 1.66E-11 (0.0000000000166) is very close to zero.

HTH,

Francesco

As Hyraulics noted, Solver rarely if ever comes to a solution where the objective function (OF) exactly equals 0. Solver aims for the "target +/- error" (see some of Solver's options) and terminates when the OF is within error of target (0 in this case).

As davsth, this looks like a regression type of problem. sum of differences is 0 is rarely if ever used in most regression problems because this OF usually has multiple solutions where the amount of total positive difference is roughly the same as the total negative difference. The "least squares" part of least squares regression algorithms refers to the objective function which is to find the minimum in the sum of the squares of the differences (=SUMSQ() in F5436).

As davsth also noted, seeing this as a regression problem, it is also a linear regression problem. In addition to using the built in regression tool, you could also use the LINEST() function https://support.microsoft.com/en-us/...rs=en-us&ad=us After moving the WEI column over adjacent to the other "x" columns, =LINEST(G5:G5435,H5:P5435,FALSE). With an r^2 of five 9's, the linear least squares fit is really good. The most irritating part, IMO, of using LINEST() for this is that the parameter output order is backwards from the input order (ie, the input order is CON -> WEI, but the output order is WEI -> CON). See my old discussion here: https://www.excelforum.com/excel-gen...put-order.html

You need to know enough about your problem to know why you chose the OF you did and whether or not a linear least squares regression would be a suitable approach.

In the OP, you presented this as an "equation solving" problem. We looked at your problem and observed that it appeared to us to be a curve fitting/regression problem to which we then applied the most common regression methodology -- linear least squares. Without know what the process is that generated the data or the methods used to model those kinds of processes, we were speaking rather naively about the problem. Of course, choice of regression algorithm, whether regression a good description of the problem, etc are all non-Excel questions that we are not always good at answering.

As for resources to better understand what regression generally (and linear least squares regression specifically), here's a link to a page at NIST (US government standards): https://www.itl.nist.gov/div898/hand...tion1/pmd1.htm that talks about process modeling.