Perform Linear and Logarithmic Regressions in a Data Set with Multiple "x variables"

[artuzelaya]

Hello guys. As you can see from the title, I need to perform both linear and logarithmic regressions in a a data sets that contains multiple x variables. The data set contains 18 columns (17 columns with the x values and 1 column with the y value). I need to perform a normal linear and logarithmic regression (which shows the effect of "x" on y to vary) and one that does not.

Additional information: As you can see, there are 18 columns in the mini spreadsheet. The y-variable is the in column A, whereas the other columns represent the x-variables

Sample Residential Water Demand Data.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	w	wbill	month	year	date	uid	br	bone	btwo	unemrate	totprecip	avemxtemp	restrict	pone	ptwo	pthree	fc	bpdays
2	1180	24.07999992	8	2001	15214	1	0	10000	10000	4.400000095	0.519999981	85.05999756	0	0.016729999	0.016729999	0.016729999	0.140000001	30.99999809
3	2210	41.16999817	9	2001	15244	1	0	10000	10000	4.599999905	1.960000038	78.47000122	0	0.016729999	0.016729999	0.016729999	0.140000001	30.00000191
4	590	13.93000031	10	2001	15273	1	0	10000	10000	5.199999809	0.02	67.62000275	0	0.016729999	0.016729999	0.016729999	0.140000001	28.99999809
5	220	8.43999958	11	2001	15307	1	0	10000	10000	5.900000095	0.370000005	57.11999893	0	0.016729999	0.016729999	0.016729999	0.140000001	34
6	170	7.039999962	12	2001	15337	1	0	10000	10000	6.300000191	0.079999998	46.70000076	0	0.016729999	0.016729999	0.016729999	0.140000001	30.00000191
7	270	9.140000343	1	2002	15370	1	0	10000	10000	7.199999809	0.25	41.75999832	0	0.016729999	0.016729999	0.016729999	0.140000001	33.00000381
8	200	7.550000191	3	2002	15400	1	0	10000	10000	6.699999809	0.119999997	45.63000107	0	0.016729999	0.016729999	0.016729999	0.140000001	30.00000191
9	230	8.18999958	4	2002	15431	1	0	10000	10000	6.199999809	0.289999992	53	0	0.016729999	0.016729999	0.016729999	0.140000001	30.99999809
10	860	18.45000076	4	2002	15460	1	0	10000	10000	6.199999809	0.02	65.33999634	0	0.016729999	0.016729999	0.016729999	0.140000001	28.99999809
11	1810	34.34000015	5	2002	15489	1	0	10000	10000	5.599999905	1.120000005	70.33999634	0	0.016729999	0.016729999	0.016729999	0.140000001	28.99999809
12	1290	25.63999939	6	2002	15518	1	0	10000	10000	6.400000095	1.169999957	85.41000366	1	0.016729999	0.016729999	0.016729999	0.140000001	28.99999809
13	2180	42.11000061	7	2002	15550	1	1	999	2999	6.099999905	1.620000005	88.55999756	1	0.015199997	0.019099999	0.022699999	0.140000001	32
14	1840	35.29999924	8	2002	15579	1	1	999	2999	5.699999809	0.430000007	85.93000031	1	0.015199997	0.019099999	0.022699999	0.140000001	28.99999809
15	1060	20.54999924	9	2002	15609	1	1	999	2999	5.599999905	1.25	76.87000275	1	0.015199997	0.019099999	0.022699999	0.140000001	30.00000191
16	510	11.81000042	10	2002	15638	1	0	10000	10000	5.599999905	0.50999999	61.83000183	1	0.015199997	0.015199997	0.015199997	0.140000001	28.99999809
17	200	7.519999981	11	2002	15670	1	0	10000	10000	5.900000095	0.970000029	47.88000107	1	0.015199997	0.015199997	0.015199997	0.140000001	32
18	260	8.710000038	12	2002	15704	1	0	10000	10000	6.099999905	0.109999999	44.56000137	1	0.015199997	0.015199997	0.015199997	0.140000001	34
19	200	7.380000114	1	2003	15735	1	0	10000	10000	6.699999809	0.029999999	48.52000046	1	0.015199997	0.015199997	0.015199997	0.140000001	30.99999809
20	220	8.81000042	3	2003	15767	1	0	10000	10000	6.800000191	0.810000002	38.22000122	1	0.015499998	0.015499998	0.015499998	0.170000002	32
21	230	8.5	4	2003	15796	1	0	10000	10000	6.300000191	0.839999974	55.31000137	1	0.015499998	0.015499998	0.015499998	0.170000002	28.99999809
22	290	9.430000305	4	2003	15825	1	0	10000	10000	6.300000191	0.970000029	62.52000046	1	0.015499998	0.015499998	0.015499998	0.170000002	28.99999809
23	1760	36.93999863	5	2003	15855	1	1	999	2999	6	0.870000005	70.52999878	1	0.015499998	0.021499999	0.024999999	0.170000002	30.00000191
24	1830	38.61999893	6	2003	15886	1	1	999	2999	6.900000095	5.099999905	73.55000305	1	0.015499998	0.021499999	0.024999999	0.170000002	30.99999809
25	1500	31.35000038	7	2003	15916	1	1	999	2999	6.599999905	1.139999986	91.76999664	1	0.015499998	0.021499999	0.024999999	0.170000002	30.00000191
26	1120	23.01000023	8	2003	15945	1	1	999	2999	6.199999809	0.899999976	87.27999878	1	0.015499998	0.021499999	0.024999999	0.170000002	28.99999809
27	900	19.04999924	9	2003	15975	1	1	999	2999	6	1.570000052	72.83000183	1	0.015499998	0.021499999	0.024999999	0.170000002	30.00000191
28	870	18.59000015	10	2003	16005	1	0	10000	10000	5.800000191	0.07	70.76999664	1	0.015499998	0.015499998	0.015499998	0.170000002	30.00000191
Sheet1

 

[kweaver]

Welcome to Mr. Excel.

You be able to do the linear regression, one by one, using the Analysis ToolPak (an addin).
If you create additional columns with the natural log of the respective x-values, you should then be able to do the log regression.

This is what I got for the linear regression for the first x-column (col B)

MISC1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I
1	SUMMARY OUTPUT
2
3	Regression Statistics
4	Multiple R	0.9964597
5	R Square	0.992932
6	Adjusted R Square	0.9926492
7	Standard Error	59.65335
8	Observations	27
9
10	ANOVA
11		df	SS	MS	F	Significance F
12	Regression	1	12497703.61	12497704	3512.049	2.0715E-28
13	Residual	25	88963.05452	3558.522
14	Total	26	12586666.67
15
16		Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
17	Intercept	-224.04803	22.01083582	-10.179	2.24E-10	-269.380197	-178.715867	-269.380197	-178.715867
18	X Variable 1	57.165979	0.964622475	59.26254	2.07E-28	55.1793022	59.1526566	55.1793022	59.1526566
19
Sheet4