Difference between revisions of "Manuals/calci/REGRESSION"

From ZCubes Wiki
Jump to navigation Jump to search
Line 32: Line 32:
 
==Examples==
 
==Examples==
 
#Temperature  -    Drying time(hours)
 
#Temperature  -    Drying time(hours)
−
54                               8
+
54         -          8
−
63                               6
+
63         -          6
−
75                               3
+
75         -          3
−
82                               1
+
82         -          1
 
=REGRESSIONANALYSIS(A1:A4,B1:B4)=
 
=REGRESSIONANALYSIS(A1:A4,B1:B4)=
−
Unit sales    Ads      population
+
Unit sales -  Ads  -     population
−
4000           12000    300000
+
4000   -      12000 -     300000
−
5200           13150    411000
+
5200   -      13150 -     411000
−
6800           14090    500000
+
6800   -      14090 -     500000
−
8000           11900     650000
+
8000   -      11900 -    650000
−
10000         15000     800000
+
10000 -      15000 -    800000
 +
=REGRESSIONANALYSIS(B1:B5,C1:D5)=
  
  
−
where  and x
+
==See Also==
−
'''REGRESSIONANALYSIS'''(XRange, YRange, ConfidenceLevel, NewTableFlag)
 
  
−
where,
 
  
−
'''XRange '''- Input range should be one block.
+
==References==
−
 
 
−
'''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.
 
−
 
 
−
</div>
 
−
----
 
−
<div id="1SpaceContent" class="zcontent" align="left">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).</div>
 
−
----
 
−
<div id="7SpaceContent" class="zcontent" align="left">
 
−
 
 
−
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).
 
−
 
 
−
</div>
 
−
----
 
−
<div id="12SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="left">
 
−
 
 
−
REGRESSION
 
−
 
 
−
</div></div>
 
−
----
 
−
<div id="10SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="justify">Syntax </div><div class="ZEditBox"><center></center></div></div>
 
−
----
 
−
<div id="4SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="justify">Remarks </div></div>
 
−
----
 
−
<div id="3SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="justify">Examples </div></div>
 
−
----
 
−
<div id="11SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="justify">Description </div></div>
 
−
----
 
−
<div id="8SpaceContent" class="zcontent" align="left">
 
−
 
 
−
If ConfidenceLevel &lt; 0 or ConfidenceLevel &gt;100, REGRESSIONANALYSIS returns the #ERROR.
 
−
 
 
−
If Lengthof XRange != Lengthof YRange, it returns the #ERROR.
 
−
 
 
−
</div>
 
−
----
 
−
<div id="2SpaceContent" class="zcontent" align="left">
 
−
 
 
−
{| id="TABLE3" class="SpreadSheet blue"
 
−
|- class="even"
 
−
| class=" " |
 
−
| Column1
 
−
| Column2
 
−
| class="  " | Column3
 
−
| Column4
 
−
|- class="odd"
 
−
| class=" " | Row1
 
−
| class=" " | 1
 
−
| class=" " | 3
 
−
| class="sshl_f" | 5Space
 
−
| class=" " |
 
−
|- class="even"
 
−
| class="  " | Row2
 
−
| class=" " | 7
 
−
| class=" " | 8
 
−
| class="    " |
 
−
| class=" " |
 
−
|- class="odd"
 
−
| Row3
 
−
| class=" " | 12
 
−
| class=" " | 10
 
−
| class=" " |
 
−
| class=" " |
 
−
|- class="even"
 
−
| Row4
 
−
| class=" " | 17
 
−
| class=" " | 18
 
−
| class="sshl_f" | #ERROR
 
−
| class=" " |
 
−
|- class="odd"
 
−
| class=" " | Row5
 
−
| class=" " | 37
 
−
| class=" " | 36
 
−
| class="  SelectTD1 ChangeBGColor SelectTD1" |
 
−
<div id="2Space_Handle" class="zhandles" title="Click and Drag to resize CALCI Column/Row/Cell. It is EZ!"></div><div id="2Space_Copy" class="zhandles" title="Click and Drag over to AutoFill other cells."></div><div id="2Space_Drag" class="zhandles" title="Click and Drag to Move/Copy Area.">[[Image:copy-cube.gif]]  </div>
 
−
| class=" " |
 
−
|- class="even"
 
−
| Row6
 
−
| class=" " | 6
 
−
| class=" " | 5
 
−
| class=" " |
 
−
| class=" " |
 
−
|}
 
−
 
 
−
<div align="left">[[Image:calci1.gif]]</div></div>
 
−
----
 
−
<div id="5SpaceContent" class="zcontent" align="left">
 
−
 
 
−
{| class="SpreadSheet blue"
 
−
|+ Regression Analysis<br />SUMMARY OUTPUT
 
−
|- class="even"
 
−
! Regression
 
−
! Statistics
 
−
|- class="odd"
 
−
| Multiple R
 
−
| 0.9933541399262806
 
−
|- class="even"
 
−
| R Square
 
−
| 0.9867524473086807
 
−
|- class="odd"
 
−
| AdjustedRSquare
 
−
| 0.9834405591358509
 
−
|- class="even"
 
−
| StandardError
 
−
| 1.6492920155683643
 
−
|- class="odd"
 
−
| Observations
 
−
| 6
 
−
|}
 
−
 
 
−
{| class="SpreadSheet blue"
 
−
|+ <br />ANOVA
 
−
|- class="even"
 
−
! Source of Variation
 
−
! Sum of Squares
 
−
! Degree of Freedom
 
−
! Mean of Squares
 
−
! F
 
−
! Significance F
 
−
|- class="odd"
 
−
| Regression:
 
−
| 810.452676722863
 
−
| 1
 
−
| 810.452676722863
 
−
| 297.9425620115529
 
−
| 0.0000661044188125954
 
−
|- class="even"
 
−
| Residual:
 
−
| 10.880656610470271
 
−
| 4
 
−
| 2.720164152617568
 
−
|- class="odd"
 
−
| Total:
 
−
| 821.3333333333333
 
−
| 5
 
−
|}
 
−
 
 
−
{| class="SpreadSheet blue"
 
−
|+ <br />
 
−
|- class="even"
 
−
!
 
−
! Coefficients
 
−
! Standard Error
 
−
! T Statistics
 
−
! Probability
 
−
! Lower 95%
 
−
! Upper 95%
 
−
! Lower95%
 
−
! Upper95%
 
−
|- class="odd"
 
−
| Intercept
 
−
| -0.5146406388642397
 
−
| 1.0473758171565741
 
−
| -0.4913619642865071
 
−
| 0.6489114785470887
 
−
| -3.4226220244213423
 
−
| 2.393340746692863
 
−
| -3.422622024421344
 
−
| 2.3933407466928646
 
−
|- class="even"
 
−
| X Variable
 
−
| 1.038598047914818
 
−
| 0.06017016854489271
 
−
| 17.26101277479262
 
−
| 0.00006610441881260698
 
−
| 0.8715388823047108
 
−
| 1.2056572135249252
 
−
| 0.8715388823047107
 
−
| 1.2056572135249252
 
−
|}
 
−
 
 
−
{| class="SpreadSheet blue"
 
−
|+ <br />RESIDUAL OUTPUT
 
−
|- class="even"
 
−
! Observation
 
−
! Predicted Y
 
−
! Residuals
 
−
! Standard Residuals
 
−
|- class="odd"
 
−
| 1
 
−
| 2.601153504880214
 
−
| -1.6011535048802141
 
−
| -1.0854015072917171
 
−
|- class="even"
 
−
| 2
 
−
| 7.7941437444543045
 
−
| -0.7941437444543045
 
−
| -0.5383398997096632
 
−
|- class="odd"
 
−
| 3
 
−
| 9.871339840283941
 
−
| 2.128660159716059
 
−
| 1.4429915300597537
 
−
|- class="even"
 
−
| 4
 
−
| 18.180124223602483
 
−
| -1.1801242236024833
 
−
| -0.7999911358813972
 
−
|- class="odd"
 
−
| 5
 
−
| 36.8748890860692
 
−
| 0.1251109139307971
 
−
| 0.08481109034532615
 
−
|- class="even"
 
−
| 6
 
−
| 4.678349600709851
 
−
| 1.3216503992901493
 
−
| 0.8959299224777
 
−
|}
 
−
 
 
−
</div>
 
−
----
 

Revision as of 09:47, 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

REGRESSIONANALYSIS(B1:B5,C1:D5)

See Also

References

Retrieved from "http://wiki.zcubes.com/index.php?title=Manuals/calci/REGRESSION&oldid=197302"