Download Introduction to statistical analysis and more Study Guides, Projects, Research Data Analysis & Statistical Methods in PDF only on Docsity!
A E 1 2 4 S T A T I S T I C A L A N A L Y S I S W I T H S O F T W A R E A P P L I C A T I O N
1
Introduction to Statistical
Analysis with MS Excel
This part explains how Microsoft Excel will be used for statistical
analysis. Microsoft Excel is a powerful software applications with
built-in functions, data representation features and Add-ins Data
Analysis ToolPak Kit for data analysis. The discussion on MS
Excel as the software application to be used must be discuss first for
the statistical analysis.
Learning Outcomes
At the end of the lectures, you should be able to:
- Familiarize students with formulas and functions in MS Excel
- Perform formulas/built-in functions and graphical representation of data in MS Excel.
- Installing MS Excel Add Ins Data Analysis ToolPak Kit for Statistics
At a Glance
Basically, we are using Microsoft Excel very often for data analysis tasks. Some use MS Excel for computations on sales, budgeting, charting and graphing data for presentation.
Excel is a spreadsheet application program from Microsoft and it is commonly used worldwide. When purchasing computers or laptops, it is pre-installed application with its Microsoft Operating System.
It is full-packed with calculation features and statistical functions. Additionally, Excel has a Data Analysis ToolPak for easy statistical analysis
Module
K E Y Objectives Assessment Exercise
A E 1 2 4 S T A T I S T I C A L A N A L Y S I S W I T H S O F T W A R E A P P L I C A T I O N
2
Microsoft Excel
Excel is a computer program used to create electronic spreadsheets.
Within Excel, users can organize data, create charts, and perform calculations.
Excel is a convenient program because it allows the user to create large spreadsheets, reference information from other spreadsheets, and it allows for better storage and modification of information.
Excel operates like other Microsoft (MS) Office programs and has many of the same functions and shortcuts of other MS programs
Overview of MS Excel
Microsoft Excel consists of workbooks. Within each workbook, there is an infinite number of worksheets.
Each worksheet contains columns and rows.
Where a column and a row intersect is called the cell. For example, cell B6 is located where column B and row 6 meet. You enter your data into the cells on the worksheet.
Figure 1. B6 Cell
The tabs at the bottom of the screen represent different worksheets within a workbook. You can use the scrolling buttons on the left to bring other worksheets into view.
A E 1 2 4 S T A T I S T I C A L A N A L Y S I S W I T H S O F T W A R E A P P L I C A T I O N
4
Figure 4. Formula Box
You can change the colors, borders, sizes, alignment, and font of a certain cell by going to the <Cell= option in the Format Menu.
Figure 5. Format Cells
You can change the view of your work so that it is page by page.
You can insert Headers and Footers to your work.
You can add comments about a specific cell for future reference.
A E 1 2 4 S T A T I S T I C A L A N A L Y S I S W I T H S O F T W A R E A P P L I C A T I O N
5
Entering Formulas
When entering numerical data, you can command Excel to do any mathematical function.
Start each formula with an equal sign (=). To enter the sameformulas for a range of cells, use the colon sign <:=
Figure 6. Entering Formula
ADDITION FORMULAS
To add cells together use the <+= sign.
To sum up a series of cells, highlight the cells, then click the auto sum button. The answer will appear at the bottom of the highlighted box.
SUBTRACTION FORMULAS
To subtract cells, use the <-= sign.
DIVISION FORMULAS
To divide cells, use the </= sign.
MULTIPLICATION FORMULAS
To multiply cells, use the <*= sign.
A E 1 2 4 S T A T I S T I C A L A N A L Y S I S W I T H S O F T W A R E A P P L I C A T I O N
7
To update your worksheets, you can use the find and replace action (under the Edit Menu).
Figure 8. Find and Replace
To change the order of worksheets, click and drag the worksheet tab to the desired order.
Figure 9. Move Around Excel
Graphs
A chart is a tool you can use in Excel to communicate data graphically. Charts allow your audience to see the meaning behind the numbers, and they make showing comparisons and trends much easier. In this lesson, you'll learn how to insert charts and modify them so they communicate information effectively.
A E 1 2 4 S T A T I S T I C A L A N A L Y S I S W I T H S O F T W A R E A P P L I C A T I O N
8
Types of Charts
Excel has a variety of chart types, each with its own advantages. Click the arrows to see some of the different types of charts available in Excel.
Figure 10. Column
Column charts use vertical bars to represent data. They can work with many different types of data, but they're most frequently used for comparing information.
Figure 11. Pie
Pie charts make it easy to compare proportions. Each value is shown as a slice of the pie, so it's easy to see which values make up the percentage of a whole.
Figure 12. Pie
A E 1 2 4 S T A T I S T I C A L A N A L Y S I S W I T H S O F T W A R E A P P L I C A T I O N
10
Figure 14. Title
Figure 15. Vertical Axis
A E 1 2 4 S T A T I S T I C A L A N A L Y S I S W I T H S O F T W A R E A P P L I C A T I O N
11
Figure 16. Data Series
Figure 17. Legend
A E 1 2 4 S T A T I S T I C A L A N A L Y S I S W I T H S O F T W A R E A P P L I C A T I O N
13
Figure 20. Types of Chart
- The chart will appear in the worksheet.
Figure 21. Chart in the Worksheet
A E 1 2 4 S T A T I S T I C A L A N A L Y S I S W I T H S O F T W A R E A P P L I C A T I O N
14
Chart Tools
Once you insert a chart, a set of chart tools arranged into three tabs will appear on the Ribbon. These are only visible when the chart is selected. You can use these three tabs to modify your chart.
Figure 22. Chart Tools
To change chart type:
- From the Design tab, click the Change Chart Type command. A dialog box appears.
Figure 23. Change Chart Type
A E 1 2 4 S T A T I S T I C A L A N A L Y S I S W I T H S O F T W A R E A P P L I C A T I O N
16
Do a two sample t-test to test whether the two treatment groups differ on X and Y.
Do a paired t-test to test whether X and Y are statistically different from each other.
Compare the number of subjects with each outcome by treatment group, using a chi-squared test.
All of these tasks are routine for a data set of this nature, and all of them could be easily done using any of the aobve listed statistical packages.
General Issues
Enable the Analysis ToolPak
The Data Analysis ToolPak is not installed with the standard Excel setup. Look in the Tools menu. If you do not have a Data Analysis item, you will need to install the Data Analysis tools. Search Help for "Data Analysis Tools" for instructions.
- Click Options in the Backstage View of the MS Excel
Figure 25. Options
A E 1 2 4 S T A T I S T I C A L A N A L Y S I S W I T H S O F T W A R E A P P L I C A T I O N
17
- Click Add Ins
- Click Analysis ToolPak
A E 1 2 4 S T A T I S T I C A L A N A L Y S I S W I T H S O F T W A R E A P P L I C A T I O N
19
- Click OK
Missing Values
A blank cell is the only way for Excel to deal with missing data. If you have any other missing value codes, you will need to change them to blanks.
Data Arrangement
Different analyses require the data to be arranged in various ways. If you plan on a variety of different tests, there may not be a single arrangement that will work. You will probably need to rearrange the data several ways to get everything you need.
Dialog Boxes
Choose Tools/Data Analysis, and select the kind of analysis you want to do. The typical dialog box will have the following items:
Input Range: Type the upper left and lower right corner cells. e.g. A1:B100. You can only choose adjacent rows and columns. Unless there is a checkbox for
A E 1 2 4 S T A T I S T I C A L A N A L Y S I S W I T H S O F T W A R E A P P L I C A T I O N
20
grouping data by rows or columns (and there usually is not), all the data is considered as one glop.
Labels - There is sometimes a box you can check off to indicate that the first row of your sheet contains labels. If you have labels in the first row, check this box, and your output MAY be labeled with your label. Then again, it may not.
Output location - New Sheet is the default. Or, type in the cell address of the upper left corner of where you want to place the output in the current sheet. New Worksheet is another option, which I have not tried. Ramifications of this choice are discussed below.
Other items, depending on the analysis.
