# An Overview

The data analysis tools package contains tools for statistical data analysis, random number generation and data sampling. To use these tools select the Data Analysis... item in the Tools menu. This yields a list of tools to choose from. Select one of the tools from the list and press the OK button or double-click on the tool. The tools are described below.

## Data Analysis Tools

Gnumeric's Data Analysis Tools

All tools have the same output options (see ). The results can be printed into a new sheet, into a new workbook, or into a given output range on a sheet of the current workbook. To select the output method select one of the radio buttons inside the Output frame. If you have chosen Output Range you must also enter a single range in the entry field.

Select the Autofit Columns option to automatically adjust the widths of the columns in the output range.

If the chosen output range is too small, some of the results will be lost.

The old data in the output range is deleted and cannot be recovered.

## Common output options of the data analysis tools

To enter a range into an entry field, you can either type the range specification into the text field, or click in the text field and then select the range on the sheet (see ).

## Specifying Ranges

Some entry fields accept lists of ranges. To enter these lists, select one range, type a comma, and then select the next range. At any time, you may switch to another sheet of the workbook.

# Analysis of Variance

## ANOVA: Single Factor Tool

Use this tool to perform a single factor analysis of the variances of given variables. The variables are specified by the Input Range: entry. The given range can be grouped into the variables either by columns, by rows or by areas. The Alpha: entry specifies the significance level which is by default 5%.

If the first row or first column of the given range, or the first field of each area contains labels, select the Labels option. The names of the variables will be included in the output table.

The results of this analysis of variance are presented in a standard ANOVA table. The F critical value is the largest value of F that is statistically significant using the given significance level (Alpha).

This tool also calculates the count, sum, average, and the variance of each variable.

### 1-factor ANOVA Dialog and Example Data

### Using the single factor ANOVA

See for an example of a completed dialog and for the corresponding output.

### Output From a 1-factor ANOVA

## ANOVA: Two-Factor Tool

Gnumeric can perform two factor fixed effects ANOVAs with and without replication. The same dialog is used and the appropriate tool is selected depending on whether the number of rows per sample is 1 or larger than 1.

### ANOVA: Two-Factor Without Replication Tool

If the number of rows per sample is given as 1, Gnumeric performs a two factor fixed effects ANOVA without replication. Each column of the input range is interpreted as a level of the first factor while each row is interpreted as a level of the second factor.

The first row and column of the range may contain labels for these levels. In this case the Labels option should be selected.

The Alpha: entry specifies the significance level which is by default 5%.

#### Using the 2-factor ANOVA Without Replication Tool

See for an example of a completed dialog and for the corresponding output.

#### 2-factor ANOVA Without Replication Dialog

#### Output From a 2-factor ANOVA Without Replication

### ANOVA: Two-Factor With Replication Tool

If the number of rows per sample is larger than 1, Gnumeric performs a two factor fixed effects ANOVA with replication. Each column of the input range is interpreted as a level of the first factor while groups of rows (the number of rows in each group given by the number of rows per sample value) are interpreted as levels of the second factor.

The first row and column of the range may contain labels for these levels. In this case the Labels option should be selected.

The Alpha: entry specifies the significance level which is by default 5%.

See for an example of a completed dialog and for the corresponding output.

#### 2-factor ANOVA With Replication Dialog

#### Output From a 2-factor ANOVA With Replication

Gnumeric will estimate missing values for each level combination as the mean of the existing values in that combination. The degrees of freedom are adjusted appropriately.

# Correlation Tool

## Correlation Tool Dialog

The correlation tool calculates the pairwise Pearson correlation coefficients of the given variables. Use this tool to calculate any number of correlation coefficients at the same time. The variables for which the correlations are calculated are specified by the Input Range: entry. The input range can consist of either a single range or a comma separated list of ranges. The given range or ranges can be grouped by columns, by rows, or by areas.

If the first row or column of the given ranges, or the first field of each area contains labels, the Labels option should be selected.

## Some Example Data

## Using the Correlation Tool

For example, you want to calculate the correlation between three variables, one each in columns A, B, and C. Both variables have 10 values in rows 2 to 11 with labels in row 1 (see ).

1. Enter A1:B11 in the Input Range: entry by typing this directly into the entry or clicking in the entry field and then selecting that range on the sheet. In the latter case the entry will also contain the sheet name.

2. Select the Columns radio button next to Grouped By:, since each variable is in its own column.

3. Select the Labels option since the first row contains labels. (see ).

4. Specify the output options as described above.

5. Press the OK button.

The calculated correlations are given in a table with each column and row labeled with the names of the variables. If the names are not given in the input range, Gnumeric generates them. In our example, the correlation between the variables in column A and B, can be found in the second column and third row of the results table (see ).

## Completed Correlation Dialog

## Correlation Tool Output

# Covariance Tool

## Covariance Tool Dialog

The covariance tool calculates the pairwise covariance coefficients of the given variables. Use this tool to calculate any number of covariance coefficients at the same time. The variables for which the covariances are calculated are specified by the Input Range: entry. The input range can consist of either a single range or a comma separated list of ranges. The given range or ranges can be grouped by columns, by rows, or by areas.

If the first row or column of the given ranges, or the first field of each area contains labels, the Labels option should be selected.

## Some Example Data

## Using The Covariance Tool

For example, you want to calculate the covariance between three variables, one each in columns A, B, and C. Both variables have 10 values in rows 2 to 11 with labels in row 1 (see ).

1. Enter A1:B11 in the Input Range: entry by typing this directly into the entry or clicking in the entry field and then selecting that range on the sheet. In the latter case the entry will also contain the sheet name.

2. Select the Columns radio button next to Grouped By:, since each variable is in its own column.

3. Select the Labels option since the first row contains labels.

4. Specify the output options as described above.

5. Press the OK button.

The calculated covariances are given in a table with each column and row labeled with the names of the variables. If the names are not given in the input range, Gnumeric generates them. In our example, the covariance between the variables in column A and B, can be found in the second column and third row of the results table (see ).

## Covariance Tool Output

# Descriptive Statistics Tool

## Descriptive Statistics Tool Dialog

The descriptive statistics tool calculates various statistics for the given variables and a confidence interval for the population mean. The variables are specified via the Input Range: entry. The given range or list of ranges can be grouped into variables by columns, rows, or areas.

This tool can produce four different kinds of statistical data.

- If the Summary Statistics option is selected, this tool calculates the mean, standard error, median, mode, standard deviation, sample variance, kurtosis, skewness, range, minimum, maximum, sum, and count for each variable.

- If the Confidence Interval for the Mean option is selected, the tool calculates confidence intervals for the population mean of each variable. Specify the confidence level in the entry box. The default confidence level is 95%.

  The interval given will usually be wider than the interval obtained using the CONFIDENCE function. The CONFIDENCE function assumes that the population standard deviation is known. This tool estimates the population standard deviation using the sample standard deviation.

- If the Kth Largest: option is selected, the tool finds the `k`th largest value of each of the variables. Specify `k` in the entry box next to the option. The default is 1.

- If the Kth Smallest: option is selected, the tool finds the `k`th smallest value of each of the variables. Specify `k` in the entry box next to the option. The default is 1.

If the first entry for each variable contains the label, select the Labels option.

## Some Example Data

## Using the Descriptive Statistics Tool

shows some example data and the corresponding output.

## Descriptive Statistics Tool Output

# Exponential Smoothing Tool

## Exponential Smoothing Tool Dialog

The Exponential Smoothing tool performs the exponential smoothing for the given set or sets of values. Each value in the smoothed set is predicted based on the forecast for the prior period. The formula to calculate the forecast is: F(t+1) = F(t) + (1 - dampingFactor) * (A(t) - F(t)), where `A(t)` is the `t`th value in the original data set.

Specify the cells containing the datasets in the Input Range entry. The entered range or ranges are grouped into datasets either by rows or by columns.

If you have labels in the first cell of each data set, select the Labels option.

Specify prior forecast adjustment value in the Damping factor entry. A value, for example, between 0.2 and 0.3 represents 20 to 30 percent error adjustment in the prior forecast.

If you want to have the standard errors output as well, press the checkbutton on before starting the tool. The standard errors are calculated using the following formula: e(t) = SQRT ( ((A(t-3)-F(t-3))^2 + (A(t-2)-F(t-2))^2 + (A(t-1)-F(t-1))^2) / 3 ).

## Some Example Data for the Exponential Smoothing Tool

## Using the Exponential Smoothing Tool

shows some example data and the corresponding output.

## Exponential Smoothing Tool Output

# Fourier Analysis Tool

## Fourier Analysis Tool Dialog

The Fourier Analysis tool normally performs a Fast Fourier Transform to obtain the discrete fourier transform Fs of the given sequence ft of real numbers according to the formula given in .

Select the Inverse option to calculate the inverse discrete fourier transform ft of the given sequence Fs of real numbers

If the given sequences does not contain a number of terms that is a power of 2 (i.e. 2, 4, 8, 16, 32, 64, 128, etc.), this tool will append zeros to reach such a power of 2!

Specify the cells containing the datasets in the Input Range entry. The entered range or ranges are grouped into sequences either by rows or by columns.

If you have labels in the first cell of each data set, select the Labels option.

## Fourier Analysis Formulae

Before using the numbers obtained by this tool, ensure that these are in fact the correct formulae for your discipline. In the physical sciences this fourier transform tends to be called the inverse fourier transform and vice versa. Moreover, frequently the scaling factor varies.

For example Mathematica uses the terms fourier transform and inverse fourier transform with the reversed meaning than Gnumeric and it uses a scaling factor of `1/SQRT(N)` rather than `1/N`.

# F-Test: Two-Sample for Variances Tool

## F-Test Tool Dialog

Use the F-Test tool to test whether two population variances are different against the null hypothesis that they are not.

Specify the variables in the Variable 1 Range: and Variable 2 Range: entries. The Alpha: entry contains the significance level which is by default 5%.

If the first field of each range contains labels, select the Labels option. The names of the variables will be included in the output table.

The results are given in a table. This table contains the mean, variance, count of observations and the degree of freedom for both variables. The output table also includes the F-value, the one-tailed probability for the F-value, and the F Critical value for one-tailed test and the corresponding values for a two tailed test. The one-tailed probability for the F-value (P(F<=f) one-tail row) is the probability of making a Type I error in the one-tailed test. Similarly, the two-tailed probability for the F-value (P two-tail row) is the probability of making a Type I error in the two-tailed test. Since in the two-tailed F-Test both critical values are positive, the F Critical two-tail row contains two numbers.

If the output is directed into a specific output range, that range should contain at least three columns and eight rows.

## Some Example Data

## Using the F-Test Tool

shows some example data and the corresponding output.

## F-Test Tool Output

# Histogram Tool

## Introduction

### Histogram Tool Dialog

The histogram calculates several kinds of histograms for one or more variables. The types of histogram created are determined by the options selected.

As shown in , the histogram dialog has four tabs. We will introduce them in sequence.

## The Input Tab

The Input tab contains the field specifying the data to be used for the histogram.

The Input Range entry contains a single range or a list of ranges, that can be grouped into variables by rows, columns, or areas. The Bin Range entry contains a single range of cutoff values. Both ranges may also include labels.

If the first row or column of the given input ranges, or the first field of each area contains labels, the Input Labels option should be selected.

## The Bins Tab

### Histogram Tool Dialog Bins Tab

The bins (or classes) for the histogram can either be predetermined by data contained in your workbook or calculated by the histogram tool.

Select the Predetermined Bins option to specify data on your worksheet in the Bin Range: entry. The range should consist of a sigle column or two columns (the first one containing labels). If the first column of the bin range contains labels, select the Bin Labels option. The values in the last column are used as seprators between adjacent bins.

Select the Calculated Bins option to have the bins determined by the tool. Enter the desired number of bins in the N: entry. It is recommended (but optional) that you specify the minimum and maximum cutoffs in the Min: and Max: entries.

## The Options Tab

The options in the options tab modify the appearance of the histogram:

- The Pareto option causes the bins to be sorted by decreasing frequency of the first variable.

- The Percentages option adds a histogram in terms of percentages rather than frequencies.

- The Cumulative Percentages option adds a cumulative histogram.

- The Chart option is not implemented.

## The Output Tab

The Output tab contains the standard output options and fields described in .

## A Histogram Example

### Some Example Data

### Specifying Bins

### Using the Histogram Tool

For example, you want to calculate a histogram and a cumulative histogram in percentages for a the number of successes in several sequences of trials. The numbers of successes are recorded in column A and the classes of interest in column D with labels in column C (see ).

1. Enter A1:A21 in the Input Range: entry of the Input tab by typing this directly into the entry or clicking in the entry field and then selecting that range on the sheet. In the latter case the entry will also contain the sheet name.

2. Since you only have one variable select the Areas or Columns radio button next to Grouped By:.

3. Select the Input Labels option since the first cell of the Input Range contains a label.

4. Enter C1:D5 in the Bin Range: entry of the Bins tab. The Predetermined Bins option will now also be selected (see ).

5. Select the Bin Labels option since the first column of the Bin Range contains labels.

6. Select the Percentage and Cumulative Percentages options of the Options tab.

7. In the Output tab, specify the output options as described in .

8. Press the OK button.

The results are shown in .

### Histogram Tool Output

# Moving Average Tool

## Moving Average Tool Dialog

Use the moving average tool to calculate moving averages of one or more data sets. A moving average provides useful trend information of the data that is lost in a simple average. In addition, moving averages can be used to eliminate random variance. For example, use this tool to create a smoother curve of a stock prize.

Specify the cells containing the datasets in the Input Range entry. The entered range or ranges are grouped into datasets either by rows or by columns.

If you have labels in the first cell of each data set, select the Labels option.

Specify the Interval for the moving average. The interval `i` is the number of consecutive values to be included in each moving average.

The results are given in one column for each dataset Each row represents the moving average of the corresponding row or column in the input range. The moving average cannot be calculated for the first `k` rows in the input range where `k` is smaller than the given interval `i`.

## Some Example Data for the Moving Average Tool

## Using the Moving Average Tool

shows some example data and the corresponding output.

## Moving Averages Tool Output

# Random Number Generation Tool

## Random Number Generation Tool Dialog

Use the random number generation tool to generate random numbers. This tool can generate random numbers from various probability distributions.

Specify the number of variables in the Number of Variables: entry. This determines the number of columns of random values to be produced.

Specify the number of random numbers for each variable in the Size of Sample: entry. This determines the number of rows of random values to be produced.

Specify the random distribution by selecting one of the items from the random distribution list. The following random distributions are supported: Discrete, Normal, Poisson, Exponential, Binomial, Negative Binomial, Bernoulli, and Uniform.

Specify the parameters of the selected distribution:

Discrete Random Distribution

  Specify the value and probability input range in the Value and Probability Input Range: entry box. The value and probability input range is a table consisting of two columns and any number of rows. The first column specifies the discrete random values and the second column the probabilities for them. The discrete random values do not have to be numbers, for example, strings will do as well. The sum of the probabilities in the second column should be one. For example, if you have the values A, B, C, and D in A1:A4 and values 0.1, 0.4, 0.2, and 0.3 in B1:B4, you would specify the value and probability input range to be A1:B4.

  If the probabilities do not add to 1, they will be automatically scaled.

Normal Random Distribution

  Specify the mean and the standard deviation. The default values are 0 for the mean and 1 for the standard deviation.

Poisson Random Distribution

  Specify the lambda in the Lambda entry. Lambda is the average number of events in a unit time interval.

Exponential Random Distribution

  Specify `b` in the b Value entry.

Binomial Random Distribution

  Specify the probability of success (`p`) in the p Value entry and the number of trials (`n`) in the Number of Trials entry. The Binomial distribution is a discrete distribution in which the experiment consists of `n` identical trials. Each trial is independent of other the trials and has two possible outcomes, a success or a failure. The probability of success `p` is constant from one trial to another. The mean of a random variable that has a Binomial distribution is `E(X) = np`, and the variance is `var(X) = np(1-p)`.

Negative Binomial Distribution

  Specify the probability of success `p` in the p Value entry and the number of failures `r` in the Number of Failures entry. Negative Binomial distribution is a discrete distribution in which the experiment consists of a sequence of independent trials. Each trial has two possible outcomes, a success or a failure. The probability of success `p` is constant from one trial to another. The experiment continues until `r` failures are observed, where `r` is fixed in advance. The mean of a random variable that has a Negative Binomial distribution is `E(X) = r(1-p)/p`, and the variance is `var(X) = r(1-p)/p^2`.

Bernoulli Random Distribution

  Specify the probability of success (`p`) in the p Value entry. `p` is a probability value between 0 and 1. The Bernoulli distribution has two random values 0 and 1, and `p` is the probability to observe value 1. The mean of a random variable that has a Bernoulli distribution is `E(X) = 1(p) + 0(1-p) = p`, and the variance is `var(X) = p(1-p)`.

Uniform Random Distribution

  Specify the range of the continuous random variable with the Between: and And: entries. The default values for these entries are 0 and 1.

## Some Example Data for the Random Number Generation Tool

## Using the Random Number Generation Tool

shows some example data and the corresponding output.

## Random Number Generation Tool Output

# Rank and Percentile Tool

## Rank and Percentile Tool Dialog

Use this tool to rank given data and to calculate the percentiles of each data point.

Specify the datasets to use in the Input Range: entry. The given range can be grouped into datasets by columns, by rows, or by areas.

For each dataset, the tool creates three columns in the output table:

1. The first column gives the indices of the ordered data from largest to smallest data value.

2. The second column gives data values corresponding to the indices in the first column.

3. The third column indicates the percentile of the data value in the second column.

If you have labels in the first cell of each data set, select the Labels option.

## Some Example Data for the Rank and Percentile Tool

## Using the Rank and Percentile Tool

shows some example data and the corresponding output.

## Rank and Percentile Tool Output

In the case of ties, the rank calculated by this tool differs from the value of the RANK function for the same data. This tool calculates the rank as it is normally used in Statistics: If two values are tied, the assigned rank is the average rank for those entries. For example in the two values 10 are the second and third largest values. Since they are equal each receives the rank of 2.5, the average of 2 and 3. The rank function on the other hand assigns the rank as it is normally used to determine placements. The two values 10 would therefore each receive a rank of 2.

# Regression Tool

## Regression Tool Dialog

The regression tool performs a multiple regression analysis.

Enter a range or list of ranges containing the independent variables into the X Variables: entry. These ranges can be grouped into the various independent variables by columns, by rows, or by areas. Select the appropriate option.

Enter a single range containing the dependent variable into the Y Variable: entry.

If the ranges for the independent and dependent variables also contains labels in the first field of each row, column or area, select the Labels option.

Specify the confidence level in the Confidence Level: entry. The default is 95%.

To force the regression line or plane to pass through the origin, select the Force Intercept To Be Zero option.

Specify the output options as described above. If the output is directed into a specific output range, that range should contain at least seven columns and 17 rows more than there are independent variables.

## Regression Example Data

## Using the Regression Tool

Suppose you want to perform a regression analysis on the data given in using `v` and `y` as independent variables and `u` as dependent variable.

1. Enter B1:B9,E1:E9 in the X Variables: entry by typing this directly into the entry or clicking in the entry field and then selecting the first part range on the sheet, typing comma, and then selecting the second range. In the latter case the entry will also contain the sheet name.

2. Enter A1:A9 in the Y Variable: entry.

3. Select the Columns or Areas option since each variable is in its own column and also its own area.

4. Select the Labels option since the first row contains labels. (see ).

5. Specify the output options as described above.

6. Press the OK button.

The output of this regression analysis is shown in .

## Completed Regression Dialog

## Regression Tool Output

# Sampling Tool

## Sampling Tool Dialog

Use the sampling tool to take a sample of a data set. This tool can take both a random sample of a given size or a periodic sample:

random sample

  A random sample is a subset of the population such that every subset of that size has the same chance of being picked.

periodic sample

  In a periodic sample every `k`th element in the population is selected.

To use this tool, first specify the data set or data sets by setting the Input Range: entry. The range or ranges given can be grouped into datasets by rows, by columns, or by areas.

If the first entry in each data set contains a variable, select the Labels option.

Select the sampling method which can be either periodic or random.

random sample

  Specify the size of the random sample in the Size of Sample: entry.

periodic sample

  Specify the period in the Period: entry.

Specify the number of samples you would like to obtain in the Number of Samples: entry.

Since the period uniquely determines a periodic sample, if you specify that you would like 2 samples you will be given the identical sample twice.

If the dataset for a periodic sample is a two dimensional range, Gnumeric will enumerate the data points by row first.

## Some Example Data for the Sampling Tool

## Using the Sampling Tool

shows some example data and the corresponding output.

## Sampling Tool Output

# Comparing Means of 2 Populations

Gnumeric provides 4 similar tools to test whether the difference of two population means is equal to a hypothesized value. These four tools use the same dialog (see ).

## `t`- and `z`-Test Tool Dialog

Depending on the options settings, the appropriate test will be performed. The entries in the Input, Test, and Output frames are independent from the specific test.

Enter the first variable in the Variable 1 Range entry and the second variable in the Variable 2 Range entry.

Enter the hypothesized difference between the population means in the Hypothesized Mean Difference entry, which has a default of 0. Enter the significance level in the Alpha entry, which has a default of 5 %.

Specify the output options as described above. If the output is printed into a range, it should have at least three columns and ten rows.

There are up to three possible options that can be selected:

Paired versus Unpaired

  If the variables are dependent (or paired) select the Paired option.

Known versus Unknown

  For unpaired or independent variables, the population variances may be known or unknown. In the latter case they will be estimated using the sample variances. Select the Known option if you in fact know the population variances prior to collecting the sample.

Equal versus Unequal

  For paired variables with unknown population variances, we may either assume that the population variances are equal or not. If the population variances are assumed to be equal, Gnumeric will estimate the common variance by pooling the sample variances. Select the Equal option to assume that the population variances are equal.

## `t`-Test: Paired Two Sample for Means Tool

### `t`-Test (Paired) Tool Dialog Options

For paired variables, when you click on OK, Gnumeric will test whether the mean of the difference between the paired variables is equal to the given hypothesized mean difference.

### Using the `t`-Test (Paired) Tool

See for an example of a completed dialog and for the corresponding output.

### `t`-Test (Paired) Example Data

### Output from the `t`-Test (Paired) Tool

## `t`-Test: Two-Sample Assuming Equal Variances Tool

### `t`-Test (Equal Variances) Tool Dialog Options

For unpaired variables with unknown but assumed equal population variances, when you click on OK, Gnumeric will test whether the mean of the difference between the paired variables is equal to the given hypothesized mean difference.

### Using the `t`-Test (Unknown but Equal Variances) Tool

See for an example of a completed dialog and for the corresponding output.

### `t`-Test (Unknown but Equal Variances) Example Data

### Output from the `t`-Test (Unknown but Equal Variances) Tool

## `t`-Test: Two-Sample Assuming Unequal Variances Tool

### `t`-Test (Unknown and Unequal Variances) Tool Dialog Options

For unpaired variables with unknown and assumed unequal population variances, when you click on OK, Gnumeric will test whether the mean of the difference between the paired variables is equal to the given hypothesized mean difference.

### Using the `t`-Test (Unknown and Unequal Variances) Tool

See for an example of a completed dialog and for the corresponding output.

### `t`-Test (Unknown and Unequal Variances) Example Data

### Output from the `t`-Test (Unknown and Unequal Variances) Tool

## `z`-Test: Two Samples for Means Tool

### `z`-Test Tool Dialog Options

For unpaired variables with known population variances, enter those variances in the Variable 1 Pop. Variance and Variable 2 Pop. Variance entries. When you click on OK, Gnumeric will test whether the mean of the difference between the paired variables is equal to the given hypothesized mean difference.

### Using the `z`-Test Tool

See for an example of a completed dialog and for the corresponding output.

### `z`-Test Example Data

### Output from the `z`-Test Tool

Output from the `z`-Test Tools