Difference between revisions of "Manuals/calci/REGRESSION"

From ZCubes Wiki
Jump to navigation Jump to search
Line 31: Line 31:
  
 
==Examples==
 
==Examples==
{| border="1"
+
1.
{| class="wikitable"
+
{| class="wikitable"
|+Spreadsheet
+
|+Spreadsheet
|-
+
|-
! !! A !! B  
+
! !! A !! B  
|-
+
|-
! 1
+
! 1
| '''Temperature''' || '''Drying Time(Hrs)'''  
+
| '''Temperature''' || '''Drying Time(Hrs)'''  
|-
+
|-
! 2
+
! 2
| 54 || 8  
+
| 54 || 8  
|-
+
|-
! 3
+
! 3
| 63  || 6  
+
| 63  || 6  
|-
+
|-
! 4
+
! 4
| 75 || 3   
+
| 75 || 3   
|-
+
|-
! 5
+
! 5
| 82 || 1  
+
| 82 || 1  
|}
+
|}
  
 
  =REGRESSIONANALYSIS(A2:A5,B2:B5)
 
  =REGRESSIONANALYSIS(A2:A5,B2:B5)
Line 109: Line 109:
 
| 4  || 82.5  || -0.5  || -0.8660254037844387   
 
| 4  || 82.5  || -0.5  || -0.8660254037844387   
 
|}
 
|}
|}
+
 
  
 
Unit sales  -  Ads  -    population
 
Unit sales  -  Ads  -    population

Revision as of 03:44, 23 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.

Spreadsheet
A B
1 Temperature Drying Time(Hrs)
2 54 8
3 63 6
4 75 3
5 82 1
=REGRESSIONANALYSIS(A2:A5,B2:B5)
Summary Output
Regression Statistics
Multiple R -0.9989241524588298
R Square 0.9978494623655915
v14193 0.9967741935483871
v15308 0.7071067811865362
ANOVA
Source of Variation Sum Of Squares Degree Of Freedom Mean Of Squares F Significance F
Regression: 464 1 464 928 0.0010758475411702228
Residual: 1 2 0.5
Total: 465 3
ANOVA
Coefficients Standard Error T Statistics Probability Lower 95% Upper 95%
Intercept: 86.5 0.6885767430246738 125.62143708199632 0.00006336233990811291 83.53729339698289 89.46270660301711
X Variable -4 0.13130643285972046 -30.463092423456118 0.0010758475411701829 -4.564965981777541 -3.435034018222459
Residual Output
Observation Predicted Y Residuals Standard Residuals
1 54.5 -0.5 -0.8660254037844387
2 62.5 0.5 0.8660254037844387
3 74.5 0.5 0.8660254037844387
4 82.5 -0.5 -0.8660254037844387


Unit sales - Ads - population 4000 - 12000 - 300000 5200 - 13150 - 411000 6800 - 14090 - 500000 8000 - 11900 - 650000 10000 - 15000 - 800000 REGRESSIONANALYSIS(B1:B5,C1:D5)=

See Also

References