Data Statistics in Calc

Use the data statistics in Calc to perform complex data analysis

To work on a complex statistical or engineering analysis, you can save steps and time by using Calc Data Statistics. You provide the data and parameters for each analysis, and the set of tools uses the appropriate statistical or engineering functions to calculate and display the results in an output table.

Valim

Create a table with data sampled from another table.

Selle käsu kasutamiseks...

Choose Data - Statistics - Sampling


Sampling allows you to pick data from a source table to fill a target table. The sampling can be random or in a periodic basis.

Märkuse ikoon

Sampling is done row-wise. That means, the sampled data will pick the whole line of the source table and copy into a line of the target table.


Data

Vahemik: ala, mis sisaldab andmeid, mida tuleb analüüsida.

Tulemused: viide lahtrivahemiku, kuhu tulemused väljastatakse, ülemisele vasakpoolsele lahtrile.

Valimi meetod

Random: Picks exactly Sample Size lines of the source table in a random way.

Sample size: Number of lines sampled from the source table.

Periodic: Picks lines in a pace defined by Period.

Period: the number of lines to skip periodically when sampling.

Näide

The following data will be used as example of source data table for sampling:

A

B

C

1

11

21

31

2

12

22

32

3

13

23

33

4

14

24

34

5

15

25

35

6

16

26

36

7

17

27

37

8

18

28

38

9

19

29

39


Sampling with a period of 2 will result in the following table:

12

22

32

14

24

34

16

26

36

18

28

38


Kirjeldav statistika

Fill a table in the spreadsheet with the main statistical properties of the data set.

Selle käsu kasutamiseks...

Choose Data - Statistics - Descriptive Statistics


The Descriptive Statistics analysis tool generates a report of univariate statistics for data in the input range, providing information about the central tendency and variability of your data.

Märkuse ikoon

For more information on descriptive statistics, refer to the corresponding Wikipedia article.


Data

Vahemik: ala, mis sisaldab andmeid, mida tuleb analüüsida.

Tulemused: viide lahtrivahemiku, kuhu tulemused väljastatakse, ülemisele vasakpoolsele lahtrile.

Rühmitamine

Vali, kas sisendandmed paiknevad veergudes või ridades.

Näide

Järgnevaid andmeid kasutatakse näidetena

A

B

C

1

Matemaatika

Füüsika

Bioloogia

2

47

67

33

3

36

68

42

4

40

65

44

5

39

64

60

6

38

43

7

47

84

62

8

29

80

51

9

27

49

40

10

57

49

12

11

56

33

60

12

57

13

26


The following table displays the results of the descriptive statistics of the sample data above.

Veerg 1

Veerg 2

Veerg 3

Keskmine

41.9090909091

59.7

44.7

Standardviga

3.5610380138

5.3583786934

4.7680650629

Mood

47

49

60

Mediaan

40

64.5

43.5

Dispersioon

139.4909090909

287.1222222222

227.3444444444

Standardhälve

11.8106269559

16.944681237

15.0779456308

Järsakus

-1.4621677981

-0.9415988746

1.418052719

Asümmeetria

0.0152409533

-0.2226426904

-0.9766803373

Vahemik

31

51

50

Miinimum

26

33

12

Maksimum

57

84

62

Summa

461

597

447

Arv

11

10

10


Dispersioonianalüüs (ANOVA)

Produces the analysis of variance (ANOVA) of a given data set

Selle käsu kasutamiseks...

Choose Data - Statistics - Analysis of Variance (ANOVA)


ANOVA is the acronym for ANalysis Of VAriance. This tool produces the analysis of variance of a given data set

Märkuse ikoon

For more information on ANOVA, refer to the corresponding Wikipedia article.


Data

Vahemik: ala, mis sisaldab andmeid, mida tuleb analüüsida.

Tulemused: viide lahtrivahemiku, kuhu tulemused väljastatakse, ülemisele vasakpoolsele lahtrile.

Rühmitamine

Vali, kas sisendandmed paiknevad veergudes või ridades.

Tüüp

Select if the analysis is for a single factor or for two factor ANOVA.

Parameetrid

Alpha: the level of significance of the test.

Rows per sample: Define how many rows a sample has.

Näide

Järgnevaid andmeid kasutatakse näidetena

A

B

C

1

Matemaatika

Füüsika

Bioloogia

2

47

67

33

3

36

68

42

4

40

65

44

5

39

64

60

6

38

43

7

47

84

62

8

29

80

51

9

27

49

40

10

57

49

12

11

56

33

60

12

57

13

26


The following table displays the results of the analysis of variance (ANOVA) of the sample data above.

ANOVA - üks tegur

Alfa

0.05

Rühmad

Arv

Summa

Keskmine

Dispersioon

Veerg 1

11

461

41.9090909091

139.4909090909

Veerg 2

10

597

59.7

287.1222222222

Veerg 3

10

447

44.7

227.3444444444

Variatsiooniallikas

SS

df

MS

F

P-väärtus

Rühmade vahel

1876.5683284457

2

938.2841642229

4.3604117704

0.0224614952

Rühmade sees

6025.1090909091

28

215.1824675325

Kokku

7901.6774193548

30


Korrelatsioon

Calculates the correlation of two sets of numeric data.

Selle käsu kasutamiseks...

Choose Data - Statistics - Correlation


The correlation coefficient (a value between -1 and +1) means how strongly two variables are related to each other. You can use the CORREL function or the Data Statistics to find the correlation coefficient between two variables.

A correlation coefficient of +1 indicates a perfect positive correlation.

A correlation coefficient of -1 indicates a perfect negative correlation

Märkuse ikoon

For more information on statistical correlation, refer to the corresponding Wikipedia article.


Data

Vahemik: ala, mis sisaldab andmeid, mida tuleb analüüsida.

Tulemused: viide lahtrivahemiku, kuhu tulemused väljastatakse, ülemisele vasakpoolsele lahtrile.

Rühmitamine

Vali, kas sisendandmed paiknevad veergudes või ridades.

Näide

Järgnevaid andmeid kasutatakse näidetena

A

B

C

1

Matemaatika

Füüsika

Bioloogia

2

47

67

33

3

36

68

42

4

40

65

44

5

39

64

60

6

38

43

7

47

84

62

8

29

80

51

9

27

49

40

10

57

49

12

11

56

33

60

12

57

13

26


The following table displays the results of the correlation of the sample data above.

Korrelatsioonid

Veerg 1

Veerg 2

Veerg 3

Veerg 1

1

Veerg 2

-0.4029254917

1

Veerg 3

-0.2107642836

0.2309714048

1


Kovariatsioon

Calculates the covariance of two sets of numeric data.

Selle käsu kasutamiseks...

Choose Data - Statistics - Covariance


The covariance is a measure of how much two random variables change together.

Märkuse ikoon

For more information on statistical covariance, refer to the corresponding Wikipedia article.


Data

Vahemik: ala, mis sisaldab andmeid, mida tuleb analüüsida.

Tulemused: viide lahtrivahemiku, kuhu tulemused väljastatakse, ülemisele vasakpoolsele lahtrile.

Rühmitamine

Vali, kas sisendandmed paiknevad veergudes või ridades.

Näide

Järgnevaid andmeid kasutatakse näidetena

A

B

C

1

Matemaatika

Füüsika

Bioloogia

2

47

67

33

3

36

68

42

4

40

65

44

5

39

64

60

6

38

43

7

47

84

62

8

29

80

51

9

27

49

40

10

57

49

12

11

56

33

60

12

57

13

26


The following table displays the results of the covariance of the sample data above.

Kovariatsioonid

Veerg 1

Veerg 2

Veerg 3

Veerg 1

126.8099173554

Veerg 2

-61.4444444444

258.41

Veerg 3

-32

53.11

204.61


Eksponentsilumine

Results in a smoothed data series

Selle käsu kasutamiseks...

Choose Data - Statistics - Exponential Smoothing


Exponential smoothing is a filtering technique that when applied to a data set, produces smoothed results. It is employed in many domains such as stock market, economics and in sampled measurements.

Märkuse ikoon

For more information on exponential smoothing, refer to the corresponding Wikipedia article.


Data

Vahemik: ala, mis sisaldab andmeid, mida tuleb analüüsida.

Tulemused: viide lahtrivahemiku, kuhu tulemused väljastatakse, ülemisele vasakpoolsele lahtrile.

Rühmitamine

Vali, kas sisendandmed paiknevad veergudes või ridades.

Parameetrid

Smoothing Factor: A parameter between 0 and 1 that represents the damping factor Alpha in the smoothing equation.

Näide

The following table has two time series, one representing an impulse function at time t=0 and the other an impulse function at time t=2.

A

B

1

1

0

2

0

0

3

0

1

4

0

0

5

0

0

6

0

0

7

0

0

8

0

0

9

0

0

10

0

0

11

0

0

12

0

0

13

0

0


The resulting smoothing is below with smoothing factor as 0.5:

Alfa

0.5

Veerg 1

Veerg 2

1

0

1

0

0.5

0

0.25

0.5

0.125

0.25

0.0625

0.125

0.03125

0.0625

0.015625

0.03125

0.0078125

0.015625

0.00390625

0.0078125

0.001953125

0.00390625

0.0009765625

0.001953125

0.0004882813

0.0009765625

0.0002441406

0.0004882813


Libisev keskmine

Calculates the moving average of a time series

Selle käsu kasutamiseks...

Choose Data - Statistics - Moving Average


Märkuse ikoon

For more information on the moving average, refer to the corresponding Wikipedia article.


Data

Vahemik: ala, mis sisaldab andmeid, mida tuleb analüüsida.

Tulemused: viide lahtrivahemiku, kuhu tulemused väljastatakse, ülemisele vasakpoolsele lahtrile.

Rühmitamine

Vali, kas sisendandmed paiknevad veergudes või ridades.

Parameetrid

Interval: The number of samples used in the moving average calculation.

Näide

The following table has two time series, one representing an impulse function at time t=0 and the other an impulse function at time t=2.

A

B

1

1

0

2

0

0

3

0

1

4

0

0

5

0

0

6

0

0

7

0

0

8

0

0

9

0

0

10

0

0

11

0

0

12

0

0

13

0

0


Results of the moving average:

Veerg 1

Veerg 2

#N/A

#N/A

0.3333333333

0.3333333333

0

0.3333333333

0

0.3333333333

0

0

0

0

0

0

0

0

0

0

0

0

0

0

0

0

#N/A

#N/A


Paired t-test

Calculates the paired t-Test of two data samples.

Selle käsu kasutamiseks...

Choose Data - Statistics - Paired t-test


A paired t-test is any statistical hypothesis test that follows a Student's t distribution.

Märkuse ikoon

For more information on paired t-tests, refer to the corresponding Wikipedia article.


Data

Variable 1 range: The reference of the range of the first data series to analyze.

Variable 2 range: The reference of the range of the second data series to analyze.

Results to: The reference of the top left cell of the range where the test will be displayed.

Rühmitamine

Vali, kas sisendandmed paiknevad veergudes või ridades.

Näide

The following table has two data sets.

A

B

1

28

19

2

26

13

3

31

12

4

23

5

5

20

34

6

27

31

7

28

31

8

14

12

9

4

24

10

0

23

11

2

19

12

8

10

13

9

33


Results for paired t-test:

The following table shows the paired t-test for the data series above:

paired t-test

Alfa

0.05

Oletatav keskmiste vahe

0

Muutuja 1

Muutuja 2

Keskmine

16.9230769231

20.4615384615

Dispersioon

125.0769230769

94.4358974359

Valimi maht

13

13

Pearsoni korrelatsioon

-0.0617539772

Keskmiste vahe valimis

-3.5384615385

Erinevuste dispersioon

232.9358974359

df

12

t-stat

-0.8359262137

P (T<=t) ühepoolne

0.2097651442

t kriitiline ühepoolne

1.7822875556

P (T<=t) kahepoolne

0.4195302884

t kriitiline kahepoolne

2.1788128297


F-test

Calculates the F-Test of two data samples.

Selle käsu kasutamiseks...

Choose Data - Statistics - F-test


A F-test is any statistical test based on the F-distribution under the null hypothesis.

Märkuse ikoon

For more information on F-tests, refer to the corresponding Wikipedia article.


Data

Variable 1 range: The reference of the range of the first data series to analyze.

Variable 2 range: The reference of the range of the second data series to analyze.

Results to: The reference of the top left cell of the range where the test will be displayed.

Rühmitamine

Vali, kas sisendandmed paiknevad veergudes või ridades.

Näide

The following table has two data sets.

A

B

1

28

19

2

26

13

3

31

12

4

23

5

5

20

34

6

27

31

7

28

31

8

14

12

9

4

24

10

0

23

11

2

19

12

8

10

13

9

33


Results for F-Test:

The following table shows the F-Test for the data series above:

Ftest

Alfa

0.05

Muutuja 1

Muutuja 2

Keskmine

16.9230769231

20.4615384615

Dispersioon

125.0769230769

94.4358974359

Valimi maht

13

13

df

12

12

F

1.3244637524

P (F<=f) parempoolne

0.3170614146

F kriitiline parempoolne

2.6866371125

P (F<=f) vasakpoolne

0.6829385854

F kriitiline vasakpoolne

0.3722125312

P kahepoolne

0.6341228293

F kriitiline kahepoolne

0.3051313549

3.277277094


Z-test

Calculates the z-Test of two data samples.

Selle käsu kasutamiseks...

Choose Data - Statistics - Z-test


Märkuse ikoon

For more information on Z-tests, refer to the corresponding Wikipedia article.


Data

Variable 1 range: The reference of the range of the first data series to analyze.

Variable 2 range: The reference of the range of the second data series to analyze.

Results to: The reference of the top left cell of the range where the test will be displayed.

Rühmitamine

Vali, kas sisendandmed paiknevad veergudes või ridades.

Näide

The following table has two data sets.

A

B

1

28

19

2

26

13

3

31

12

4

23

5

5

20

34

6

27

31

7

28

31

8

14

12

9

4

24

10

0

23

11

2

19

12

8

10

13

9

33


Results for z-Test:

The following table shows the z-Test for the data series above:

z-test

Alfa

0.05

Oletatav keskmiste vahe

0

Muutuja 1

Muutuja 2

Teadaolev dispersioon

0

0

Keskmine

16.9230769231

20.4615384615

Valimi maht

13

13

Keskmiste vahe valimis

-3.5384615385

z

#DIV/0!

P (Z<=z) ühepoolne

#DIV/0!

z kriitiline ühepoolne

1.644853627

P (Z<=z) kahepoolne

#DIV/0!

z kriitiline kahepoolne

1.9599639845


Hii-ruut test

Calculates the Chi-square test of a data sample.

Selle käsu kasutamiseks...

Choose Data - Statistics - Chi-square Test


Märkuse ikoon

For more information on chi-square tests, refer to the corresponding Wikipedia article.


Data

Input range: The reference of the range of the data series to analyze.

Results to: The reference of the top left cell of the range where the test will be displayed.

Rühmitamine

Vali, kas sisendandmed paiknevad veergudes või ridades.

Näide

The following table has two data sets.

A

B

1

28

19

2

26

13

3

31

12

4

23

5

5

20

34

6

27

31

7

28

31

8

14

12

9

4

24

10

0

23

11

2

19

12

8

10

13

9

33


Results for Chi-square Test:

Sõltumatuse test (hii-ruut)

Alfa

0.05

df

12

P-väärtus

2.32567054678584E-014

Testi statistik

91.6870055842

Kriitiline väärtus

21.0260698175