Standard Error of Prediction for Prediction Interval using Multiple Regression

[michaelsmith559]

I have calculated the standard error of prediction for linear regression following this video on youtube: https://www.youtube.com/watch?v=nFj7nAeGlLk
I have also watched his video on multiple regression and to calculate the standard error of prediction for multiple predictors, he multiplies excels regression standard error output by 10%. Okay for estimating, I guess. How could I use matrix multiplication to find the standard error of prediction for multiple regression. Thanks for any help.

Mike

 

[kalak]

I have calculated the standard error of prediction for linear regression following this video on youtube: https://www.youtube.com/watch?v=nFj7nAeGlLk
I have also watched his video on multiple regression and to calculate the standard error of prediction for multiple predictors, he multiplies excels regression standard error output by 10%. Okay for estimating, I guess. How could I use matrix multiplication to find the standard error of prediction for multiple regression. Thanks for any help.

Mike

Run a multiple regression on the following augmented dataset and check the regression coeff etc results against the YouTube ones.
Note that the dependent variable (sales) should be the one on the left. Also note the new (Pred) column and the values in the additional row.

To do this (i.e SE's of prediction etc.) for more independent variables just put in an extra column for each, but leave the same Pred column on the RHS of the datatset.

Day	Sales	Temp	Pred
1	20	65	0
2	22	68	0
3	21	66	0
4	23	75	0
5	25	81	0
6	26	76	0
7	28	78	0
8	30	85	0
9	30	88	0
10	35	95	0
11	29	90	0
12	28	82	0
13	26	80	0
14	26	76	0
15	24	74	0
16	24	73	0
17	20	73	0
18	18	77	0
19	19	68	0
20	20	60	0
21	0	70	-1

<tbody>
</tbody>

For more info check out
Article: The use of dummy variables to compute predictions, prediction errors, and confidence intervals
David S. Salkever
Journal of Econometrics 02/1976; 4(4):393-397. DOI:10.1016/0304-4076(76)90027-0 · 1.53

 

[michaelsmith559]

Not sure what you mean. I am looking for a formula that I can use to calculate the standard error of prediction for multiple predictors. Here is a regression output and formulas for prediction interval that I made up. What would the formula be for standard error of prediction if using multiple predictors? Thanks.

Excel 2012
	G	H	I	J	K
1	SUMMARY OUTPUT
2
3	Regression Statistics
4	Multiple R	0.999906954
5	R Square	0.999813918
6	Adjusted R Square	0.999812866
7	Standard Error	0.708186158
8	Observations	179
9
10	ANOVA
11		df	SS	MS	F
12	Regression	1	476960.1841	476960.2	951014.8
13	Residual	177	88.77039137	0.501528
14	Total	178	477048.9545
15
16		Coefficients	Standard Error	t Stat	P-value
17	Intercept	1	0.10630976	9.406474	2.69E-17
18	X Variable 1	0.998984884	0.00102439	975.1999	0
19
20
21
22
23	Desired Prediction Level:	90%		Mean:	90
24	Alpha:	10%		Std Dev:	51.81699
25
26	Value to Predict For:	180
27
28	Point Prediction:	180.8172792
29	t-value:	1.653508002
30	Standard Error of Pred:	0.716121079
31	Margin of Error:	1.184111935
32	Lower Bound:	179.6331673
33	Upper Bound:	182.0013911
34	Interval Width:	2.368223869
Sheet1

Cell Formulas
Range	Formula
H24	=1-H23
H28	=H17+H18*H26
H29	=TINV(H24,H13)
H30	=H7*SQRT(1+1/H8+(H26-K23)^2/K24^2/H14)
H31	=H29*H30
H32	=H28-H31
H33	=H28+H31
H34	=H33-H32
K23	=AVERAGE(INDIRECT("'Sheet1'!A2:A"&COUNT(Sheet1!A:A)))
K24	=STDEV(INDIRECT("'Sheet1'!A2:A"&COUNT(Sheet1!A:A)))

 

[kalak]

I have calculated the standard error of prediction for linear regression following this video on youtube: https://www.youtube.com/watch?v=nFj7nAeGlLk
...
How could I use matrix multiplication to find the standard error of prediction for multiple regression. Thanks for any help.

Mike

Post#2 does just what you requested in your post#1. That is, it uses matrix multiplication to find the standard error of prediction for multiple regression.
And it's backed up by a specific valid reference to one of the top academic journals in the world.

It uses matrix multiplication (and some inversion as well) to give the correct answer (which you can readily verify by looking at it) using the same data as in the Youtube video that you used.

If you want the same regression output as in that video just leave out the last row and the Pred column in post#2 when doing you regression.

If you additionally want the standard error of prediction interval then just include the last row and Pred column, do the regression and your requested information is on the bottom (Pred) line of the regression output. This was what post#2 suggested, although it seems you haven't tried it.

So at this stage it's not that clear to me just what you're looking for.
Is it to get operational results for a specific problem, which the approach of post#2 does as do many good regression/prediction programs commercially available?
Or is it to enhance your knowledge of regression/prediction statistical methods, which could be done by reference to any of the good statistical texts in the area.

 

[michaelsmith559]

I need more of a step by step example of how to do the matrix multiplication. In post #3, the formula in H30 is how the standard error of prediction was calculated for a simple linear regression. In excel formula notation what would the excel formula be for multiple regression? I would assume something like mmult would have to be used. Sorry if I was unclear in the other post.

 

[kalak]

I need more of a step by step example of how to do the matrix multiplication. In post #3, the formula in H30 is how the standard error of prediction was calculated for a simple linear regression. In excel formula notation what would the excel formula be for multiple regression? I would assume something like mmult would have to be used. Sorry if I was unclear in the other post.

Is this for some college assignment in statistics?

Formulas for standard errors of prediction in multiple regression are found in a variety of texts.

You could copy the version you want and post it here along with the specific data layout you want it referred to in an excel formula. Someone here should be able to help you.

I might have something like that in a file somewhere if I can find it, or can re-figure it out if I have time. Although I've never been enamoured with taking two steps over what can be far more conveniently done in one step.

 

[michaelsmith559]

No it is not for college, just learning some statistics on my own and want to know how to implement it into excel with a formula. As an example, when the guy on youtube did the prediction interval for multiple regression, I think he increased excels regression output standard error by 10% and used this as an estimated standard error of prediction. If using his example, how would he actually calculate, using excel formulas, the standard error of prediction? What would he have to type formula wise into excel in order to get the standard error of prediction for multiple predictors? In post #3 I showed the formulas used for simple linear regression, specifically look at the formula used in cell H30. How would these formulas look for multiple predictors? Any help, will be appreciated.

Mike

 

[michaelsmith559]

Found an answer. Here is some vba code and an example workbook, with the formulas. You will need to google this: <cite class="_Rm">www.iun.edu/~bnwbga/PredInt.xls

</cite>