Home > Forum Home > Excel Multiple Regression Analysis and Forecasting Template > Data Input for Regression Analysis Share

Data Input for Regression Analysis

Excel Help for Data Input For Regression Analysis in Excel Multiple Regression Analysis and Forecasting Template


Forum TopicPost Reply Login

Data Input For Regression Analysis

Rate this:
(3/5 from 1 vote)
ConfusedI would like to determine if the amount of salary earned in a particular grade level is related to a person's time in the job; time with the company; their gender; and/or their race. 

Would I plug all the different salary amounts into the "dependent variable" and the date entered the job; date hired with the company, their gender; and their race into separate independent variables? 
 Posted by on
 
Replies - Displaying 1 to 8 of 8Order Replies By: Most recent | Chronological | Highest Rated
Confused
Rate this:
(3/5 from 1 vote)
Here are some steps you can follow when running your own regression analysis using Excel:Enter your data into Excel.Install Data Analysis ToolPak plugin. Open "Data Analysis" to reveal the dialog box.Enter variable data.Select output options.Analyze your results. ...Create a scatter plot.Add regression trendline.
Regards,Rachel Gomez
 rachel
 Posted by on
Grateful
Rate this:
(3/5 from 1 vote)
Thank you for the continued assistance.
 Posted by on
Confused
Rate this:
(3/5 from 1 vote)
The numbers within the resulting multiple regression equation didn't come through in your post.  However, to understand which independent variables have the greatest impact on the dependent, you can look at the independent analysis.  Those with the largest absolute gradient have the greatest impact and their ability to describe the changes in the dependent variable are reflected in their r-squared statistic.
 Excel Business Forums Administrator
 Posted by on
Confused
Rate this:
(3/5 from 1 vote)
I have run a regression (for the first time) and would like a little help in the interpretation.  I have pasted the results below.  The dependent Variable is "hired or not hired" (put in as "1" for being hired and "0" if not) - as you can see.  Can you tell me which factors have been determined by the analysis to be the most important to being hired - and what the statistics mean (in easy terms)?  I appreciate any help on this to get me started in having knowledge to do this in the future.

Regards
BD

                     
False Bitmap
Equation Parameters
                 False Bitmap
 
 
R Square0.0598 05.98% of the change in Hired-not hired can be explained by the change in the 10 Independent Variables    
Adjusted R Square0.0536 Adjusted for Sample Size bias  0.39002  Durbin-Watson StatisticCritical D-W Values: Lower (Dl)=1.57; Upper (Du)=1.78False Bitmap
 
Standard Error0.4160 to +/- on result of Regression Equation  Therefore Positive Autocorrelation detected at 95% Confidence           
F - Statistic9.6729 Therefore analysis IS Significant 1.83687  Critical F-Statistic at 95% Confidence (Significance holds to 99.9% Level of Confidence)
                     
False Bitmap
 Multiple Regression Equation
 False Bitmap
Independent Analysis
 False Bitmap
Auto Correlation
False Bitmap
Tests for Multicolinearity between Independent Variables
 CoefficientsStandard Error  R Squared Gradient Intercept Dl=1.65 Du=1.69Adjusted R-Squared against other Indep Independent R-Square Matrix
Intercept0.2620.042     DW-Stat
gender0.1580.030 3.62%0.200.08  24.05%100%1%4%5%14%12%12%3%4%6%gender
HS-GED-0.2240.043 2.03%-0.200.42  1.23%1%100%19%24%8%10%9%21%19%14%HS-GED
screw gun0.0530.039 0.17%0.040.21  45.13%4%19%100%59%27%34%31%52%25%42%screw gun
drill-0.0170.047 0.04%0.020.23  63.66%5%24%59%100%30%39%34%60%27%47%drill
band saw0.0620.036 0.76%0.070.20  52.52%14%8%27%30%100%56%49%27%17%31%band saw
table saw-0.0260.037 0.33%0.050.21  54.73%12%10%34%39%56%100%47%34%18%34%table saw
drill press-0.0050.033 0.38%0.050.21  37.38%12%9%31%34%49%47%100%31%17%34%drill press
staple gun0.0730.041 0.13%0.030.22  56.25%3%21%52%60%27%34%31%100%28%43%staple gun
laborer-0.0200.029 0.00%-0.010.24  28.59%4%19%25%27%17%18%17%28%100%25%laborer
power tool-0.0350.034 0.04%0.020.23  41.66%6%14%42%47%31%34%34%43%25%100%power tool
Hired-not hired = 0.16*gender + -0.22*HS-GED + 0.05*screw gun + -0.02*drill + 0.06*band saw + -0.03*table saw + -0.01*drill press + 0.07*staple gun + -0.02*laborer + -0.03*power tool + 0.26 (+/- 0.42)genderHS-GEDscrew gundrillband sawtable sawdrill pressstaple gunlaborerpower toolFalse Bitmap

 Posted by on
Applaud
Rate this:
(3/5 from 1 vote)
Thanks again.
 Posted by on
OopsYes - it would make sense to convert boolean values such as gender to numeric values in order to create forecasts using the numeric values in the regression formula.
 Excel Business Forums Administrator
 Posted by on
Confused
Rate this:
(3/5 from 1 vote)
Thank you.  Would the analysis also perform better if I converted the gender (female or male) to numeric (1 or 2) data - as well as converting race data to numeric?
 Posted by on
Happy
Rate this:
(3/5 from 1 vote)
It is correct that the salary grades should be entered as the dependent variable in this multiple regression analysis application.

For the dates that the employee entered the job and was originally hired in the company, it would be better to convert this into duration such as the number of days.  While the resulting regression equation can apply the date values, the use of the equation will be more relevant using duration values.
 Excel Business Forums Administrator
 Posted by on
 Displaying page 1 of 1 

Find relevant Excel templates and add-ins for Data Input for Regression Analysis in the