But you also need to check p-values in range I17: I19 to see if constant and independent variables are useful for the prediction of the dependent variable. Only if p-value in cell J12 is less than 0.05, the whole regression equation is reliable. However, to see if the results are reliable, you also need to check p-values highlighted in yellow. The equation should be Annual sales = 1589.2 + 19928.3*(Highest Year of School Completed) + 11.9*(Motivation as Measured by Higgins Motivation Scale). And coefficients (range F17: F19) in the third table returned you the values of constants and coefficients. The higher R-square (cell F5), the tight relationship exists between dependent variables and independent variables. It is better to always put the dependent variable (Annual sales here) before the independent variables. Therefore, the equation will be:Īnnual sales = constant + β1*(Highest Year of School Completed) + β2*(Motivation as Measured by Higgins Motivation Scale) Set Up ModelĪnnual sales, highest year of school completed and Motivation was entered into column A, column B, and column C as shown in Figure 1. After you get values of constant, β1, β2… βn, you can use them to make the predictions.Īs for our problem, there are only two factors in which we have an interest. The change in Y each 1 increment change in xnĬonstant and β1, β2… βn can be calculated based on available sample data. The change in Y each 1 increment change in x2 The change in Y each 1 increment change in x1 Here are the explanations for constants and coefficients: Y And this kind of linear relationship can be described using the following formula: Generally, multiple regression analysis assumes that there is a linear relationship between the dependent variable (y) and independent variables (x1, x2, x3 … xn). Motivation as Measured by Higgins Motivation Scale Whether education or motivation has an impact on annual sales or not? Highest Year of School Completed Click “OK” and a new workbook opens with the analysis.Suppose that we took 5 randomly selected salespeople and collected the information as shown in the below table. Select “New Workbook” in the Output Options section to have the analysis placed in a new workbook. Note that there is an additional “Rows Per Sample” field if you selected “Anova: Two-Factor With Replication.” In the example used above you would type “10” in this field for ten females and ten males. Leave the “Alpha” field at its default “0.5” value unless you have calculated a different alpha risk for your data analysis. This is automatically placed in the “Input Range” field. Drag the cursor across the cells to be analyzed in the workbook. For example, gender in the subjects taking the medication is known but is not to be analyzed.Ĭlick “OK” after selecting the appropriate Anova analysis. Select “Anova: Two-Factor Without Replication” when there are two sets of variables but the second variable is not to be analyzed. In the above example, this would be to factor in the effect of gender on the medication. Select “Anova: Two-Factor With Replication” when two different variables are present in the samples. Select “Anova: Single Factor” to test a hypothesis for a single analysis on two columns of data only such as one medication compared to the other without any other variables. The number of males and females must also be the same if you plan to use Anova analysis with replication.Ĭlick the “Tools” menu and select “Data Analysis.” Three types of Anova analysis are listed at the top of the window. If you had ten male and ten female subjects, for example, the first ten rows would have to be of one gender and the second ten of the other gender. Note that the second variable cannot be mixed in these columns when using Anova. You would then add a third column showing the sex of each subject beside the data. For example, if the gender of each subject was known, the data in both columns would have to be arranged by gender. Arrange the data to take into consideration a second variable if you have one in your data.