Manuals/calci/REGRESSION

REGRESSIONANALYSIS(y,x)

• $y$ is the set of dependent variables .
• $x$ is the set of independent variables.

Description

• This function is calculating the Regression analysis of the given data.
• This analysis is very useful for the analyzation of large amounts of data and making predictions.
• This analysis give the result in three table values.

1. Regression statistics table.
2. ANOVA table.
3. Residual output.

• 1.Regression statistics :
• It contains multiple R, R Square, Adjusted R Square, Standard Error and observations.
• R square gives the fittness of the data with the regression line.
• That value is closer to 1 is the better the regression line fits the data.
• Standard Error refers to the estimated standard deviation of the error term. It is called the standard error of the regression.
• 2.ANOVA table:
• ANOVA is the analysis of variance.
• This table splits in to two components which is Residual and Regression.
• Total sum of squares= Residual (error) sum of squares+ Regression (explained) sum of squares.
• Also this table gives the probability, T stat, significance of F and P.
• When the significance of F is < 0.05, then the result for the given data is statistically significant.
• When the significance of F is > 0.05, then better to stop using this set of independent variables.
• Then remove a variable with a high P-value and returnun the regression until Significance F drops below 0.05.
• So the Significance of P value should be <0.05.
• This table containing the regression coefficient values also.
• 3.Residual output:
• The residuals show you how far away the actual data points are fom the predicted data points.

Examples

1. Temperature - Drying time(hours)

54 8 63 6 75 3 82 1

REGRESSIONANALYSIS(A1:A4,B1:B4)

Unit sales Ads population 4000 12000 300000 5200 13150 411000 6800 14090 500000 8000 11900 650000 10000 15000 800000

where  and x 

REGRESSIONANALYSIS(XRange, YRange, ConfidenceLevel, NewTableFlag)

where,

XRange - Input range should be one block.

YRange - Input range should be one block.

ConfidenceLevel - represents the confidence level for percentage nad value should in between 0 and 100.the default percentage is 95.

NewTableFlag - is the TRUE or FALSE.If set as TRUE,the result in new sheet. If NewTableFlag is omitted, it assumed to be FALSE.

---

Regression analysis is a technique used for the modeling and analysis of numerical data consisting of values of a dependent variable(response variable) and of one or more independent variables(explanatory variables).

---

Lets see an example in (Column3Row1)

?UNIQad0c61c6a857529e-nowiki-00000004-QINU?

REGRESSIONANALYSIS returns the result in new sheet(5Space).

?UNIQad0c61c6a857529e-nowiki-00000005-QINU?

RANKANDPERCENTILE returns the #ERROR(ConfidenceLevel=-5).

---

REGRESSION

---

Syntax

---

Remarks

---

Examples

---

Description

---

If ConfidenceLevel < 0 or ConfidenceLevel >100, REGRESSIONANALYSIS returns the #ERROR.

If Lengthof XRange != Lengthof YRange, it returns the #ERROR.

---

	Column1	Column2	Column3	Column4
Row1	1	3	5Space
Row2	7	8
Row3	12	10
Row4	17	18	#ERROR
Row5	37	36	File:Copy-cube.gif
Row6	6	5

File:Calci1.gif

---

Regression Analysis
SUMMARY OUTPUT

Regression	Statistics
Multiple R	0.9933541399262806
R Square	0.9867524473086807
AdjustedRSquare	0.9834405591358509
StandardError	1.6492920155683643
Observations	6

ANOVA

Source of Variation	Sum of Squares	Degree of Freedom	Mean of Squares	F	Significance F
Regression:	810.452676722863	1	810.452676722863	297.9425620115529	0.0000661044188125954
Residual:	10.880656610470271	4	2.720164152617568
Total:	821.3333333333333	5

	Coefficients	Standard Error	T Statistics	Probability	Lower 95%	Upper 95%	Lower95%	Upper95%
Intercept	-0.5146406388642397	1.0473758171565741	-0.4913619642865071	0.6489114785470887	-3.4226220244213423	2.393340746692863	-3.422622024421344	2.3933407466928646
X Variable	1.038598047914818	0.06017016854489271	17.26101277479262	0.00006610441881260698	0.8715388823047108	1.2056572135249252	0.8715388823047107	1.2056572135249252

RESIDUAL OUTPUT

Observation	Predicted Y	Residuals	Standard Residuals
1	2.601153504880214	-1.6011535048802141	-1.0854015072917171
2	7.7941437444543045	-0.7941437444543045	-0.5383398997096632
3	9.871339840283941	2.128660159716059	1.4429915300597537
4	18.180124223602483	-1.1801242236024833	-0.7999911358813972
5	36.8748890860692	0.1251109139307971	0.08481109034532615
6	4.678349600709851	1.3216503992901493	0.8959299224777

---