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.

Mostraxe

Create a table with data sampled from another table.

Para acceder a esta orde...

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.

Icona Nota

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.


Datos

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

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

Método de mostraxe

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.

Exemplo

Os datos seguintes serán usados como exemplo de mostraxe dunha táboa de orixe:

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


Estatísticas descritivas

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

Para acceder a esta orde...

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.

Icona Nota

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


Datos

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

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

Agrupado por

Select whether the input data has columns or rows layout.

Exemplo

Os datos seguintes serán usados como exemplo

A

B

C

1

Maths

Physics

Biology

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.

Columna

Columna

Columna

Media

41.9090909091

59.7

44.7

Standard Error

3.5610380138

5.3583786934

4.7680650629

Modo

47

49

60

MEDIANA

40

64.5

43.5

Varianza

139.4909090909

287.1222222222

227.3444444444

Standard Deviation

11.8106269559

16.944681237

15.0779456308

Kurtosis

-1.4621677981

-0.9415988746

1.418052719

Skewness

0.0152409533

-0.2226426904

-0.9766803373

Intervalo

31

51

50

Mínimo

26

33

12

Máximo

57

84

62

Suma

461

597

447

Conta

11

10

10


Análise de varianza (ANOVA)

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

Para acceder a esta orde...

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

Icona Nota

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


Datos

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

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

Agrupado por

Select whether the input data has columns or rows layout.

Tipo

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

Parámetros

Alpha: the level of significance of the test.

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

Exemplo

Os datos seguintes serán usados como exemplo

A

B

C

1

Maths

Physics

Biology

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 - Single Factor

Alfa:

0.05

Grupos

Conta

Suma

Media

Varianza

Columna

11

461

41.9090909091

139.4909090909

Columna

10

597

59.7

287.1222222222

Columna

10

447

44.7

227.3444444444

Source of Variation

SS

df

ms

F

P-value

F-critical

Entre grupos

1876.5683284457

2

938.2841642229

4.3604117704

0.0224614952

3.340385558

Dentro de grupos

6025.1090909091

28

215.1824675325

Total

7901.6774193548

30


Correlación

Calcula a correlación entre dous conxuntos de datos numéricos.

Para acceder a esta orde...

Escolla Datos - Estatísticas - Correlación


O coeficiente de correlación (un valor entre -1 e +1) indica a forza coa que se relacionan dúas variábeis entre si. Pódese empregar a función CORREL ou Estatísticas de datos para atopar o coeficiente de correlación entre dúas variábeis.

Un coeficiente de correlación de +1 indica unha correlación positiva perfecta.

Un coeficiente de correlación de -1 indica unha correlación negativa perfecta

Icona Nota

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


Datos

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

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

Agrupado por

Select whether the input data has columns or rows layout.

Exemplo

Os datos seguintes serán usados como exemplo

A

B

C

1

Maths

Physics

Biology

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


A táboa seguinte mostra os resultados da correlación dos datos de exemplo anteriores.

Correlación

Columna

Columna

Columna

Columna

1

Columna

-0.4029254917

1

Columna

-0.2107642836

0.2309714048

1


Covarianza

Calcula a covarianza de dous conxuntos de datos numéricos.

Para acceder a esta orde...

Escolla Datos - Estatísticas - Covarianza


A covarianza é unha medida de canto cambian xuntas dúas variábeis aleatorias.

Icona Nota

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


Datos

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

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

Agrupado por

Select whether the input data has columns or rows layout.

Exemplo

Os datos seguintes serán usados como exemplo

A

B

C

1

Maths

Physics

Biology

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


A táboa seguinte mostra os resultados da covarianza dos datos de exemplo anteriores.

Covarianza

Columna

Columna

Columna

Columna

126.8099173554

Columna

-61.4444444444

258.41

Columna

-32

53.11

204.61


Suavizado exponencial

Resultados en series de datos suavizados

Para acceder a esta orde...

Escolla Datos - Estatísticas - Suavizado exponencial


O suavizado exponencial é unha técnica de filtrado que produce resultados suavizados ao ser aplicada a un conxuntos de datos. Emprégase en moitos dominios, como no mercado de valores, na economía e en medidas de mostras.

Icona Nota

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


Datos

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

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

Agrupado por

Select whether the input data has columns or rows layout.

Parámetros

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

Exemplo

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


O suavizado resultante aparece abaixo cun factor de suavizado de 0,5:

Alfa:

0.5

Columna

Columna

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


Media móbil

Calcula a media móbil dunha serie temporal

Para acceder a esta orde...

Escolla Datos - Estatísticas - Media móbil


Icona Nota

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


Datos

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

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

Agrupado por

Select whether the input data has columns or rows layout.

Parámetros

Intervalo: O número de mostras empregado no cálculo da media móbil.

Exemplo

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


Resultados da media móbil:

Columna

Columna

#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.

Para acceder a esta orde...

Choose Data - Statistics - Paired t-test


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

Icona Nota

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


Datos

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.

Agrupado por

Select whether the input data has columns or rows layout.

Exemplo

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

Hypothesized Mean Difference

0

Variábel 1

Variábel 2

Media

16.9230769231

20.4615384615

Varianza

125.0769230769

94.4358974359

Observations

13

13

Pearson Correlation

-0.0617539772

Observed Mean Difference

-3.5384615385

Varianza das diferenzas

232.9358974359

df

12

t Stat

-0.8359262137

P (T<=t) one-tail

0.2097651442

t Critical one-tail

1.7822875556

P (T<=t) two-tail

0.4195302884

t Critical two-tail

2.1788128297


F-test

Calculates the F-Test of two data samples.

Para acceder a esta orde...

Choose Data - Statistics - F-test


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

Icona Nota

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


Datos

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.

Agrupado por

Select whether the input data has columns or rows layout.

Exemplo

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

Variábel 1

Variábel 2

Media

16.9230769231

20.4615384615

Varianza

125.0769230769

94.4358974359

Observations

13

13

df

12

12

F

1.3244637524

P (F<=f) right-tail

0.3170614146

F Critical right-tail

2.6866371125

P (F<=f) left-tail

0.6829385854

F Critical left-tail

0.3722125312

P two-tail

0.6341228293

F Critical two-tail

0.3051313549

3.277277094


Z-test

Calculates the z-Test of two data samples.

Para acceder a esta orde...

Choose Data - Statistics - Z-test


Icona Nota

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


Datos

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.

Agrupado por

Select whether the input data has columns or rows layout.

Exemplo

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

Hypothesized Mean Difference

0

Variábel 1

Variábel 2

Known Variance

0

0

Media

16.9230769231

20.4615384615

Observations

13

13

Observed Mean Difference

-3.5384615385

z

#DIV/0!

P (Z<=z) one-tail

#DIV/0!

z Critical one-tail

1.644853627

P (Z<=z) two-tail

#DIV/0!

z Critical two-tail

1.9599639845


Chi-square test

Calculates the Chi-square test of a data sample.

Para acceder a esta orde...

Choose Data - Statistics - Chi-square Test


Icona Nota

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


Datos

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.

Agrupado por

Select whether the input data has columns or rows layout.

Exemplo

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:

Test of Independence (Chi-Square)

Alfa:

0.05

df

12

P-value

2.32567054678584E-014

Test Statistic

91.6870055842

Critical Value

21.0260698175


Axúdenos, por favor!