UNISTAT - the ultimate Excel statistics add-in

Linear Regression in Excel with Unistat

The Unistat statistics add-in extends Excel with Linear Regression capabilities.

Further documentation is available at section 7.2.1 of the manual, Linear Regression.

Sample output

Here we provide annotated example report output from the Unistat Excel statistics add-in for data analysis.

Linear Regression

Regression Results

Valid Number of Cases: 33, 0 Omitted
Dependent Variable: temperature

	Coefficient	Standard Error	t-Statistic	Significance	Lower 95%	Upper 95%
Constant	5.4727	8.5311	0.6415	0.5264	-12.0024	22.9479
cm	0.3380	0.3225	1.0479	0.3036	-0.3227	0.9987
mm	0.8140	1.1999	0.6784	0.5031	-1.6438	3.2718
min	0.8143	0.4266	1.9086	0.0666	-0.0597	1.6882
ml	-4.3970	0.7238	-6.0751	0.0000	-5.8795	-2.9144

Residual Sum of Squares	171.0201
Standard Error	2.4714
Mean of Y	4.4545
Stand Dev of y	3.8413
Correlation Coefficient	0.7986
R-squared	0.6378
Adjusted R-squared	0.5861
F(4,28)	12.3268
significance of F	0.0000
Durbin-Watson Statistic	2.1570
log of likelihood	-74.1830
Press Statistic	247.2813

ANOVA of Regression

Due To	Sum of Squares	DoF	Mean Square	F-Stat	Signif
cm	51.022	1	51.022	8.353	0.0074
mm	22.403	1	22.403	3.668	0.0657
min	2.319	1	2.319	0.380	0.5428
ml	225.418	1	225.418	36.906	0.0000
Regression	301.162	4	75.290	12.327	0.0000
Error	171.020	28	6.108
Total	472.182	32	14.756	2.416	0.0101

Correlation Matrix of Regression Coefficients

	Constant	cm	mm	min	ml
Constant	1.0000	-0.5711	-0.9141	0.3421	-0.4426
cm	-0.5711	1.0000	0.2519	-0.3261	0.3251
mm	-0.9141	0.2519	1.0000	-0.3316	0.2118
min	0.3421	-0.3261	-0.3316	1.0000	-0.4068
ml	-0.4426	0.3251	0.2118	-0.4068	1.0000

Covariance Matrix of Regression Coefficients

	Constant	cm	mm	min	ml
Constant	72.7798	-1.5713	-9.3573	1.2453	-2.7329
cm	-1.5713	0.1040	0.0975	-0.0449	0.0759
mm	-9.3573	0.0975	1.4396	-0.1698	0.1839
min	1.2453	-0.0449	-0.1698	0.1820	-0.1256
ml	-2.7329	0.0759	0.1839	-0.1256	0.5238

Actual and Fitted Values

Row	Actual *	Fitted +	-3.0000 11.0000
1	6.0000	5.4399	+ *
2	1.0000	1.3627	*+
3	-2.0000	0.1851	* +
4	11.0000	8.7195	+ *
5	-1.0000	7.2171	* +
6	2.0000	2.7244	* +
7	5.0000	3.3487	+ *
8	1.0000	0.5218	+ *
9	1.0000	1.3888	*+
10	3.0000	2.6417	+*
11	11.0000	9.9415	+ *
12	9.0000	9.3458	*+
13	5.0000	4.6987	+ *
14	-3.0000	-2.0454	* +
15	1.0000	3.8400	* +
16	8.0000	7.4178	+ *
17	-2.0000	-0.9276	* +
18	3.0000	2.1171	+ *
19	6.0000	5.2984	+ *
20	10.0000	8.2386	+ *
21	4.0000	4.0762	*+
22	5.0000	3.1411	+ *
23	5.0000	6.0274	* +
24	3.0000	3.3652	* +
25	8.0000	1.2426	+ *
26	8.0000	5.7900	+ *
27	6.0000	7.2143	* +
28	6.0000	5.3121	+ *
29	3.0000	5.3368	* +
30	5.0000	4.0480	+ *
31	1.0000	3.2429	* +
32	8.0000	8.7425	* +
33	10.0000	7.9863	+ *

Residuals

Row	Residuals	-8.2171 8.2171
1	0.5601	*
2	-0.3627	*
3	-2.1851	*
4	2.2805	*
5	-8.2171	*
6	-0.7244	*
7	1.6513	*
8	0.4782	*
9	-0.3888	*
10	0.3583	*
11	1.0585	*
12	-0.3458	*
13	0.3013	*
14	-0.9546	*
15	-2.8400	*
16	0.5822	*
17	-1.0724	*
18	0.8829	*
19	0.7016	*
20	1.7614	*
21	-0.0762	*
22	1.8589	*
23	-1.0274	*
24	-0.3652	*
25	6.7574	*
26	2.2100	*
27	-1.2143	*
28	0.6879	*
29	-2.3368	*
30	0.9520	*
31	-2.2429	*
32	-0.7425	*
33	2.0137	*

95% Confidence Intervals for Mean and Actual Y Values

	lb Actual Y	lb Mean of Y	Fitted Y	ub Mean of Y	ub Actual Y
1	0.1965	4.0746	5.4399	6.8051	10.6832
2	-4.1514	-0.8228	1.3627	3.5482	6.8768
3	-5.2115	-1.6845	0.1851	2.0547	5.5818
4	3.3762	7.0098	8.7195	10.4293	14.0629
5	1.6948	5.0108	7.2171	9.4233	12.7394
6	-2.6776	0.8394	2.7244	4.6094	8.1264
7	-1.8272	2.2713	3.3487	4.4260	8.5245
8	-4.8537	-1.2858	0.5218	2.3295	5.8974
9	-3.9433	-0.2853	1.3888	3.0629	6.7209
10	-2.9213	0.3356	2.6417	4.9477	8.2046
11	4.2940	7.4382	9.9415	12.4449	15.5891
12	3.9722	7.5437	9.3458	11.1478	14.7194
13	-0.6029	3.1244	4.6987	6.2730	10.0003
14	-7.7730	-4.7244	-2.0454	0.6335	3.6821
15	-1.6934	1.6060	3.8400	6.0741	9.3735
16	1.9465	5.3425	7.4178	9.4931	12.8891
17	-6.3506	-2.8720	-0.9276	1.0168	4.4954
18	-3.3346	0.0942	2.1171	4.1399	7.5687
19	-0.1659	3.2416	5.2984	7.3551	10.7627
20	2.8694	6.4498	8.2386	10.0274	13.6078
21	-1.1422	2.8100	4.0762	5.3424	9.2946
22	-2.3912	0.9100	3.1411	5.3722	8.6734
23	0.5731	3.9973	6.0274	8.0575	11.4818
24	-1.9096	1.8836	3.3652	4.8468	8.6400
25	-4.1108	-0.4983	1.2426	2.9835	6.5960
26	0.2468	3.5318	5.7900	8.0482	11.3333
27	1.9352	5.7173	7.2143	8.7113	12.4934
28	0.0181	3.7635	5.3121	6.8608	10.6062
29	-0.3323	2.7853	5.3368	7.8883	11.0059
30	-1.2389	2.5238	4.0480	5.5721	9.3349
31	-2.4035	0.7422	3.2429	5.7437	8.8894
32	3.1669	6.4060	8.7425	11.0789	14.3181
33	2.5311	5.9539	7.9863	10.0187	13.4415

Case (Diagnostic) Statistics

	Fitted Values	Standard Error of Fitted	Standardised Fitted	Adjusted Fitted	Residuals	Standard Error of Residuals	Standardised Residuals	Studentised Residuals	Press (Deleted) Residuals
1	5.4399	0.6665	0.3212	5.3959	0.5601	2.3798	0.2266	0.2354	0.6041
2	1.3627	1.0669	-1.0078	1.4458	-0.3627	2.2292	-0.1468	-0.1627	-0.4458
3	0.1851	0.9127	-1.3917	0.5302	-2.1851	2.2967	-0.8842	-0.9514	-2.5302
4	8.7195	0.8347	1.3903	8.4259	2.2805	2.3262	0.9227	0.9803	2.5741
5	7.2171	1.0771	0.9005	9.1436	-8.2171	2.2244	-3.3249	-3.6941	-10.1436
6	2.7244	0.9202	-0.5640	2.8410	-0.7244	2.2937	-0.2931	-0.3158	-0.8410
7	3.3487	0.5259	-0.3605	3.2703	1.6513	2.4148	0.6682	0.6838	1.7297
8	0.5218	0.8825	-1.2819	0.4520	0.4782	2.3085	0.1935	0.2071	0.5480
9	1.3888	0.8173	-0.9993	1.4365	-0.3888	2.3324	-0.1573	-0.1667	-0.4365
10	2.6417	1.1258	-0.5909	2.5479	0.3583	2.2001	0.1450	0.1629	0.4521
11	9.9415	1.2221	1.7886	9.5990	1.0585	2.1481	0.4283	0.4927	1.4010
12	9.3458	0.8797	1.5944	9.3960	-0.3458	2.3095	-0.1399	-0.1497	-0.3960
13	4.6987	0.7686	0.0796	4.6664	0.3013	2.3489	0.1219	0.1283	0.3336
14	-2.0454	1.3078	-2.1188	-1.6742	-0.9546	2.0970	-0.3862	-0.4552	-1.3258
15	3.8400	1.0906	-0.2003	4.5269	-2.8400	2.2177	-1.1492	-1.2806	-3.5269
16	7.4178	1.0131	0.9659	7.3002	0.5822	2.2542	0.2356	0.2583	0.6998
17	-0.9276	0.9492	-1.7544	-0.7420	-1.0724	2.2818	-0.4339	-0.4700	-1.2580
18	2.1171	0.9875	-0.7619	1.9493	0.8829	2.2655	0.3573	0.3897	1.0507
19	5.2984	1.0041	0.2751	5.1597	0.7016	2.2583	0.2839	0.3107	0.8403
20	8.2386	0.8733	1.2335	7.9873	1.7614	2.3120	0.7127	0.7619	2.0127
21	4.0762	0.6181	-0.1233	4.0813	-0.0762	2.3929	-0.0308	-0.0318	-0.0813
22	3.1411	1.0892	-0.4281	2.6930	1.8589	2.2185	0.7522	0.8379	2.3070
23	6.0274	0.9911	0.5127	6.2243	-1.0274	2.2640	-0.4157	-0.4538	-1.2243
24	3.3652	0.7233	-0.3551	3.3994	-0.3652	2.3632	-0.1478	-0.1545	-0.3994
25	1.2426	0.8499	-1.0470	0.3364	6.7574	2.3207	2.7342	2.9118	7.6636
26	5.7900	1.1024	0.4353	5.2411	2.2100	2.2119	0.8942	0.9991	2.7589
27	7.2143	0.7308	0.8996	7.3306	-1.2143	2.3609	-0.4913	-0.5143	-1.3306
28	5.3121	0.7560	0.2795	5.2411	0.6879	2.3529	0.2783	0.2923	0.7589
29	5.3368	1.2456	0.2876	6.1325	-2.3368	2.1346	-0.9455	-1.0948	-3.1325
30	4.0480	0.7441	-0.1325	3.9531	0.9520	2.3567	0.3852	0.4040	1.0469
31	3.2429	1.2208	-0.3949	3.9670	-2.2429	2.1488	-0.9076	-1.0438	-2.9670
32	8.7425	1.1406	1.3977	8.9434	-0.7425	2.1925	-0.3004	-0.3386	-0.9434
33	7.9863	0.9922	1.1512	7.5994	2.0137	2.2635	0.8148	0.8896	2.4006
	Studentised Press Residuals	Leverage	Cook's Distance	Mahalanobis Distance	Welsch Distance	Covratio	DfFit	Standardised DfFit	Delta-Beta Constant
1	0.2314	0.0424	0.0009	1.3577	0.3806	1.2808	0.0439	0.0648	0.0085
2	-0.1598	0.1561	0.0012	4.9944	-0.4798	1.4672	-0.0831	-0.0765	0.5291
3	-0.9497	0.1061	0.0286	3.3947	-2.2975	1.1784	-0.3451	-0.3774	0.3973
4	0.9796	0.0838	0.0247	2.6805	2.1126	1.1369	0.2936	0.3515	0.3939
5	-5.0666	0.1596	0.6399	5.1080	-15.4191	0.0524	-1.9265	-2.4533	-7.6570
6	-0.3107	0.1083	0.0032	3.4668	-0.7597	1.3678	-0.1166	-0.1246	-0.2116
7	0.6772	0.0150	0.0044	0.4795	0.8539	1.1549	0.0783	0.1475	0.2321
8	0.2036	0.0972	0.0013	3.1104	0.4712	1.3642	0.0699	0.0778	-0.2697
9	-0.1638	0.0791	0.0007	2.5298	-0.3440	1.3400	-0.0477	-0.0574	-0.3140
10	0.1600	0.1772	0.0014	5.6702	0.5203	1.5063	0.0938	0.0819	0.3111
11	0.4860	0.2142	0.0157	6.8549	1.7994	1.5200	0.3426	0.2765	1.1432
12	-0.1471	0.0964	0.0007	3.0850	-0.3391	1.3680	-0.0502	-0.0560	0.1238
13	0.1260	0.0664	0.0004	2.1250	0.2454	1.3239	0.0323	0.0412	-0.2470
14	-0.4487	0.2497	0.0161	7.9911	-1.8654	1.6052	-0.3713	-0.2798	0.4158
15	-1.2960	0.1644	0.0793	5.2621	-4.0179	1.1015	-0.6868	-0.6374	-2.7942
16	0.2539	0.1377	0.0027	4.4079	0.7077	1.4246	0.1176	0.1141	-0.3609
17	-0.4633	0.1172	0.0076	3.7510	-1.1809	1.3524	-0.1856	-0.1927	-0.1405
18	0.3837	0.1294	0.0058	4.1394	1.0322	1.3890	0.1678	0.1673	-0.1280
19	0.3056	0.1348	0.0038	4.3121	0.8413	1.4119	0.1387	0.1359	-0.0955
20	0.7560	0.0945	0.0166	3.0255	1.7267	1.2342	0.2513	0.2855	0.5343
21	-0.0313	0.0323	0.0000	1.0321	-0.0472	1.2792	-0.0051	-0.0081	-0.0391
22	0.8334	0.1639	0.0338	5.2455	2.5784	1.3110	0.4481	0.4091	1.6514
23	-0.4473	0.1305	0.0079	4.1763	-1.2091	1.3775	-0.1969	-0.1958	-1.3289
24	-0.1518	0.0553	0.0004	1.7711	-0.2749	1.3062	-0.0342	-0.0465	-0.0801
25	3.4244	0.0880	0.2274	2.8144	7.5548	0.2241	0.9062	1.2541	5.3444
26	0.9991	0.1687	0.0496	5.3975	3.1473	1.2488	0.5490	0.4979	0.1867
27	-0.5075	0.0571	0.0051	1.8283	-0.9302	1.2534	-0.1163	-0.1571	0.3402
28	0.2875	0.0633	0.0018	2.0250	0.5489	1.3032	0.0710	0.0924	0.3793
29	-1.0988	0.2237	0.0816	7.1590	-4.1995	1.2920	-0.7957	-0.6412	-0.4665
30	0.3978	0.0603	0.0033	1.9308	0.7451	1.2810	0.0949	0.1256	-0.3071
31	-1.0455	0.2137	0.0703	6.8391	-3.8648	1.3010	-0.7240	-0.5940	3.7874
32	-0.3332	0.1827	0.0062	5.8465	-1.1055	1.4931	-0.2010	-0.1734	0.8374
33	0.8862	0.1309	0.0304	4.1881	2.3994	1.2389	0.3869	0.3885	-2.2336
	Delta-Beta cm	Delta-Beta mm	Delta-Beta min	Delta-Beta ml	Standardised Delta-Beta Constant	Standardised Delta-Beta cm	Standardised Delta-Beta mm	Standardised Delta-Beta min	Standardised Delta-Beta ml
1	0.0102	-0.0067	-0.0140	-0.0023	0.0010	0.0311	-0.0055	-0.0323	-0.0032
2	-0.0104	-0.0640	0.0101	-0.0401	0.0609	-0.0317	-0.0524	0.0233	-0.0544
3	-0.0346	0.0614	-0.0088	-0.2042	0.0465	-0.1072	0.0511	-0.0205	-0.2816
4	-0.0219	0.0214	0.0860	-0.1881	0.0461	-0.0680	0.0178	0.2015	-0.2597
5	0.4429	0.3737	-0.3936	0.8961	-1.2310	1.8832	0.4272	-1.2653	1.6981
6	0.0003	0.0553	-0.0355	-0.0207	-0.0244	0.0009	0.0453	-0.0819	-0.0281
7	-0.0217	0.0027	-0.0101	0.0091	0.0269	-0.0665	0.0022	-0.0235	0.0125
8	-0.0044	0.0461	-0.0132	0.0355	-0.0311	-0.0135	0.0378	-0.0304	0.0482
9	0.0103	0.0375	0.0021	-0.0066	-0.0362	0.0313	0.0307	0.0048	-0.0090
10	0.0013	-0.0459	-0.0213	0.0060	0.0358	0.0041	-0.0376	-0.0490	0.0082
11	-0.0150	-0.1423	0.0929	-0.1482	0.1322	-0.0460	-0.1170	0.2148	-0.2019
12	-0.0079	-0.0205	0.0008	0.0214	0.0143	-0.0240	-0.0168	0.0019	0.0290
13	0.0028	0.0361	0.0017	0.0064	-0.0284	0.0084	0.0296	0.0039	0.0087
14	-0.0410	0.0472	0.0229	-0.1726	0.0480	-0.1254	0.0387	0.0530	-0.2351
15	0.0837	0.2351	0.1293	0.0940	-0.3315	0.2625	0.1983	0.3066	0.1314
16	0.0263	0.0206	0.0142	-0.0036	-0.0416	0.0801	0.0169	0.0327	-0.0049
17	-0.0084	0.0642	0.0256	-0.1076	-0.0162	-0.0255	0.0527	0.0592	-0.1465
18	-0.0211	0.0294	0.0407	0.0285	-0.0148	-0.0644	0.0242	0.0940	0.0387
19	0.0039	0.0382	-0.0462	-0.0085	-0.0110	0.0119	0.0313	-0.1065	-0.0115
20	0.0425	-0.1085	0.0099	-0.0962	0.0622	0.1308	-0.0898	0.0230	-0.1319
21	0.0019	0.0030	-0.0007	0.0016	-0.0045	0.0056	0.0025	-0.0016	0.0022
22	-0.0823	-0.1868	0.1358	-0.0392	0.1925	-0.2537	-0.1549	0.3166	-0.0538
23	0.0079	0.1819	-0.0020	0.0632	-0.1535	0.0242	0.1494	-0.0045	0.0861
24	0.0102	-0.0063	0.0035	0.0025	-0.0092	0.0309	-0.0052	0.0080	0.0033
25	-0.1797	-0.6129	-0.0951	0.1338	0.7368	-0.6552	-0.6007	-0.2622	0.2175
26	-0.0995	0.1292	0.1302	-0.1251	0.0219	-0.3086	0.1077	0.3051	-0.1729
27	0.0004	-0.0939	0.0174	0.0503	0.0393	0.0011	-0.0772	0.0403	0.0686
28	0.0094	-0.0701	-0.0004	-0.0108	0.0437	0.0286	-0.0574	-0.0009	-0.0146
29	-0.1212	0.3075	-0.0975	-0.0454	-0.0549	-0.3771	0.2572	-0.2294	-0.0629
30	-0.0100	0.0825	-0.0271	0.0047	-0.0355	-0.0307	0.0678	-0.0625	0.0063
31	-0.0041	-0.6325	0.0852	-0.1362	0.4447	-0.0128	-0.5280	0.1999	-0.1885
32	-0.0415	-0.1033	0.0283	0.0153	0.0966	-0.1266	-0.0847	0.0654	0.0209
33	0.0683	0.3268	-0.0535	-0.0351	-0.2608	0.2110	0.2713	-0.1249	-0.0484

Linear Regression

Regression Results

Valid Number of Cases: 24, 0 Omitted
Dependent Variable: AUC

	Coefficient	Standard Error	t-Statistic	Significance	Lower 95%	Upper 95%
Constant	204.0000	35.5700	5.7352	0.0000	129.2704	278.7296
Sequence = AB	68.0000	50.3036	1.3518	0.1932	-37.6837	173.6837
Treatment = A	-32.1667	41.0727	-0.7832	0.4437	-118.4570	54.1237
B	-40.5000	43.5642	-0.9297	0.3648	-132.0247	51.0247
Sequence � Treatment = AB � A	-5.3333	59.8733	-0.0891	0.9300	-131.1221	120.4554
AB � B	-74.1667	59.8733	-1.2387	0.2314	-199.9554	51.6221

Residual Sum of Squares	45548.1667
Standard Error	50.3036
Mean of Y	188.2917
Stand Dev of y	58.3956
Correlation Coefficient	0.6475
R-squared	0.4193
Adjusted R-squared	0.2579
F(5,18)	2.5990
significance of F	0.0614
Durbin-Watson Statistic	1.4941
log of likelihood	-125.0884
Press Statistic	74388.7556

DownloadUnistat 6.0
For Windows