Random Number Generation analysis tool

This analysis tool fills a range with independent random numbers drawn from one of several distributions. You can characterize subjects in a population with a probability distribution. For example, you might use a normal distribution to characterize the population of individuals' heights, or you might use a Bernoulli distribution of two possible outcomes to characterize the population of coin-flip results.

Number of Variables

Enter the number of columns of values you want in the output table. If you do not enter a number, Microsoft Excel fills all columns in the output range you specify.

Number of Random Numbers

Enter the number of data points you want to see. Each data point appears in a row of the output table. If you do not enter a number, Microsoft Excel fills all rows in the output range you specify.

Distribution

Click the distribution method you want to use to create random values.

Uniform

Characterized by lower and upper bounds. Variables are drawn with equal probability from all values in the range. A common application uses a uniform distribution in the range 0...1.

Normal

Characterized by a mean and a standard deviation. A common application uses a mean of 0 and a standard deviation of 1 for the standard normal distribution.

Bernoulli

Characterized by a probability of success (p value) on a given trial. Bernoulli random variables have the value 0 or 1. For example, you can draw a uniform random variable in the range 0...1. If the variable is less than or equal to the probability of success, the Bernoulli random variable is assigned the value 1; otherwise, it is assigned the value 0.

Binomial

Characterized by a probability of success (p value) for a number of trials. For example, you can generate number-of-trials Bernoulli random variables, the sum of which is a binomial random variable.

Poisson

Characterized by a value lambda, equal to 1/mean. Poisson distribution is often used to characterize the number of events that occur per unit of time — for example, the average rate at which cars arrive at a toll plaza.

Patterned

Characterized by a lower and upper bound, a step, repetition rate for values, and repetition rate for the sequence.

Discrete

Characterized by a value and the associated probability range. The range must contain two columns: The left column contains values, and the right column contains probabilities associated with the value in that row. The sum of the probabilities must be 1.

Parameters

Enter a value or values to characterize the distribution selected.

Random Seed

Enter an optional value from which to generate random numbers. You can reuse this value later to produce the same random numbers.

Output Range

Enter the reference for the upper-left cell of the output table. Microsoft Excel automatically determines the size of the output area and displays a message if the output table will replace existing data.

New Worksheet Ply

Click to insert a new worksheet in the current workbook and paste the results starting at cell A1 of the new worksheet. To name the new worksheet, type a name in the box.

New Workbook

Click to create a new workbook and paste the results on a new worksheet in the new workbook.

Descriptive Statistics analysis tool

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

Input Range

Enter the cell reference for the range of data you want to analyze. The reference must consist of two or more adjacent ranges of data arranged in columns or rows.

Grouped By

To indicate whether the data in the input range is arranged in rows or in columns, click Rows or Columns.

Labels in First Row/Labels in First Column

If the first row of your input range contains labels, select the Labels in First Row check box. If the labels are in the first column of your input range, select the Labels in First Column check box. This check box is clear if your input range has no labels; Microsoft Excel generates appropriate data labels for the output table.

Confidence Level for Mean

Select if you want to include a row in the output table for the confidence level of the mean. In the box, enter the confidence level you want to use. For example, a value of 95 percent calculates the confidence level of the mean at a significance of 5 percent.

Kth Largest

Select if you want to include a row in the output table for the kth largest value for each range of data. In the box, enter the number to use for k. If you enter 1, this row contains the maximum of the data set.

Kth Smallest

Select if you want to include a row in the output table for the kth smallest value for each range of data. In the box, enter the number to use for k. If you enter 1, this row contains the minimum of the data set.

Output Range

Enter the reference for the upper-left cell of the output table. This tool produces two columns of information for each data set. The left column contains statistics labels, and the right column contains the statistics. Microsoft Excel writes a two-column table of statistics for each column or row in the input range, depending on the Grouped By option selected.

New Worksheet Ply

Click to insert a new worksheet in the current workbook and paste the results starting at cell A1 of the new worksheet. To name the new worksheet, type a name in the box.

New Workbook

Click to create a new workbook and paste the results on a new worksheet in the new workbook.

Summary statistics

Select if you want Microsoft Excel to produce one field for each of the following statistics in the output table: Mean, Standard Error (of the mean), Median, Mode, Standard Deviation, Variance, Kurtosis, Skewness, Range, Minimum, Maximum, Sum, Count, Largest (#), Smallest (#), and Confidence Level.

Histogram analysis tool

This analysis tool calculates individual and cumulative frequencies for a cell range of data and data bins. This tool generates data for the number of occurrences of a value in a data set. For example, in a class of 20 students, you could determine the distribution of scores in letter-grade categories. A histogram table presents the letter-grade boundaries and the number of scores between the lowest bound and the current bound. The single most-frequent score is the mode of the data.

Summary functions for data analysis

Summary functions are used in automatic subtotals, data consolidations, and PivotTable and PivotChart reports. In PivotTable and PivotChart reports, the following summary functions are available for those that are based on non-OLAP source data. For reports that are based on OLAP data, the available summary functions depend on how the cube was defined on the server. For information about summary fields available for your source data, contact your OLAP database administrator.

F-Test: Two-Sample for Variances analysis tool

This analysis tool performs a two-sample F-test to compare two population variances. For example, you can use an F-test to determine whether the time scores in a swimming meet have a difference in variance for samples from two teams.

Anova: Single Factor analysis tool

This analysis tool performs simple analysis of variance (anova) to test the hypothesis that means from two or more samples are equal (drawn from populations with the same mean). This technique expands on the tests for two means, such as the t-test.

Correlation analysis tool and formulas

This analysis tool and its formulas measure the relationship between two data sets that are scaled to be independent of the unit of measurement. The population correlation calculation returns the covariance of two data sets divided by the product of their standard deviations:

You can use the Correlation tool to determine whether two ranges of data move together — that is, whether large values of one set are associated with large values of the other (positive correlation), whether small values of one set are associated with large values of the other (negative correlation), or whether values in both sets are unrelated (correlation near zero). 

Note   To return the correlation coefficient for two cell ranges, use the CORREL worksheet function.

Regression analysis tool

This analysis tool performs linear regression analysis by using the "least squares" method to fit a line through a set of observations. You can analyze how a single dependent variable is affected by the values of one or more independent variables — for example, how an athlete's performance is affected by such factors as age, height, and weight. You can apportion shares in the performance measure to each of these three factors, based on a set of performance data, and then use the results to predict the performance of a new, untested athlete.

Input Y Range

Enter the reference for the range of dependent data. The range must consist of a single column of data.

Input X Range

Enter the reference for the range of independent data. Microsoft Excel orders independent variables from this range in ascending order from left to right. The maximum number of independent variables is 16.

Labels

Select if the first row or column of your input range or ranges contains labels. Clear if your input has no labels; Microsoft Excel generates appropriate data labels for the output table.

Confidence Level

Select to include an additional level in the summary output table. In the box, enter the confidence level you want applied in addition to the default 95 percent level.

Constant is Zero

Select to force the regression line to pass through the origin.

Output Range

Enter the reference for the upper-left cell of the output table. Allow at least seven columns for the summary output table, which includes an anova table, coefficients, standard error of y estimate, r2 values, number of observations, and standard error of coefficients.

New Worksheet Ply

Click to insert a new worksheet in the current workbook and paste the results starting at cell A1 of the new worksheet. To name the new worksheet, type a name in the box.

New Workbook

Click to create a new workbook and paste the results on a new worksheet in the new workbook.

Residuals

Select to include residuals in the residuals output table.

Standardized Residuals

Select to include standardized residuals in the residuals output table.

Residual Plots

Select to generate a chart for each independent variable versus the residual.

Line Fit Plots

Select to generate a chart for predicted values versus the observed values.

Normal Probability Plots

Select to generate a chart that plots normal probability.