data analysis regression excel slightly off

Although some of these tools offer older dialog boxes that really need updating, they can often produce far more detailed results. Click the icon and Excel offers a list of tools. You will now have a Data Analysis icon on the right side of the Data ribbon tab. Add a checkmark next to Analysis ToolPak and click OK. In this example, Sally was correct that ice cream sales are dependent on rain and temperature, but even a powerful regression engine could not predict the absolutely correct formula.Īlternate Strategy: The Analysis ToolPak still offers tools to do Regression, as well as testing correlation, exponential smoothing, create histograms, generate random numbers, create samples, and more. Because I created the spreadsheet used here, I know that the actual data in the gelato model uses the formula (Temperature - 50) x $2 if raining and (Temperature - 50) x $6 if not raining. The fact is that life may not fit in a straight-line formula. The 0.88 value here confirms that the prediction model is pretty good but not perfect.Īdditional Details: Regression models try to force actual results into a straight-line formula. It ranges from 0 to 1, where 1 is a perfect match, and 0 is a horrible match.

I only somewhat paid attention in statistics class, but I know that a key statistical indicator is the R-squared value. Excel performs the regression and provides statistics. You will get the results shown here.įigure 481. Choose five rows and several columns before entering the formula.ĥ. Be sure to enter the function in a five-row range.įigure 480. To get the additional statistics that LINEST can return to show how well the results match reality, add a fourth argument: TRUE. The predictions in D11 and D12 are off by $20 each-an error of 10%.įigure 479.

The prediction in D6 is right on the mark. Enter one formula in three cells.ģ.Ğnter a prediction formula in column D to see how well the regression calculation describes sales. Press Ctrl+Shift+Enter to calculate the array formula.įigure 478. The second argument is the range of temperatures and rainfall.Ģ.

The first argument is the range of known sales figures. LINEST is going to return three values, so select a range of three cells that are side by side. The LINEST function can return the values M1, M2, and b that best describe your sales model.

Data analysis regression excel slightly off