I was just reading about things in general when something just dawned on me: SOLVER will solve regression problems. A whole variety of regression problems. To prove that, I build a series of models using SOLVER and found that it is true. Jut when you think it’s a waste of time to learn yet another regression technique, SOLVER will solve your simple regression problems, your logarithmic, power, exponential and polynomial problems.
To use the SOLVER method you set up your model and enter estimates for the answers you need. Then you run SOLVER and that’s it: done!
The best thing to do is to illustrate my ideas with examples.
Linear Regression: Ordinary Least Squares … X v Y
Set up the worksheet as you see below and program the Predict Y column to feed off the range G4:G5. This means, cell C4 contains the formula ==G$4+G$5*A4 which you fill down to C13.
In cells G4 and G5 enter your initial guess … try to make an intelligent guess but don’t worry if you are wrong because SOLVER will find the right answer for you.
You MUST find the SSE now, which is the sum of squared errors which means find the residuals for all rows and square each one then add all of them together. I have done that this way:
- For OLS, the R square value is found this way: =RSQ(B4:B13,A4:A13), in cell G8
- Now run SOLVER:
- Set Objective: you need to find the Minimum SSE figure … $G$7
- by changing the values in G4 and G5
- where the non negative constraint is UNchecked and you use GRG Nonlinear
- Click Solve
- In this example, the equation you get is y = a + bX = 13.932159 + 0.000698X
- SSE minimum = 442.98
- R^2 = 0.0021
I verified my results by adding a trendline to the graph of the data that I prepared and it gave me the same answers, except that the graph does not show the SSE value
I went to the web site you can see in the following graphic and worked through the example you see there. This relates to a non linear example and the regression equation in C5 is =G$5(1-EXP(-G$6A5))+$G$7, using the EXP() function.
Otherwise, look at this example in the file you can download from the link below and make sure it works for you.
I prepared two graphs to show the actual data in blue and the predicted data in orange. If nothing else, you can see that the R^2 value must be 1 or very nearly 1
A polynomial example now but with two parameters held constant. Again, work you way through this example and by all means, go to the web page that I also referred to and ensure everything is clear for you.
The formula we are programming is in the range D7:D14 … Y = min+(max-min)/(1+10^((logEC50-log(x))*slope)))
I have used range names to help here and do notice that min and max are held constant at 0 and 100 respectively in this example, so SOLVER is only solving for H8:H9
Again you will see a graph here that confirms the goodness of fit of our work and again you will see an excellent result. By the way, examples 2 and 3 relate to scientific examples whose outcomes are predictable and reliable.
Training and Testing Model
I even prepared a model for training and testing data. This is not in the downloadable file you can see below but you can get it by asking me for it: it relates to the prices of second hand(used) Rolls Royce Cars.
To run a training and testing model, gather your data and set up the model as with any other regression exercise. However, in my case I used the first 56 rows of data to create the training model and the remaining 13 rows of data for the testing model. To do this I just took the data from the source in the order that they presented it: I did not sorting or evaluation of any kind.
If the data are fairly stored by the source, then the training model should be useful to use to make predictions for the testing data.
So, find your regression coefficients and apply them ONLY to the testing data. Evaluate your results! One way of testing the model is by finding the Mean Squared Error (MSE) of the model … the smaller the better. In my case the training MSE was much smaller than the testing MSE.
You might or might not be surprised at the ability of SOLVER to do these things but it does and in my file you will find a total of nine worksheets and that includes the relatively simple examples above, all the way to solving for this equation: =-G$9A4^5+G$8A4^4-G$7A4^3+G$6A4^2-G$5*A4+G$4
Please note, I have found that SOLVER and/or Excel is not always consistent in the answers they provide and running SOLVER two or three times on the same data before deciding that that is the final answer might be necessary. Moreover, the following screenshot shows that SOLVER gives a different answer to the TRENDLINE and the result from the data analysis toolpak:
There you are: try it and see what you find. Take my lesson, though; and ensure you verify any answers to a complex example by running the ToolPak and even the LINEST function as well as using SOLVER.
Download my file from here regression_using_SOLVER
14th April 2018