Regression Analysis for the Attached Data - help needed - TIA

[Tim Folkerts]

Try this spreadsheet -- it calculates the fit and the error. I made a couple different errors the first time I created the spreadsheet and got poor results like you did. But then I figured out the problems and then the regression results did indeed give a good fit.

[quick_silver]

In Reply to Parent Post by Tim Folkerts

Try this spreadsheet -- it calculates the fit and the error. I made a couple different errors the first time I created the spreadsheet and got poor results like you did. But then I figured out the problems and then the regression results did indeed give a good fit.

Thank you Tim Folkerts. I checked the attached file and i've seen that the error is already small and the predicted output is almost same with the actual dependent variable....I looked at how the predicted value was calculated and it was the summation of the product of coefficients and the actual value for the day... I got very big variance because when I calculated the predicted value I summed the product of the (coefficient divide by 100) times the (actual value of independent variable divide by the value of the independent variable of the model) times the (value of the dependent variable of the model)

the value of the independent variable of the model was the average for all the days
the value of the dependent variable of the model was the average value of the dependent variable for all days

But this kind of calculating the predicted output. i used it on the other data and i got closer values. So does it means that the equation in determining the predicted output can be changed or is not fixed...Please enlighten me.. Thanks a lot..

[Barbara B]

In Reply to Parent Post by quick_silver

So does it means that the equation in determining the predicted output can be changed or is not fixed...

What do you mean with "changed"? Different values for the input variables? Different equation structure (e.g. quadratic effects additional to main effects, interactions)?

[quick_silver]

In Reply to Parent Post by Barbara B

What do you mean with "changed"? Different values for the input variables? Different equation structure (e.g. quadratic effects additional to main effects, interactions)?

I was asking about the different equation structure. Because I was working with a different set of data and the equation that I used there doesn't work with the equation for the data attached in this thread...

[quick_silver]

Just a clarification on the interpretation of the equation..does it mean that the variables C,G,H and I negatively impacts the dependent variable? Thanks a lot..so in this case if we will go with G for instance it will negatively affects the overall (which is the dpendent variable)...

[Barbara B]

In Reply to Parent Post by quick_silver

I was asking about the different equation structure. Because I was working with a different set of data and the equation that I used there doesn't work with the equation for the data attached in this thread...

The coefficients will change if you're using different data and/or model structure, because they are calculated out of the data values with respect to the model structure.

If you're using a statistical software package instead of Excel you'll get not only the coefficients but also a whole bunch of informations about several other relevant aspects of a model:

• effect size of an input variable, e.g. "Is G a vital factor to explain the outcom 'Dependent Variable'?" The answer is "no" here for your data and a model with main effects only, because the p value is high (p=0.365).
• observations which aren't properly described by the model, e.g. high standardized residuals for obs. no. 11, 12, 18, 32, 55, 88, 98 and 125.
• adjusted coefficient of determination R²(adj) to assess the reliability of a model and as a figure for comparison of different models (higher is better), e.g. R²(adj)=86.57% (good) for the main effects model.
• confidence intervals for coefficients to assess the certainty of statements derived out of the model, e.g. the coefficient of A in the main effects model (coef[A]=0.4666) has a 95% confidence interval of ( 0.4238 ; 0.5095 ).
• VIFs (variance inflation factors) to evaluate the stability of a model and multicollinearity amoung the input variables. VIFs should be small with 1 being the optimal value. For VIFs above 10 the input variables are highly correlated among themselves, so the recommendation is to have VIFs smaller than 5. Conclusions drawn out of an instable model could be misleading. In your model with main effects only the VIFs are smaller than 1.8 (good), but if you're trying to fit interactions (e.g. A*B) or quadratic effects (A*A) the VIFs are getting huge (e.g. with A*B maximum of VIF is 108.7 and with A*A the highest VIF value is 87.8 - way too high for a stable model in both cases).
• variation not explained by the model in the unity of the response, e.g. S=0.1483[unity of 'Dependent Variable'] for the main effects model
• predicted R² for evaluation of the reliability of predictions for new data, e.g. R²(pred)=66.71% (a little bit too small)f or the main effects model.
• graphs and tests to assess the model quality and Gauss-Markov assumptions (see Gauss–Markov_theorem), usually done via the residuals graphs and test of normality for the residuals. For your data with all observations the residuals aren't normally distributed due to unusual observations (see above), but the rest looks okay (no pattern, trends, etc. in the residuals vs. fits and residuals vs. obs no graphs).
• Prediction AND prediction intervals, e.g. if all input variables are set to their means you'll get a fit/predicted value of 3.32682 and a 95% prediction interval (3.03224 ; 3.62141).

Therefore I won't recommend Excel for modelling, because the calculation of coefficients and fits only isn't sufficient to evaluate the model and be sure to get good, reliable statements and conclusions out of the model

In Reply to Parent Post by quick_silver

Just a clarification on the interpretation of the equation..does it mean that the variables C,G,H and I negatively impacts the dependent variable? Thanks a lot..so in this case if we will go with G for instance it will negatively affects the overall (which is the dpendent variable)...

Yes, that's correct As stated before G doesn't have a vital (significant) impact on the 'Dependent Variable' and could be removed from the model, but the negative impact of C, H and I remains even if G is excluded.