DOC

guide_to_excels_data_analysis_tools

By Justin Carter,2014-08-19 14:40
11 views 0
guide_to_excels_data_analysis_tools

    GUIDE TO MICROSOFT? EXCEL’S

    DATA ANALYSIS TOOLS

    1. What Are the Data Analysis Tools? Excel includes a collection of 19 data analysis tools. These provide analysis capabilities beyond Excel’s inherent capabilities. One of them provides

    Fourier analysis for engineering applications. The remaining 18 provide statistical analyses. A discussion of the differences between these 18 statistical analysis tools and Excel’s 80 built-in

    statistical functions begins on the first page of guide to Excel’s Statistical Functions.

2. Where Can I Find the Data Analysis Tools? The data analysis tools are available in the

    component called the Analysis ToolPak that is an add-in program. Add-in programs are

    developed by organizations other than Microsoft and can be added to Excel to increase its capabilities. Microsoft includes a number of add-ins, such as the Analysis ToolPak, with the Excel software it distributes. These must be added in to Excel before they can be used within it.

3. Are the Data Analysis Tools Available on the Computer I am Using? The level of

    availability of the data analysis tools depends on the specific computer you are using. There are three possibilities.

a. The first possibility is that the Analysis ToolPak (1) has been installed and (2) has been

    added to the Tools pull-down menu. This means the data analysis tools are ready to use. To determine if this is your situation, click on Tools from the menu bar. If there is an entry

Excel’s Data Analysis Tools 2

usually near the bottom of the subsequent pull-down menu labeled Data Analysis…, the tools

    are ready to use. If it is not present, continue to the next possibility.

b. The second possibility is that the Analysis ToolPak (1) has been installed but (2) has

    not been added to the Tools menu. You must then add it to the menu. To determine if this is your situation and to add it to the menu, click on Tools from the menu bar. Then click on

    Add-ins… from the Tools pull-down menu. If the menu includes the selection Analysis

    ToolPak in the subsequent dialog box, you should click on the check box to its left to add a check mark to the box. (Note you are to add Analysis ToolPak, not Analysis ToolPak-

    VBA.) Next click on OK and the Analysis ToolPak will be added to the Tools menu and

    ready to use. If Analysis ToolPak is not an option on the add-ins dialog box, continue to the third possibility. Usually this will not be true if you are using Excel 2000 but could be if you

    are using Excel 97.

c. The third possibility is that the Analysis ToolPak (1) has not been installed and (2) has

    not been added. You must then first install it and second add it to the Tools menu. If this is your situation, you will need to exit from Excel and all other application programs and return to your computer’s opening screen. Get your original diskettes or CD-ROM’s for Microsoft

    Office (or just Excel) and run the Setup program for it. Put the first CD or first diskette for your Microsoft Office software in the appropriate drive.

(1) Click on the Start command button and click on Run from the subsequent menu.

    (2) The Run dialog box will appear on the screen. Assuming your CD or diskette is in drive D enter D:\SETUP in the text box and click on OK. (You may next get a dialog box indicating

    the Shortcut Bar is running. If so, click on OK.)

(3) The next dialog box will be labeled the Microsoft OfficeSet Up. Click on the

    Add/Remove command button.

(4) The next dialog box is labeled Microsoft OfficeMaintenance. Click to put a check

    mark in the Excel checkbox and to highlight Excel. Click on the Change Option button.

(5) The next dialog box is labeled Microsoft OfficeMicrosoft Excel. Click on the Add Ins

    button. Click on Change Option.

(6) The next dialog box is labeled Microsoft OfficeAdd-Ins. Click to put a check mark in

    the Analysis ToolPak (not Analysis ToolPakVBA) check box and to highlight Analysis

    ToolPak. Click on OK.

    The installation program will now install the Analysis ToolPak. You can then start Excel. You will next need to add it to the Tools menu. Select Tools, then Add-Ins and put a check

Excel’s Data Analysis Tools 3

mark in the Analysis ToolPak check box and select OK. The Data Analysis Tools will now

    be available from the Tools pull-down menu.

4. How Do I Use the Data Analysis Tools? Once you have the Analysis ToolPak installed

    and it has been added to the Tools pull-down menu, you are ready to use these analysis tools.

    First click on Tools from the menu bar and then click on Data Analysis on the subsequent pull-

    down menu. The result will be the Data Analysis dialog box with a scrolling list of the 19 data

    analysis tools. You then click on the name of the tool you wish to use and click on OK. A dialog

    box will then appear which allows you to enter the input range, output options and any other options required for the tool you have selected. If you are uncertain of an entry for a tool’s dialog box, first click on Help (this is the Help in the tool’s dialog box, not the Help in the prior Data

    Analysis dialog box with the scrolling list of the 19 tools). Once the tool’s dialog box is complete, click on OK and the output will appear.

5. For What Analyses Are the Data Analysis Tools Used? A presentation of the uses

    of the data analysis tools is perhaps most helpful if it is given by category of analysis. Accordingly, the following list of the 18 statistical tools is by the type of statistical analysis each supports. The six categories used are Descriptive Statistics, Sampling, Hypothesis Testing, Analysis of Variance, Regression and Correlation, and Time Series Forecasting. The order of presentation of the six

    categories is the order of a typical business statistics textbook. Within each category the tools are listed in the order they appear in this manual. The entry for each of the 18 statistical tools provides its name and its purpose. Further description of these is available through the Excel help

    facility. To access it, select Tools from the menu bar, Data Analysis from the subsequent pull-

    down menu, select the tool you wish to use from the resulting scrolling list, click on OK and click

    on Help in the next dialog box.

DESCRIPTIVE STATISTICS

Histogramdetermines and graphs individual and cumulative frequencies for a one

    variable data set.

    Descriptive Statisticsgenerates a report of the values for 16 descriptive statistics such as the mean, median, mode, standard deviation, range, skewness and kurtosis for a one variable data set.

Excel’s Data Analysis Tools 4

    Rank and Percentileproduces a table of the ordinal and percentage rank of each value in a one variable data set.

SAMPLING

Random Number Generationgenerates random values drawn from one of six possible

    probability distributions (it also has a non-random selection called patterned).

    Samplingdraws a random sample from a specified population for a single variable. This is the one statistical data analysis tool not used within this manual.

HYPOTHESIS TESTING

t-Test: Two Sample Assuming Unequal Variancesperforms a t-test on two

    independent samples to determine if the difference in the two population means is equal to a specified value (such as zero). This test is conducted under the condition that the two population variances are not known to be equal.

t-Test: Two Sample Assuming Equal Variancesperforms a t-test on two independent

    samples to determine if the difference in the two population means is equal to a specified value (such as zero). This test is conducted under the condition that the two unknown population variances are known to be equal.

z-Test: Two Sample for Meansperforms a z-test on two independent samples to

    determine if the difference in the two population means is equal to a specified value (such as zero). This test is conducted under the condition that the values of the two population variances are known, or the sample sizes are large so the normal distribution can be used to approximate the t distribution.

t-Test: Paired Two Sample for Meansperforms a t-test on two paired (matched)

    samples to determine if the mean of the differences between the two population is equal to a specified values (such as zero).

F-Test: Two-Sample for Variancesperforms an F-test on two independent samples to

    determine if two population variances equal.

Excel’s Data Analysis Tools 5

ANALYSIS OF VARIANCE

    ANOVA: Single-Factorperforms a one-way analysis of variance to determine if two or more population means are equal. This test uses data from a completely randomized

    experimental design.

ANOVA: Two-Factor Without Replicationperforms a two-way analysis of variance

    without interaction to determine if two or more population means are equal. This test uses data from a randomized block experimental design.

ANOVA: Two-Factor With Replicationperforms a two-way analysis of variance with

    interaction to determine if two or more population means are equal. This test uses data from a two-way factorial experimental design.

REGRESSION AND CORRELATION

    Covariancecreates a table of covariance values for all possible pairs of 2 or more independent variables.

    Correlationcreates a table of correlation coefficients for all possible pairs of 2 or more independent variables.

    Regressionperforms a simple linear regression analysis or a multiple regression analysis with up to 16 independent variables.

TIME SERIES FORECASTING

    Moving Averageprojects a time series based on the moving averages smoothing method.

Exponential Smoothingprojects a time series based on the simple exponential

    smoothing method.

Report this document

For any questions or suggestions please email
cust-service@docsford.com