In other words, we are trying to find A1, A2, and B1 that will minimize the number in cell G13. We are trying to find the settings for the Decision Variables that will minimize the sum of the squares of the differences.

The square of each difference is taken and then all squares are summed up. We now take the difference between the actual number of sales and the number of sales predicted by our model with our arbitrary settings for the Decision Variables. We have arbitrarily set our Decision Variables for: In Solver language, these solves that we are changing are called Decision Variables. We are listing the variable that we are solving for (A1, A2, and B1) in cells B3 to B5. One example of this formula in action is explained for Cell E16. Here we have created an Excel model based upon our model of: This table shows the arrangement of data and the calculations. We need to arrange the data in a form that can be input into the Excel Solver as follows: We can use the Excel Solver to solve for A1, A2, and B1. Sales = A1 + A2 * (Number of Ads Running) B1 A formula for such a curve would have the general form: In this case it appears to a graph the has a diminishing y value for an increasing x value.

The first step is to eyeball the data and estimate what general type of curve this graph probably is.

We would like to create an equation from this data that allows us to predict the sales based upon the number of ads currently running. Here is an Excel scatter plot of that data: the number of ads that were running at different times. The purpose of this equation is to be able to predict the number of sales based upon the number of ads that will be run.Ī marketing manager has collected this following data on the company’s sales vs. In this problem we are going to show how to use the Excel Solver to calculate an equation which most closely describes the relationship between sales and number of ads being run. Solver then calculates all needed variables which produce the equation which most closely fits the data points. This information is in the form of the general equation that defines the curve, such as a0 + a1*x + a2*x2 = c or a*ln(xb) = c.

One very important caveat must be added: the user must first determine the general type of the curve and input that information into Solver at the start. The Excel Solver will find the equation of the linear or nonlinear curve which most closely fits a set of data points. Its curve-fitting capabilities make it an excellent tool to perform nonlinear regression. Nonlinear Regression in Excel How To Do Nonlinear Regression in ExcelĮxcel Solver is one of the best and easiest curve-fitting devices in the world, if you know how to use it.

Excel linear regression parameters