“Regression” Data Analysis Tool in Microsoft Excel 2010

Regression is an Analysis Tool, which we use for analyzing large amounts of data and making forecasts and predictions in Microsoft Excel 2010.

The Regression function is part of Add INS in Microsoft Excel 2010 and 2013.

To access this tool, click on Data Tab, click on Data Analysis in the Analysis group. If the Data Analysis command is not available then you need to load the Analysis Toolpak.

To load and activate the Analysis Toolpak follow below mentioned steps:-

  • Click the File Tab, Click options and click on Add INS Category.

img1

  • In the Manage box, select the Analysis Toolpak and then click on the Go button.

img2

  • In the Add Ins dialog box, select the Analysis Toolpak check box and then click Ok.
  • If Analysis Toolpak is not listed in the Add Ins available box, click Browse to locate it.

img3

Let’s come back to the topic, we have cost data to month wise Service calls. Column A Contains Month, Column B contains Number of Calls, and Column C contains Cost of calls.

img4

To use the Regression Tool follow below mentioned steps:-

  • Go to Data Tab.
  • Click on Data Analysis in the Analyses group.

img5

  • Data Analysis dialog box will appear.

img6

  • From the Analysis tool drop down menu, select Regression and click on ok.
  • You will get another Regression dialog box will appear.
  • In the Input Y range field, select the range of total cost C2:C13.
  • In the input X range field, select the range of number of calls B2:B13.
  • In the output range selects any cell where you want to return the result.
  • Click on Residuals.

img7

img8

img9

R Square

R square equals 0.247. 24% of variation in cost of calls.

Significance F and P-Values

To check if the result isdependable (statistically significant), look at implication F (0.001). If the value is less than 0.05, this is ok. If implicationF is greater than 0.05, it’s probably better to stop using the set of self-determining variables. Delete a variable with a high P-Value (greater than 0.05) and return the decline until implication F drop below 0.05.

img10

Coefficients: -You can use Coefficient for forecasting.

img11

The Residuals show you how far away the actual data points are from the predicted data points (using the equation). For example the first data point equals 58752. The residual is giving 58752-68245.897 = -9493.898

This is all about the Regression Tools in Microsoft Excel 2010 and 2013.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube