Difference between revisions of "Manuals/calci/REGRESSION"

From ZCubes Wiki
Jump to navigation Jump to search
(Created page with "<div id="6SpaceContent" class="zcontent" align="left"> '''REGRESSIONANALYSIS'''(XRange, YRange, ConfidenceLevel, NewTableFlag) where, '''XRange '''- Input range sho...")
 
Line 1: Line 1:
<div id="6SpaceContent" class="zcontent" align="left">
+
<div style="font-size:30px">'''REGRESSIONANALYSIS(y,x)'''</div><br/>
 +
*<math>y </math> is the set of dependent variables .
 +
*<math>x </math> 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.
 +
# Regression statistics table.
 +
# ANOVA table.
 +
# 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==
 +
#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)
 
'''REGRESSIONANALYSIS'''(XRange, YRange, ConfidenceLevel, NewTableFlag)
  
Line 21: Line 66:
 
Lets see an example in (Column3Row1)
 
Lets see an example in (Column3Row1)
  
<nowiki>=REGRESSIONANALYSIS(R1C1:R6C1, R1C2:R6C2, 95, TRUE)</nowiki>
+
UNIQad0c61c6a857529e-nowiki-00000004-QINU
  
 
REGRESSIONANALYSIS returns the result in new sheet(5Space).
 
REGRESSIONANALYSIS returns the result in new sheet(5Space).
  
<nowiki>=REGRESSIONANALYSIS(R1C1:R6C1, R1C2:R6C2, -5, TRUE)</nowiki>
+
UNIQad0c61c6a857529e-nowiki-00000005-QINU
  
 
RANKANDPERCENTILE returns the #ERROR(ConfidenceLevel=-5).
 
RANKANDPERCENTILE returns the #ERROR(ConfidenceLevel=-5).

Revision as of 10:38, 20 January 2014

REGRESSIONANALYSIS(y,x)


  • is the set of dependent variables .
  • 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
Row6 6 5

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