About statistical analysis tools

Analysis ToolPak add-in Microsoft Excel provides a set of data analysis tools — called the Analysis ToolPak — that you can use to save steps when you develop complex statistical or engineering analyses. You provide the data and parameters for each analysis; the tool uses the appropriate statistical or engineering macro functions and then displays the results in an output table. Some tools generate charts in addition to output tables.

Available tools To view a list of available analysis tools, click Data Analysis on the Tools menu. If the Data Analysis command is not on the Tools menu, you need to install the Analysis ToolPak. How to install and use the Analysis ToolPak.

Prerequisite knowledge To use these tools, you need to be familiar with the specific area of statistics or engineering that you want to develop analyses for.

Related worksheet functions Excel provides many other statistical, financial, and engineering worksheet functions. Some of the statistical functions are built-in and others become available when you install the Analysis ToolPak. View a list of the available statistical functions, a list of the available financial functions, or a list of the available engineering functions.

More information

Perform an analysis of variance (anova)

Correlation analysis tool

Covariance analysis tool

Descriptive Statistics analysis tool

Exponential Smoothing analysis tool

Fourier Analysis tool

F-Test: Two-Sample for Variances analysis tool

Histogram analysis tool

Moving Average analysis tool

Perform a t-Test analysis

Random Number Generation analysis tool

Rank and Percentile analysis tool

Regression analysis tool

Sampling analysis tool

z-Test: Two Sample for Means analysis tool


Install and use the Analysis ToolPak

Before using an analysis tool, you must arrange the data you want to analyze in columns or rows on your worksheet. This is your input range.

If the Data Analysis command is not on the Tools menu, you need to install the Analysis ToolPak in Microsoft Excel.

To install the Analysis ToolPak

  1. On the Tools menu, click Add-Ins.

    If Analysis ToolPak is not listed in the Add-Ins dialog box, click Browse and locate the drive, folder name, and file name for the Analysis ToolPak add-in, Analys32.xll — usually located in the Microsoft Office\Office\Library\Analysis folder — or run the Setup program if it isn't installed.

  2. Select the Analysis ToolPak check box.

To use the Analysis ToolPak

  1. On the Tools menu, click Data Analysis.

  2. In the Analysis Tools box, click the tool you want to use.

  3. Enter the input range and the output range, and then select the options you want.

Note Add-ins you select in the Add-Ins dialog box remain active until you remove them. How to remove an add-in.


Supplemental information about statistical methods and algorithms

For detailed information about the algorithms used to create the Microsoft Excel analysis tools and functions, see the following book:

  • Strum, Robert D., and Donald E. Kirk. First Principles of Discrete Systems and Digital Signal Processing. Reading, Mass.: Addison-Wesley Publishing Company, 1988.

For detailed information about statistical methods or the algorithms used to create the Microsoft Excel statistical tools and functions, see the following books:

  • Abramowitz, Milton, and Irene A. Stegun, eds. Handbook of Mathematical Functions, with Formulas, Graphs, and Mathematical Tables. Washington, D.C.: U.S. Government Printing Office, 1972.

  • Box, George E.P., William G. Hunter, and J. Stuart Hunter. Statistics for Experimenters: An Introduction to Design, Data Analysis, and Model Building. New York: John Wiley and Sons, 1978.

  • Devore, Jay L. Probability and Statistics for Engineering and the Sciences. 4th ed. Wadsworth Publishing, 1995.

  • McCall, Robert B. Fundamental Statistics for the Behavioral Sciences. 5th ed. New York: Harcourt Brace Jovanovich, 1990.

  • Press, William H., Saul A. Teukolsky, William T. Vetterling, and Brian P. Flannery. Numerical Recipes in C: The Art of Scientific Computing. 2nd ed. New York: Cambridge University Press, 1992.


Perform an analysis of variance (anova)

This tool is a part of the Analysis ToolPak. For information about how to install and use the Analysis ToolPak, click [image] .

The Analysis ToolPak provides three tools that you can use to analyze variance. The tool to use depends on the number of factors and the number of samples you have from the populations you want to test.

What do you want to learn about?

Anova: Single Factor analysis

Anova: Two-Factor With Replication analysis

Anova: Two-Factor Without Replication analysis


Correlation analysis tool and formulas

This tool is a part of the Analysis ToolPak. For information about how to install and use the Analysis ToolPak, click [image] .

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:

Formulas to calculate correlations

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). For more information about options in the Correlation dialog box, click [image] .

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


Covariance analysis tool and formula

This tool is a part of the Analysis ToolPak. For information about how to install and use the Analysis ToolPak, click [image] .

This analysis tool and its formula return the average of the product of deviations of data points from their respective means. Covariance is a measure of the relationship between two ranges of data.

Formula to calculate covariance

You can use the Covariance 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 covariance), whether small values of one set are associated with large values of the other (negative covariance), or whether values in both sets are unrelated (covariance near zero). For more information about options in the Covariance dialog box, click [image] .

Note To return the covariance for individual data point pairs, use the COVAR worksheet function.


Descriptive Statistics analysis tool

This tool is a part of the Analysis ToolPak. For information about how to install and use the Analysis ToolPak, click [image] .

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. For more information about options in the Descriptive Statistics dialog box, click [image] .


Exponential Smoothing analysis tool and formula

This tool is a part of the Analysis ToolPak. For information about how to install and use the Analysis ToolPak, click [image] .

This analysis tool and its formula predict a value based on the forecast for the prior period, adjusted for the error in that prior forecast. The tool uses the smoothing constant a, the magnitude of which determines how strongly forecasts respond to errors in the prior forecast.

Formula for exponential smoothing

For more information about options in the Exponential Smoothing dialog box, click [image] .

Note Values of 0.2 to 0.3 are reasonable smoothing constants. These values indicate that the current forecast should be adjusted 20 to 30 percent for error in the prior forecast. Larger constants yield a faster response but can produce erratic projections. Smaller constants can result in long lags for forecast values.


F-Test: Two-Sample for Variances analysis tool

This tool is a part of the Analysis ToolPak. For information about how to install and use the Analysis ToolPak, click [image] .

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. For more information about options in the F-Test: Two-Sample for Variances dialog box, click [image] .


Fourier Analysis tool

This tool is a part of the Analysis ToolPak. For information about how to install and use the Analysis ToolPak, click [image] .

This analysis tool solves problems in linear systems and analyzes periodic data by using the Fast Fourier Transform (FFT) method to transform data. This tool also supports inverse transformations, in which the inverse of transformed data returns the original data. For more information about options in the Fourier Analysis dialog box, click [image] .

Input and output ranges for Fourier analysis


Histogram analysis tool

This tool is a part of the Analysis ToolPak. For information about how to install and use the Analysis ToolPak, click [image] .

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. For more information about options in the Histogram dialog box, click [image] .


Moving Average analysis tool and formula

This tool is a part of the Analysis ToolPak. For information about how to install and use the Analysis ToolPak, click [image] .

This analysis tool and its formula project values in the forecast period, based on the average value of the variable over a specific number of preceding periods. Each forecast value is based on the following formula.

Formula to calculate moving averages

where

  • N is the number of prior periods to include in the moving average

  • Aj is the actual value at time j

  • Fj is the forecasted value at time j

A moving average provides trend information that a simple average of all historical data would mask. Use this tool to forecast sales, inventory, or other trends. For more information about options in the Moving Average dialog box, click [image] .


Random Number Generation analysis tool

This tool is a part of the Analysis ToolPak. For information about how to install and use the Analysis ToolPak, click [image] .

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. For more information about options in the Random Number Generation dialog box, click [image] .

Note To return an evenly distributed random number greater than or equal to 0 and less than 1 every time the worksheet is calculated, use the RAND worksheet function. To return a random number between numbers you specify every time the worksheet is calculated, use the RANDBETWEEN worksheet function.


Rank and Percentile analysis tool

This tool is a part of the Analysis ToolPak. For information about how to install and use the Analysis ToolPak, click [image] .

This analysis tool produces a table that contains the ordinal and percentage rank of each value in a data set. You can analyze the relative standing of values in a data set. For more information about options in the Rank and Percentile dialog box, click [image] .


Regression analysis tool

This tool is a part of the Analysis ToolPak. For information about how to install and use the Analysis ToolPak, click [image] .

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. For more information about options in the Regression dialog box, click [image] .


Sampling analysis tool

This tool is a part of the Analysis ToolPak. For information about how to install and use the Analysis ToolPak, click [image] .

This analysis tool creates a sample from a population by treating the input range as a population. When the population is too large to process or chart, you can use a representative sample. You can also create a sample that contains only values from a particular part of a cycle if you believe that the input data is periodic. For example, if the input range contains quarterly sales figures, sampling with a periodic rate of four places values from the same quarter in the output range. For more information about options in the Sampling dialog box, click [image] .


Perform a t-Test analysis

This tool is a part of the Analysis ToolPak. For information about how to install and use the Analysis ToolPak, click [image] .

The Analysis ToolPak provides three tools that you can use to test the means of different types of populations.

What do you want to learn about?

t-Test: Two-Sample Assuming Equal Variances analysis

t-Test: Two-Sample Assuming Unequal Variances analysis

t-Test: Paired Two Sample For Means analysis


z-Test: Two Sample for Means analysis tool

This tool is a part of the Analysis ToolPak. For information about how to install and use the Analysis ToolPak, click [image] .

This analysis tool performs a two-sample z-test for means with known variances. This tool is used to test hypotheses about the difference between two population means. For example, you can use this test to determine differences between the performances of two car models. For more information about options in the z-Test: Two Sample for Means dialog box, click [image] .


Data appears only on the first worksheet.

The data analysis functions can be used on only one worksheet at a time. When you perform data analysis on grouped worksheets, results will appear on the first worksheet and empty formatted tables will appear on the remaining worksheets. To perform data analysis on the remainder of the worksheets, recalculate the analysis tool for each worksheet.


 Template Wizard - Step 1 of 5

Template Wizard - Step 1 of 5

The Template Wizard with Data Tracking helps you create a template you can use to enter data in a database that you link to the template. The database can be a Microsoft Excel workbook or a Microsoft Access, Microsoft FoxPro, dBASE, or Paradox database. When you or others in your group create a new workbook based on the template, you can save the information entered in the workbook as a new record in the database.

To create the template, first open an existing workbook that's organized and formatted so that you can easily fill in the data you want entered in the database. If you have already created a form template and want to use the Template Wizard to link the template to a database, click Cancel in the Template Wizard dialog box. Click New on the File menu to create a new blank workbook based on your form template. Then restart the Template Wizard by clicking Template Wizard on the Data menu. The workbook you use to create the template remains unchanged.

Select the name of the workbook you want to create a template from

All open workbooks appear in the list. If you don't see the workbook you want, click Cancel, open the workbook, and then restart the Template Wizard.

Type a name for the template

If you want the template and workbook names to be different, type a new name. Microsoft Excel will automatically include the .xlt file name extension. By default, the template is stored in the Templates folder of your Microsoft Office folder or Microsoft Excel folder. To store the template in a subfolder of the Templates folder, include the subfolder name in the path.


 Template Wizard - Step 2 of 5

Template Wizard - Step 2 of 5

In this step, you specify the database that you want to link to the template. You can start a new database or use an existing database. If you use an existing database, the categories of data you want to copy to the database must correspond to the existing database fields.

Select the type of database you want the wizard to create

Click the type of database you want to use. The database can be a Microsoft Excel workbook or a Microsoft Access, Microsoft FoxPro, dBASE, or Paradox database. The appropriate ODBC driver must be installed and registered on your system.

Type the location and name of the database

To start a new database, type a path and a file name. To add information to an existing database, click Browse, and then locate the database file you want.

Note If you use the Template Wizard to create a new database, the database can contain a single table or worksheet. To track data in multiple tables or sheets, first use your database program to create the database with the desired tables or sheets; the database can contain up to 25 tables or sheets. Then use the Template Wizard in Microsoft Excel to link the workbook cells to the fields in the appropriate table or sheet.


 Template Wizard - Step 3 of 5

Template Wizard - Step 3 of 5

In this step, you identify each cell you want to copy data from and the corresponding database field the data is copied to. The set of data saved in each workbook based on the template creates one new record in the database.

Sheet or Table

Identifies the sheet or table in the database where you want the data copied. If you're creating a new database, you can type a new sheet or table name. If you're using an existing database that has multiple sheets or tables, click the item you want.

No.

Identifies the field, numbered from left to right, in the database to which data will be copied from the selected cells.

Cell

Identifies each cell that will contain data you want copied to the database. To enter a cell reference, first click in the Cell box, and then click the cell on the worksheet. The reference must be an absolute reference to a single cell. You can enter cell references from multiple worksheets; however, you must include the sheet name — for example, 'Sheet 1'!$B$3 — if your template contains more than one worksheet.

Field Name

Identifies the database field that is linked to each cell you select on the worksheet. If you're creating a new database and the selected cell is below or to the right of a text label, click in the Field Name box to enter the label as the field name, or type another name. The field name must meet the requirements for field names in the selected database.

If you selected an existing database, the field names from the database automatically appear — you cannot change the names.


 Template Wizard - Step 4 of 5

Template Wizard - Step 4 of 5

You might have a number of workbooks that contain data similar to data in the active workbook. If the cell locations, including sheet names, of the data in these workbooks are identical to the cell references you specified in the preceding step, Microsoft Excel can collect the data from the various workbooks and add it to the database. If the cell locations are not identical, blank or incorrect records might be entered in the database. Click the option you want, and then click Next.


 Template Wizard - Step 4 of 5

Template Wizard - Step 4 of 5

In this step, you select the existing workbooks that contain information you want to copy to the database. Use the Preview area to make sure that the values from the workbooks will be copied to the correct database fields.

Select

To add a workbook to the Files to Convert box, click Select, and then locate the workbook you want. If the active workbook you are using to create the template also contains data you want to include in the database, select the active workbook as well.

To select multiple workbooks in the Select Files to Convert dialog box, hold down CTRL and click each workbook. To select a series of adjacent files, hold down SHIFT and click the first and last files in the series.

Delete

To remove a workbook from the Files to convert box, click the workbook, and then click Delete.

Preview

To make sure that data in a workbook will be copied to the correct database fields, click the workbook in the Files to convert box, and then click Preview. If data appears in the wrong fields, click Delete to remove the file from the Files to convert box.

Preview area

Displays the data that Microsoft Excel will copy from the selected workbook to the database fields. Use the scroll bar to view all of the fields.

Table

Identifies the sheet or table in the database that contains the fields linked to the cells. If you're using an existing database that has multiple sheets or tables, click the item you want.


 Template Wizard - Step 5 of 5

Template Wizard - Step 5 of 5

Your new template is ready to use. The template is saved in the Templates folder of your Microsoft Office or Microsoft Excel folder. To create a new workbook based on the template, click New on the File menu.

To change the template — for example, to copy data from additional workbook cells to the database or to change the location of cells in which you enter data — click Open on the File menu, open the template, and then click Template Wizard on the Data menu.

You can change the data entered in a particular record of the database by opening and editing the workbook associated with the record. When you save the edited workbook, you can either update the existing record or save the edited information as a new record, leaving the existing record intact. You can also edit a record directly in the database program. To delete a record, you must delete the record in the database program; deleting the workbook associated with a record does not delete the record.

Add Routing Slip

To automatically route a new workbook created from the template to selected recipients, click Add Routing Slip, and then select the information service you want to use. The service you select must support Microsoft Exchange.


AutoSave dialog box options

Automatic save every

Select to save workbooks automatically at specific intervals. In the Minutes box, specify how often you want to save workbooks, from 1 to 120 minutes. Clear the check box to stop saving workbooks automatically.

Save active workbook only

Automatically saves only the active workbook.

Save all open workbooks

Automatically saves all open workbooks.

Prompt before saving

Select to display a prompt that allows you to perform or skip each automatic save.


 About the Report Manager command (View menu)

About the Report Manager command (View menu)

Combines a sequence of sheets, views, and scenarios to create a report that you can print.

Reports

Click a report in the Reports box to print, edit, or delete the report.

Print

Click Print to print the selected report.

Add

Click Add to create a new report.

Edit

Click Edit to edit the selected report.

Delete

Click Delete to delete the selected report.


 About the Add Report and Edit Report dialog boxes

About the Add Report and Edit Report dialog boxes

In the Add Report dialog box, you create new reports. In the Edit Report dialog box, you edit existing reports. You can name reports, create and delete report sections, change the order of sections, and number report sections consecutively.

Report Name

Type a name in the Report Name box to create a name for a report.

Section to Add

Select the options you want under Section to Add to create a section for a report.

Sheet

Click a sheet in the Sheet box to specify the sheet in the active workbook you want for the section.

View

Click a view in the View box to add it to the section.

Scenario

Click a scenario in the Scenario box to add it to the section.

Add

Click Add to add the current section to the Sections in this Report box.

Sections in this Report

Lists the sections in the current report. Microsoft Excel prints sections in the order in which they're listed.

Move Up

Moves the selected section so that it is printed before other sections.

Move Down

Moves the selected section so that it is printed after other sections.

Use Continuous Page Numbers

Select to number pages consecutively within a report. If this option is not selected, Microsoft Excel restarts the numbering at 1 in each section of the report.

Delete

Click Delete to delete the section selected in the Sections in this Report box.


Update Add-in Links

Use this command to update links to built-in features of Microsoft Excel 2000 that were add-ins in earlier versions of Excel. To update references in only the active workbook, click Active document. To update references in all open workbooks, click All open documents.


About the Add Constraint and Change Constraint dialog boxes

Cell reference

Specifies the cell or range of cells where you want to constrain the values.

Constraint

Specifies a restriction on the contents of the Cell reference box. Select the relationship you want to add or change ( <=, =, >=, Int, or Bin ) between the referenced cell and the constraint. Then enter the constraint — a number, cell or range reference, or formula — in the box to the right.

Add

Click to add another constraint without returning to the Solver Parameters dialog box.


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. For more information about options in the Anova: Single Factor dialog box, click [image] .


About the Anova: Single Factor dialog box

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.

Alpha

Enter the level at which you want to evaluate critical values for the F statistic. The alpha level is a significance level related to the probability of having a type I error (rejecting a true hypothesis).

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 or extend beyond the bounds of the worksheet.

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.


Anova: Two-Factor Without Replication analysis tool

This analysis tool performs a two-factor anova that does not include more than one sampling per group, testing the hypothesis that means from two or more samples are equal (drawn from populations with the same mean). This technique expands on tests for two means, such as the t-test. For more information about options in the Anova: Two-Factor Without Replication dialog box, click [image] .


About the Anova: Two-Factor Without Replication dialog box

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.

Labels

This check box is clear if your input range has no labels; Microsoft Excel generates appropriate data labels for the output table.

Alpha

Enter the level at which you want to evaluate critical values for the F statistic. The alpha level is a significance level related to the probability of having a type I error (rejecting a true hypothesis).

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 or extend beyond the bounds of the worksheet.

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.


Anova: Two-Factor With Replication analysis tool

This analysis tool performs an extension of the single-factor anova to include more than one sample for each group of data. For more information about options in the Anova: Two-Factor With Replication dialog box, click [image] .

Input range setup for anova tool


About the Anova: Two-Factor With Replication dialog box

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.

Rows per sample

Enter the number of rows contained in each sample. Each sample must contain the same number of rows, because each row represents a replication of the data.

Alpha

Enter the level at which you want to evaluate critical values for the F statistic. The alpha level is a significance level related to the probability of having a type I error (rejecting a true hypothesis).

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 or extend beyond the bounds of the worksheet.

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.


About the Correlation dialog box

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.

Output Range

Enter the reference for the upper-left cell of the output table. Microsoft Excel populates only half of the table because correlation between two ranges of data is independent of the order in which the ranges are processed. Cells in the output table with matching row and column coordinates contain the value 1 because each data set correlates exactly with itself.

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.


About the Covariance dialog box

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

Output Range

Enter the reference for the upper-left cell of the output table. Microsoft Excel populates only half of the table because covariance between two ranges of data is independent of the order in which the ranges are processed. The table's diagonal contains the variance of each range.

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.


About the Descriptive Statistics dialog box

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.


About the Exponential Smoothing dialog box

Input Range

Enter the cell reference for the range of data you want to analyze. The range must contain a single column or row with four or more cells of data.

Damping factor

Enter the damping factor you want to use as the exponential smoothing constant. The damping factor is a corrective factor that minimizes the instability of data collected across a population. The default damping factor is 0.3.

Labels

Select if the first row and column of your input range contain labels. Clear this check box if your input range has no labels; Microsoft Excel generates appropriate data labels for the output table.

Output Range

Enter the reference for the upper-left cell of the output table. If you select the Standard Errors check box, Microsoft Excel generates a two-column output table with standard error values in the right column. If there are insufficient historical values to project a forecast or calculate a standard error, Microsoft Excel returns the #N/A error value.

Note The output range must be on the same worksheet as the data used in the input range. For this reason, the New Worksheet Ply and New Workbook options are unavailable.

Chart Output

Select to generate an embedded chart for the actual and forecast values in the output table.

Standard Errors

Select if you want to include a column that contains standard error values in the output table. Clear if you want a single-column output table without standard error values.


About the Fourier Analysis dialog box

Input Range

Enter the reference for the range of real or complex data you want to transform. Complex data must be in either x+yi or x+yj format. The number of input range values must be an even power of 2. If x is a negative number, precede it with an apostrophe ( ' ). The maximum number of values is 4096.

Labels in First Row

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

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.

Inverse

If selected, the data in the input range is considered transformed and an inverse transformation is performed, returning the original inputs. If cleared, the data in the input range is transformed in the output table.


About the F-Test: Two-Sample for Variances dialog box

Variable 1 Range

Enter the reference for the first column or row of data you want to analyze.

Variable 2 Range

Enter the reference for the second column or row of data you want to analyze.

Labels

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

Alpha

Enter the confidence level for the test. This value must be in the range 0...1. The alpha level is a significance level related to the probability of having a type I error (rejecting a true hypothesis).

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.


About the Histogram dialog box

Input Range

Enter the reference for the range of data you want to analyze.

Bin Range (optional)

Enter the cell reference to a range that contains an optional set of boundary values that define bin ranges. These values should be in ascending order. Microsoft Excel counts the number of data points between the current bin number and the adjoining higher bin, if any. A number is counted in a particular bin if it is equal to or less than the bin number down to the last bin. All values below the first bin value are counted together, as are the values above the last bin value.

If you omit the bin range, Microsoft Excel creates a set of evenly distributed bins between the data's minimum and maximum values.

Labels

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

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.

Pareto (sorted histogram)

Select to present data in the output table in descending order of frequency. If this check box is cleared, Microsoft Excel presents the data in ascending order and omits the three rightmost columns that contain the sorted data.

Cumulative Percentage

Select to generate an output table column for cumulative percentages and to include a cumulative percentage line in the histogram chart. Clear to omit the cumulative percentages.

Chart Output

Select to generate an embedded histogram chart with the output table.


About the Moving Average dialog box

Input Range

Enter the cell reference for the range of data you want to analyze. The range must consist of a single column that contains four or more cells of data.

Labels in First Row

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

Interval

Enter the number of values you want to include in the moving average. The default interval is 3.

Output Range

Enter the reference for the upper-left cell of the output table. If you select the Standard Errors check box, Microsoft Excel generates a two-column output table with standard error values in the right column. If there are insufficient historical values to project a forecast or calculate a standard error, Microsoft Excel returns the #N/A error value.

The output range must be on the same worksheet as the data used in the input range. For this reason, the New Worksheet Ply and New Workbook options are unavailable.

Chart Output

Select to generate an embedded histogram chart with the output table.

Standard Errors

Select if you want to include a column that contains standard error values in the output table. Clear if you want a single-column output table without standard error values.


About the Random Number Generation dialog box

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.


About the Rank and Percentile dialog box

Input Range

Enter the cell reference for the range of worksheet data you want to analyze.

Grouped By

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

Labels in First Row/Labels in First Column

If you click Columns and the first row of your input range contains labels, select the Labels in First Row check box. If you click Rows and the first column of your input range contains labels, select the Labels in First Column check box. Clear the check box if your input range has no labels; Microsoft Excel generates appropriate row and column labels for the output table.

Output Range

Enter the reference for the upper-left cell of the output table. Microsoft Excel generates one output table for each data set in the input range. Each output table contains four columns: the data point number, the data point value, the data point rank, and the data point percentage rank, sorted in ascending rank order.

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.


About the Regression dialog box

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.


About the Sampling dialog box

Input Range

Enter the references for the range of data that contains the population of values you want to sample. Microsoft Excel draws samples from the first column, then the second column, and so on.

Labels

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

Sampling Method

Click Periodic or Random to indicate the sampling interval you want.

Period

Enter the periodic interval at which you want sampling to take place. The period-th value in the input range and every period-th value thereafter is copied to the output column. Sampling stops when the end of the input range is reached.

Number of Samples

Enter the number of random values you want in the output column. Each value is drawn from a random position in the input range, and any number can be selected more than once.

Output Range

Enter the reference for the upper-left cell of the output table. Data is written in a single column below the cell. If you select Periodic, the number of values in the output table is equal to the number of values in the input range, divided by the sampling rate. If you select Random, the number of values in the output table is equal to the number of samples.

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.


About the Save Model and Load Model dialog boxes

Select Model Area

In the Save Model dialog box, displays the default selection, based on the active cell, as the area where you want to save the problem model. The suggested range includes a cell for each constraint plus three additional cells. You can also enter a reference for the upper-left cell of the vertical range in which you want the model to appear.

In the Load Model dialog box, displays the reference for the model you want to load. The reference must be for the entire range that contains the model, not just the first cell of the range.


About the Show Trial Solution and Save Scenario dialog boxes

Save Scenario

Displays the Save Scenario dialog box, where you can save cell values to use with the Microsoft Excel Scenario Manager. If you have not defined a scenario, the Scenario Manager uses the reference in the By Changing Cells box on the Solver Parameters dialog box.

In the Save Scenario dialog box, type a name for the scenario you want to save in the Scenario name box, and click OK.

Continue

Continues the solution process.

Stop

Displays the Solver Results dialog box without completing the solution process.


About the Solver Options dialog box

You can control advanced features of the solution process, load or save problem definitions, and define parameters for both linear and nonlinear problems. Each option has a default setting that is appropriate for most problems.

Max time

Limits the time taken by the solution process. While you can enter a value as high as 32,767, the default value of 100 (seconds) is adequate for most small problems.

Iterations

Limits the time taken by the solution process by limiting the number of interim calculations. While you can enter a value as high as 32,767, the default value of 100 is adequate for most small problems.

Precision

Controls the precision of solutions by using the number you enter to determine whether the value of a constraint cell meets a target or satisfies a lower or upper bound. Precision must be indicated by a fractional number between 0 (zero) and 1. Higher precision is indicated when the number you enter has more decimal places — for example, 0.0001 is higher precision than 0.01.

Tolerance

The percentage by which the target cell of a solution satisfying the integer constraints can differ from the true optimal value and still be considered acceptable. This option applies only to problems with integer constraints. A higher tolerance tends to speed up the solution process.

Convergence

When the relative change in the target cell value is less than the number in the Convergence box for the last five iterations, Solver stops. Convergence applies only to nonlinear problems and must be indicated by a fractional number between 0 (zero) and 1. A smaller convergence is indicated when the number you enter has more decimal places — for example, 0.0001 is less relative change than 0.01. The smaller the convergence value, the more time Solver takes to reach a solution.

Assume Linear Model

Select to speed the solution process when all relationships in the model are linear and you want to solve a linear optimization problem.

Show Iteration Results

Select to have Solver pause to show the results of each iteration.

Use Automatic Scaling

Select to use automatic scaling when inputs and outputs have large differences in magnitude — for example, when maximizing the percentage of profit based on million-dollar investments.

Assume Non-Negative

Causes Solver to assume a lower limit of 0 (zero) for all adjustable cells for which you have not set a lower limit in the Constraint box in the Add Constraint dialog box.

Estimates

Specifies the approach used to obtain initial estimates of the basic variables in each one-dimensional search.

Tangent Uses linear extrapolation from a tangent vector.

Quadratic Uses quadratic extrapolation, which can improve the results on highly nonlinear problems.

Derivatives

Specifies the differencing used to estimate partial derivatives of the objective and constraint functions.

Forward Use for most problems, in which the constraint values change relatively slowly.

Central Use for problems in which the constraints change rapidly, especially near the limits. Although this option requires more calculations, it might help when Solver returns a message that it could not improve the solution.

Search

Specifies the algorithm used at each iteration to determine the direction to search.

Newton Uses a quasi-Newton method that typically requires more memory but fewer iterations than the Conjugate gradient method.

Conjugate Requires less memory than the Newton method but typically needs more iterations to reach a particular level of accuracy. Use this option when you have a large problem and memory usage is a concern, or when stepping through iterations reveals slow progress.

Load Model

Displays the Load Model dialog box, where you can specify the reference for the model you want to load.

Save Model

Displays the Save Model dialog box, where you can specify where to save the model. Click only when you want to save more than one model with a worksheet — the first model is automatically saved.


About the Solver Parameters dialog box

Set Target Cell

Specifies the target cell that you want to set to a certain value or that you want to maximize or minimize. This cell must contain a formula.

Equal to

Specifies whether you want the target cell to be maximized, minimized, or set to a specific value. If you want a specific value, type it in the box.

By Changing Cells

Specifies the cells that can be adjusted until the constraints in the problem are satisfied and the cell in the Set Target Cell box reaches its target. The adjustable cells must be related directly or indirectly to the target cell.

Guess

Guesses all nonformula cells referred to by the formula in the Set Target Cell box, and places their references in the By Changing Cells box.

Subject to the Constraints

Lists the current restrictions on the problem.

Add

Displays the Add Constraint dialog box.

Change

Displays the Change Constraint dialog box.

Delete

Removes the selected constraint.

Solve

Starts the solution process for the defined problem.

Close

Closes the dialog box without solving the problem. Retains any changes you made by using the Options, Add, Change, or Delete buttons.

Options

Displays the Solver Options dialog box, where you can load and save problem models and control advanced features of the solution process.

Reset All

Clears the current problem settings, and resets all settings to their original values.


About the Solver Results dialog box

Displays a completion message and the resulting values that are nearest the solution you want. Learn about Solver completion messages.

Keep Solver Solution

Click to accept the solution and place the resulting values in the adjustable cells.

Restore Original Values

Click to restore the original values in the adjustable cells.

Reports

Creates the type of report you specify, and places each report on a separate sheet in the workbook.

Answer Lists the target cell and the adjustable cells with their original and final values, constraints, and information about the constraints.

Sensitivity Provides information about how sensitive the solution is to small changes in the formula in the Set Target Cell box in the Solver Parameters dialog box or the constraints. This report is not generated for models that have integer constraints. For nonlinear models, the report provides values for reduced gradients and Lagrange multipliers. For linear models, the report includes reduced costs, shadow prices, objective coefficient (with allowable increase and decrease), and constraint right-hand side ranges.

Limits Lists the target cell and the adjustable cells with their respective values, lower and upper limits, and target values. This report is not generated for models that have integer constraints. The lower limit is the smallest value that the adjustable cell can take while holding all other adjustable cells fixed and still satisfying the constraints. The upper limit is the greatest value.

Save Scenario

Opens the Save Scenario dialog box, where you can save cell values for use with the Microsoft Excel Scenario Manager. For more information about the Save Scenario dialog box, click [image] .


t-Test: Paired Two Sample for Means analysis tool and formula

This analysis tool and its formula perform a paired two-sample student's t-test to determine whether a sample's means are distinct. This t-test form does not assume that the variances of both populations are equal. You can use a paired test when there is a natural pairing of observations in the samples, such as when a sample group is tested twice — before and after an experiment. For more information about options in the t-Test: Paired Two-Sample for Means dialog box, click [image] .

Note Among the results generated by this tool is pooled variance, an accumulated measure of the spread of data about the mean, derived from the following formula.

Formula to calculate pooled variance


About the t-Test: Paired Two-Sample for Means dialog box

Variable 1 Range

Enter the cell reference for the first range of data you want to analyze. The range must consist of a single column or row and contain the same number of data points as the second range.

Variable 2 Range

Enter the cell reference for the second range of data you want to analyze. The range must consist of a single column or row and contain the same number of data points as the first range.

Hypothesized Mean Difference

Enter the number you want for the shift in sample means. A value of 0 (zero) indicates that the sample means are hypothesized to be equal.

Labels

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

Alpha

Enter the confidence level for the test. This value must be in the range 0...1. The alpha level is a significance level related to the probability of having a type I error (rejecting a true hypothesis).

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.


t-Test: Two-Sample Assuming Equal Variances analysis tool

This analysis tool performs a two-sample student's t-test. This t-test form assumes that the means of both data sets are equal; it is referred to as a homoscedastic t-test. You can use t-tests to determine whether two sample means are equal. For more information about options in the t-Test: Two-Sample Assuming Equal Variances dialog box, click [image] .


About the t-Test: Two-Sample Assuming Equal Variances dialog box

Variable 1 Range

Enter the cell reference for the first range of data you want to analyze. The range must consist of a single column or row of data.

Variable 2 Range

Enter the cell reference for the second range of data you want to analyze. The range must consist of a single column or row of data.

Hypothesized Mean Difference

Enter the number that you want for the shift in sample means. A value of 0 (zero) indicates that the sample means are hypothesized to be equal.

Labels

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

Alpha

Enter the confidence level for the test. This value must be in the range 0...1. The alpha level is a significance level related to the probability of having a type I error (rejecting a true hypothesis).

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.


t-Test: Two-Sample Assuming Unequal Variances analysis tool and formulas

This analysis tool and its formulas perform a two-sample student's t-test. This t-test form assumes that the variances of both ranges of data are unequal; it is referred to as a heteroscedastic t-test. You can use a t-test to determine whether two sample means are equal. Use this test when the groups under study are distinct. Use a paired test when there is one group before and after a treatment. For more information about options in the t-Test: Two-Sample Assuming Unequal Variances dialog box, click [image] .

The following formula is used to determine the test statistic value t.

Formula to calculate value t

The following formula is used to approximate the degrees of freedom. Because the result of the calculation is usually not an integer, use the nearest integer to obtain a critical value from the t table.

Formula to approximate degrees of freedom


About the t-Test: Two-Sample Assuming Unequal Variances dialog box

Variable 1 Range

Enter the cell reference for the first range of data you want to analyze. The range must consist of a single column or row of data.

Variable 2 Range

Enter the cell reference for the second range of data you want to analyze. The range must consist of a single column or row of data.

Hypothesized Mean Difference

Enter the number you want for the shift in sample means. A value of 0 (zero) indicates that the sample means are hypothesized to be equal.

Labels

Select if the first row or column of your input ranges contain labels. Clear this check box if your input ranges have no labels; Microsoft Excel generates appropriate data labels for the output table.

Alpha

Enter the confidence level for the test. This value must be in the range 0...1. The alpha level is a significance level related to the probability of having a type I error (rejecting a true hypothesis).

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.


About the z-Test: Two Sample for Means dialog box

Variable 1 Range

Enter the cell reference for the first range of data you want to analyze. The range must consist of a single column or row of data.

Variable 2 Range

Enter the cell reference for the second range of data you want to analyze. The range must consist of a single column or row of data.

Hypothesized Mean Difference

Enter the number you want for the shift in sample means. A value of 0 (zero) indicates that the sample means are hypothesized to be equal.

Variable 1 Variance (known)

Enter the known population variance for the Variable 1 input range.

Variable 2 Variance (known)

Enter the known population variance for the Variable 2 input range.

Labels

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

Alpha

Enter the confidence level for the test. This value must be in the range 0...1. The alpha level is a significance level related to the probability of having a type I error (rejecting a true hypothesis).

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.