Difference between revisions of "Manuals/calci/DESCRIPTIVESTATISTICS"

From ZCubes Wiki
Jump to navigation Jump to search
(Created page with "<div id="6SpaceContent" class="zcontent" align="left"> '''DESCRIPTIVESTATISTICS'''(Array, GroupBy, ConfidenceLevel, KthLargest, KthSmallest, NewTableFlag) where, ''...")
 
Line 1: Line 1:
−
<div id="6SpaceContent" class="zcontent" align="left">
+
=DESCRIPTIVESTATISTICS(Array, GroupBy, ConfidenceLevel, KthLargest, KthSmallest, NewTableFlag)=
−
 
 
−
'''DESCRIPTIVESTATISTICS'''(Array, GroupBy, ConfidenceLevel, KthLargest, KthSmallest, NewTableFlag)
 
  
 
where,
 
where,
 +
*<math>Array</math> is array of integers or reference to the cells containing array,
 +
*<math>GroupBy</math> is a method that performs the analysis by Columns or Rows,
 +
*<math>ConfidenceLevel</math> represents the confidence level for mean of data points and is between 0 to 100,
 +
*<math>KthLargest</math> represents the Kth largest value for each range of data,
 +
*<math>KthSmallest</math> represents the Kth smallest value for each range of data,
 +
*<math>NewTableFlag</math> is a logical value that determines how the output should be displayed.
  
−
'''Array '''- Input range should be one or more blocks.
+
DESCRIPTIVESTATISTICS() calculates a range of statistical measurements and summarizes them into a table.
  
−
'''GroupBy''' - Either groupby rows or columns.
+
== Description ==
  
−
'''ConfidenceLevel -''' represents the confidence level for mean and value should be in between 0 and 100.
+
DESCRIPTIVESTATISTICS(Array, GroupBy, ConfidenceLevel, KthLargest, KthSmallest, NewTableFlag)
  
−
'''KthLargest -''' represents the kth largest value for each range of data.
+
*<math>Array</math> can be numbers or can be names, arrays or references containing numbers.
 +
*Values containing text, logical values or empty cells are ignored.
 +
*Argument <math>GroupBy</math> can be "Rows" or "Columns".
 +
*If <math>ConfidenceLevel</math> &lt; '0' or <math>ConfidenceLevel</math> &gt; '100', Calci displays an error message.
 +
*If <math>KthSmallest</math> &lt; '0' or <math>KthLargest</math> &gt; 'Length of array', Calci displays an error message.
 +
*<math>NewTableFlag</math> can be a logical value TRUE or FALSE. If omitted, Calci assumes it to be TRUE.
 +
*If <math>NewTableFlag</math> is TRUE, output is displayed on a new ZSpace cube. If <math>NewTableFlag</math> is FALSE, output is displayed on the same spreadsheet where command is written.
 +
*If data is invalid, Calci displays NaN error message.
  
−
'''KthSmallest''' - represents the kth smallest value for each range of data.
 
  
−
'''NewTableFlag''' - is the TRUE or FALSE.If set as TRUE,the result in new sheet.If NewTableFlag is omitted, it assumed to be FALSE.
+
== Examples ==
  
−
</div>
+
Consider the following table with two arrays as input to DESCRIPTIVESTATISTICS() function -
−
----
 
−
<div id="1SpaceContent" class="zcontent" align="left">Calculates a range of statistical measurements and summarises them into a table. This table includes calculations for: Mean, Standard Error, Median, Mode, Standard Deviation, Sample Variance, Kurtosos, Skewness, Range, Minimum, Maximum, Sum, Count, Largest, Smallest and Confidence Level.</div>
 
−
----
 
−
<div id="7SpaceContent" class="zcontent" align="left">
 
  
−
If ConfidenceLevel &gt;100 or ConfidenceLevel &lt; 0, returns the #ERROR.
+
<div id="5SpaceContent" class="zcontent" align="left">
−
 
 
−
DESCRIPTIVESTATISTICS returns the #ERROR,
 
−
 
 
−
if KthLargest &gt; Length of Array or KthSmallest &lt; 0.
 
−
 
 
−
</div>
 
−
----
 
−
<div id="12SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="left">
 
−
 
 
−
DESCRIPTIVE STATISTICS
 
−
 
 
−
</div></div>
 
−
----
 
−
<div id="8SpaceContent" class="zcontent" align="left">
 
−
 
 
−
Lets see an example in (Column3, Row1)
 
−
 
 
−
<nowiki>=DESCRIPTIVESTATISTICS(R1C1:R4C2, "Columns", 95, 1, 1, TRUE)</nowiki>
 
−
 
 
−
It returns the result in new sheet(5Sapce).
 
−
 
 
−
<nowiki>=DESCRIPTIVESTATISTICS(R1C1:R3C2,"Rows", 95, 2, 1, TRUE)</nowiki>
 
−
 
 
−
It returns the result in new sheet(9space).
 
−
 
 
−
</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="2SpaceContent" class="zcontent" align="left">
 
  
 
{| id="TABLE3" class="SpreadSheet blue"
 
{| id="TABLE3" class="SpreadSheet blue"
Line 65: Line 35:
 
| class=" " |
 
| class=" " |
 
| Column1
 
| Column1
−
| class="  " | Column2
+
| Column2
−
| class="  " | Column3
+
| Column3
 
| Column4
 
| Column4
 +
 
|- class="odd"
 
|- class="odd"
 
| class=" " | Row1
 
| class=" " | Row1
 
| class=" " | 8
 
| class=" " | 8
−
| class="sshl_f" | 8
+
| class=" " | 8
−
| class="sshl_f" | 5Space
+
| class="sshl_f" |  
−
| class="sshl_f" | 5
+
| class="sshl_f" |  
 +
 
 
|- class="even"
 
|- class="even"
 
| class="  " | Row2
 
| class="  " | Row2
 
| class=" " | 7
 
| class=" " | 7
−
| class="   " | 8
+
| class=" " | 8
−
| class="  " | 9
+
| class="  " |  
−
| class="sshl_f" | 128
+
| class="sshl_f" |  
 +
 
 
|- class="odd"
 
|- class="odd"
−
| Row3
+
| class=" " | Row3
−
| class="sshl_f" | 11
+
| class=" " | 11
−
| class="sshl_f" | 11
+
| class=" " | 11
−
| 14
+
| class=" " |
−
| class="sshl_f   " | 15
+
| class="sshl_f" |  
 +
 
 
|- class="even"
 
|- class="even"
−
| Row4
+
| class=" " | Row4
−
| class="sshl_f" | 7
+
| class=" " | 7
−
| class="       SelectTD1 ChangeBGColor SelectTD1" |
+
| class=" " | 18
−
<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>18
+
| class="sshl_f" |  
−
| class="sshl_f" | 10000
+
| class="  " |  
−
| class="  " | 20
+
 
 
|- class="odd"
 
|- class="odd"
 
| class=" " | Row5
 
| class=" " | Row5
−
| class="   " | 33
+
| class=" " | 33
−
| 37
+
| class=" " | 37
−
| 10023
+
| class="sshl_f" |
−
| 168
+
| class="  " |
 +
 
|- class="even"
 
|- class="even"
−
| Row6
+
| class=" " | Row6
−
| class="sshl_f" | 9Space
+
| class=" " |  
−
| class="sshl_f" | 2
+
| class=" " |
−
| 1.619775
+
| class="sshl_f" |  
−
| 0.525322
+
| class="  " |  
 
|}
 
|}
  
−
<div align="left">[[Image:calci1.gif]]</div></div>
+
</div>
 +
 
 +
=DESCRIPTIVESTATISTICS(A2:B5,"Columns",95,1,1,TRUE)
 +
=DESCRIPTIVESTATISTICS(A2:B6,"Rows",95,2,1,TRUE)
 +
 
 
----
 
----
 +
 +
DESCRIPTIVESTATISTICS() displays the respective output for above examples -
 +
 
<div id="5SpaceContent" class="zcontent" align="left">
 
<div id="5SpaceContent" class="zcontent" align="left">
  
Line 119: Line 101:
 
| Mean
 
| Mean
 
| 8.25
 
| 8.25
−
|- class="even"
 
−
| Standard Deviation
 
−
| 1.8929694486000912
 
−
|- class="odd"
 
−
| Count
 
−
| 4
 
 
|- class="even"
 
|- class="even"
 
| Standard Error
 
| Standard Error
Line 135: Line 111:
 
| 7
 
| 7
 
|- class="odd"
 
|- class="odd"
 +
| Standard Deviation
 +
| 1.8929694486000912
 +
|- class="even"
 
| Sample Variance
 
| Sample Variance
 
| 3.5833333333333335
 
| 3.5833333333333335
 +
|- class="odd"
 +
| Kurtosis
 +
| 2.6154678204434845
 
|- class="even"
 
|- class="even"
−
| Kurtosis
 
−
| 2.615467820443488
 
−
|- class="odd"
 
 
| Skewness
 
| Skewness
 
| 1.6585238002878033
 
| 1.6585238002878033
 +
|- class="odd"
 +
| Range
 +
| 4
 
|- class="even"
 
|- class="even"
 
| Minimum
 
| Minimum
Line 150: Line 132:
 
| 11
 
| 11
 
|- class="even"
 
|- class="even"
−
| Range
 
−
| 4
 
−
|- class="odd"
 
 
| Sum
 
| Sum
 
| 33
 
| 33
 +
|- class="odd"
 +
| Count
 +
| 4
 
|- class="even"
 
|- class="even"
 
| Confidence Level 95%
 
| Confidence Level 95%
−
| 1.5060697372158713
+
| 3.0121394744317427
 
|- class="odd"
 
|- class="odd"
 
| Largest(1)
 
| Largest(1)
Line 174: Line 156:
 
| Mean
 
| Mean
 
| 11.25
 
| 11.25
−
|- class="even"
 
−
| Standard Deviation
 
−
| 4.716990566028302
 
−
|- class="odd"
 
−
| Count
 
−
| 4
 
 
|- class="even"
 
|- class="even"
 
| Standard Error
 
| Standard Error
Line 190: Line 166:
 
| 8
 
| 8
 
|- class="odd"
 
|- class="odd"
 +
| Standard Deviation
 +
| 4.716990566028302
 +
|- class="even"
 
| Sample Variance
 
| Sample Variance
 
| 22.249999999999996
 
| 22.249999999999996
−
|- class="even"
+
|- class="odd"
 
| Kurtosis
 
| Kurtosis
 
| 1.9800530236081304
 
| 1.9800530236081304
 +
|- class="even"
 +
| Skewness
 +
| 1.517345279919216
 
|- class="odd"
 
|- class="odd"
−
| Skewness
+
| Range
−
| 1.5173452799192164
+
| 10
 
|- class="even"
 
|- class="even"
 
| Minimum
 
| Minimum
Line 205: Line 187:
 
| 18
 
| 18
 
|- class="even"
 
|- class="even"
−
| Range
 
−
| 10
 
−
|- class="odd"
 
 
| Sum
 
| Sum
 
| 45
 
| 45
 +
|- class="odd"
 +
| Count
 +
| 4
 
|- class="even"
 
|- class="even"
 
| Confidence Level 95%
 
| Confidence Level 95%
−
| 3.752895614602602
+
| 7.505791229205204
 
|- class="odd"
 
|- class="odd"
 
| Largest(1)
 
| Largest(1)
Line 233: Line 215:
 
| Mean
 
| Mean
 
| 8
 
| 8
−
|- class="even"
 
−
| Standard Deviation
 
−
| 0
 
−
|- class="odd"
 
−
| Count
 
−
| 2
 
 
|- class="even"
 
|- class="even"
 
| Standard Error
 
| Standard Error
Line 248: Line 224:
 
| Mode
 
| Mode
 
| 8
 
| 8
 +
| Standard Deviation
 +
| 0
 
|- class="odd"
 
|- class="odd"
 
| Sample Variance
 
| Sample Variance
Line 258: Line 236:
 
| NaN
 
| NaN
 
|- class="even"
 
|- class="even"
 +
| Range
 +
| 0
 +
|- class="odd"
 
| Minimum
 
| Minimum
 
| 8
 
| 8
−
|- class="odd"
+
|- class="even"
 
| Maximum
 
| Maximum
 
| 8
 
| 8
−
|- class="even"
 
−
| Range
 
−
| 0
 
 
|- class="odd"
 
|- class="odd"
 
| Sum
 
| Sum
 
| 16
 
| 16
 
|- class="even"
 
|- class="even"
 +
| Count
 +
| 2
 +
|- class="odd"
 
| Confidence Level 95%
 
| Confidence Level 95%
 
| 0
 
| 0
−
|- class="odd"
+
|- class="even"
 
| Largest(2)
 
| Largest(2)
 
| 8
 
| 8
Line 288: Line 269:
 
| Mean
 
| Mean
 
| 7.5
 
| 7.5
−
|- class="even"
 
−
| Standard Deviation
 
−
| 0.7071067811865476
 
−
|- class="odd"
 
−
| Count
 
−
| 2
 
 
|- class="even"
 
|- class="even"
 
| Standard Error
 
| Standard Error
Line 303: Line 278:
 
| Mode
 
| Mode
 
| #ERROR
 
| #ERROR
 +
| Standard Deviation
 +
| 0.7071067811865476
 
|- class="odd"
 
|- class="odd"
 
| Sample Variance
 
| Sample Variance
Line 313: Line 290:
 
| NaN
 
| NaN
 
|- class="even"
 
|- class="even"
 +
| Range
 +
| 1
 +
|- class="odd"
 
| Minimum
 
| Minimum
 
| 7
 
| 7
−
|- class="odd"
+
|- class="even"
 
| Maximum
 
| Maximum
 
| 8
 
| 8
−
|- class="even"
 
−
| Range
 
−
| 1
 
 
|- class="odd"
 
|- class="odd"
 
| Sum
 
| Sum
 
| 15
 
| 15
 
|- class="even"
 
|- class="even"
 +
| Count
 +
| 2
 +
|- class="odd"
 
| Confidence Level 95%
 
| Confidence Level 95%
−
| 4.492321766046884
+
| 6.353102368087358
−
|- class="odd"
+
|- class="even"
 
| Largest(2)
 
| Largest(2)
 
| 7
 
| 7
Line 343: Line 323:
 
| Mean
 
| Mean
 
| 11
 
| 11
−
|- class="even"
 
−
| Standard Deviation
 
−
| 0
 
−
|- class="odd"
 
−
| Count
 
−
| 2
 
 
|- class="even"
 
|- class="even"
 
| Standard Error
 
| Standard Error
Line 358: Line 332:
 
| Mode
 
| Mode
 
| 11
 
| 11
 +
| Standard Deviation
 +
| 0
 
|- class="odd"
 
|- class="odd"
 
| Sample Variance
 
| Sample Variance
Line 368: Line 344:
 
| NaN
 
| NaN
 
|- class="even"
 
|- class="even"
 +
| Range
 +
| 0
 +
|- class="odd"
 
| Minimum
 
| Minimum
 
| 11
 
| 11
−
|- class="odd"
+
|- class="even"
 
| Maximum
 
| Maximum
 
| 11
 
| 11
−
|- class="even"
 
−
| Range
 
−
| 0
 
 
|- class="odd"
 
|- class="odd"
 
| Sum
 
| Sum
 
| 22
 
| 22
 
|- class="even"
 
|- class="even"
 +
| Count
 +
| 2
 +
|- class="odd"
 
| Confidence Level 95%
 
| Confidence Level 95%
 
| 0
 
| 0
−
|- class="odd"
+
|- class="even"
 
| Largest(2)
 
| Largest(2)
 
| 11
 
| 11
Line 388: Line 367:
 
| Smallest(1)
 
| Smallest(1)
 
| 11
 
| 11
 +
|}
 +
 +
{| class="SpreadSheet blue"
 +
|+ <br />Descriptive Statistics
 +
|- class="even"
 +
! Row 4
 +
!
 +
|- class="odd"
 +
| Mean
 +
| 12.5
 +
|- class="even"
 +
| Standard Error
 +
| 5.499999999999999
 +
|- class="odd"
 +
| Median
 +
| 12.5
 +
|- class="even"
 +
| Mode
 +
| #ERROR
 +
| Standard Deviation
 +
| 7.7781745930520225
 +
|- class="odd"
 +
| Sample Variance
 +
| 60.49999999999999
 +
|- class="even"
 +
| Kurtosis
 +
| NaN
 +
|- class="odd"
 +
| Skewness
 +
| NaN
 +
|- class="even"
 +
| Range
 +
| 11
 +
|- class="odd"
 +
| Minimum
 +
| 7
 +
|- class="even"
 +
| Maximum
 +
| 18
 +
|- class="odd"
 +
| Sum
 +
| 25
 +
|- class="even"
 +
| Count
 +
| 2
 +
|- class="odd"
 +
| Confidence Level 95%
 +
| 69.88412604896092
 +
|- class="even"
 +
| Largest(2)
 +
| 7
 +
|- class="even"
 +
| Smallest(1)
 +
| 7
 
|}
 
|}
  
 
</div>
 
</div>
−
----
+
== See Also ==
 +
 
 +
*[[Manuals/calci/COVAR | COVAR]]
 +
*[[Manuals/calci/COVARIANCE | COVARIANCE]]
 +
 
 +
== References ==
 +
 
 +
*[http://en.wikipedia.org/wiki/Descriptive_statistics]

Revision as of 18:36, 21 January 2014

DESCRIPTIVESTATISTICS(Array, GroupBy, ConfidenceLevel, KthLargest, KthSmallest, NewTableFlag)

where,

  • is array of integers or reference to the cells containing array,
  • is a method that performs the analysis by Columns or Rows,
  • represents the confidence level for mean of data points and is between 0 to 100,
  • represents the Kth largest value for each range of data,
  • represents the Kth smallest value for each range of data,
  • is a logical value that determines how the output should be displayed.

DESCRIPTIVESTATISTICS() calculates a range of statistical measurements and summarizes them into a table.

Description

DESCRIPTIVESTATISTICS(Array, GroupBy, ConfidenceLevel, KthLargest, KthSmallest, NewTableFlag)

  • can be numbers or can be names, arrays or references containing numbers.
  • Values containing text, logical values or empty cells are ignored.
  • Argument can be "Rows" or "Columns".
  • If < '0' or > '100', Calci displays an error message.
  • If < '0' or > 'Length of array', Calci displays an error message.
  • can be a logical value TRUE or FALSE. If omitted, Calci assumes it to be TRUE.
  • If is TRUE, output is displayed on a new ZSpace cube. If is FALSE, output is displayed on the same spreadsheet where command is written.
  • If data is invalid, Calci displays NaN error message.


Examples

Consider the following table with two arrays as input to DESCRIPTIVESTATISTICS() function -

Column1 Column2 Column3 Column4
Row1 8 8
Row2 7 8
Row3 11 11
Row4 7 18
Row5 33 37
Row6 
=DESCRIPTIVESTATISTICS(A2:B5,"Columns",95,1,1,TRUE)
=DESCRIPTIVESTATISTICS(A2:B6,"Rows",95,2,1,TRUE)

DESCRIPTIVESTATISTICS() displays the respective output for above examples -


Descriptive Statistics
Column 1
Mean 8.25
Standard Error 0.9464847243000456
Median 7.5
Mode 7
Standard Deviation 1.8929694486000912
Sample Variance 3.5833333333333335
Kurtosis 2.6154678204434845
Skewness 1.6585238002878033
Range 4
Minimum 7
Maximum 11
Sum 33
Count 4
Confidence Level 95% 3.0121394744317427
Largest(1) 11
Smallest(1) 7

Descriptive Statistics
Column 2
Mean 11.25
Standard Error 2.358495283014151
Median 9.5
Mode 8
Standard Deviation 4.716990566028302
Sample Variance 22.249999999999996
Kurtosis 1.9800530236081304
Skewness 1.517345279919216
Range 10
Minimum 8
Maximum 18
Sum 45
Count 4
Confidence Level 95% 7.505791229205204
Largest(1) 18
Smallest(1) 8

Descriptive Statistics
Row 1
Mean 8
Standard Error 0
Median 8
Mode 8 Standard Deviation 0
Sample Variance 0
Kurtosis NaN
Skewness NaN
Range 0
Minimum 8
Maximum 8
Sum 16
Count 2
Confidence Level 95% 0
Largest(2) 8
Smallest(1) 8

Descriptive Statistics
Row 2
Mean 7.5
Standard Error 0.5
Median 7.5
Mode #ERROR Standard Deviation 0.7071067811865476
Sample Variance 0.5000000000000001
Kurtosis NaN
Skewness NaN
Range 1
Minimum 7
Maximum 8
Sum 15
Count 2
Confidence Level 95% 6.353102368087358
Largest(2) 7
Smallest(1) 7

Descriptive Statistics
Row 3
Mean 11
Standard Error 0
Median 11
Mode 11 Standard Deviation 0
Sample Variance 0
Kurtosis NaN
Skewness NaN
Range 0
Minimum 11
Maximum 11
Sum 22
Count 2
Confidence Level 95% 0
Largest(2) 11
Smallest(1) 11

Descriptive Statistics
Row 4
Mean 12.5
Standard Error 5.499999999999999
Median 12.5
Mode #ERROR Standard Deviation 7.7781745930520225
Sample Variance 60.49999999999999
Kurtosis NaN
Skewness NaN
Range 11
Minimum 7
Maximum 18
Sum 25
Count 2
Confidence Level 95% 69.88412604896092
Largest(2) 7
Smallest(1) 7

See Also

References

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