MsOffice.US
Home
Excel
FrontPage
Access
Word
PowerPoint
Outlook
Visio
Project
Publisher
InfoPath
Exchange
SharePoint
Commerce
Expression
Silverlight
Management
Software
Windows

  Home  |  Excel  | FrontPage Access Word | PowerPoint  | Outlook | Visio |  Project |  Publisher  |  InfoPath | Exchange  |  SharePoint Commerce  | Silverlight Database  | Data Warehouse  |  SQL  |  OO Design SQL_ServerMYSQL | PHP  | Oracle | VBA ASP.NET  | C programming | Java  |  Photoshop | HTMLDreamweaver | Web DesignExpression Web  | XML  | Java script  |  Security | UML | Flash Silverlight |UNIX | Networking s


  Home  |  Excel  | FrontPage Access Word | PowerPoint  | Outlook | Visio |  Project |  Publisher  |  InfoPath | Exchange  |  SharePoint Commerce  | Silverlight Database  | Data Warehouse  |  SQL  |  OO Design SQL_ServerMYSQL | PHP  | Oracle | VBA ASP.NET  | C programming | Java  |  Photoshop | HTMLDreamweaver | Web DesignExpression Web  | XML  | Java script  |  Security | UML | Flash Silverlight |UNIX | Networking 

Links

www.msoffice.us

dbmanagement.info

italiankitchenrecipes.com

relationshipadvices.info

www.coptics.info

 

 

 

 

 

   

Home Excel Topics
 

Analysis of Variance: One-Way ANOVA

 

 


 

 

■     The owner of my company, which publishes computer books, wants to know whether the position of our books in the computer book section of bookstores influences sales. More specifically, does it really matter whether the books are placed in the front, back,

or middle of the computer book section?

 

■     If I am determining whether populations have significantly different means, why is the technique called analysis of variance?

■     How can I use the results of one-way ANOVA for forecasting?

 

We often have several different groups of people or items and want to determine whether data about the groups differs significantly. Here are some examples:

 

■     Is there a significant difference in the length of time that four doctors keep mothers in the hospital after they give birth?

 

■     Does the production yield for a new drug depend on whether the size of the container

in which the drug is produced is large, small, or medium?

 

■     Does the drop in blood pressure attained after taking one of four drugs depend on the drug taken?

When youíre trying to determine whether the means in several sets of data that depend on one factor are significantly different, one-way analysis of variance, or ANOVA, is the correct tool to use. In the examples given above, the factors are the doctors, the container size, and the drug, respectively. In analyzing the data, we can choose between two hypotheses:

 

■     Null hypothesis, which indicates that the means of all groups are identical.

 

■     Alternative hypothesis, which indicates that there is a statistically significant difference between the group means.

To test these hypotheses in Microsoft Office Excel 2007, we can use the Anova: Single Factor option in the Data Analysis dialog box. If the p-value computed by Excel is small (usually less

 


 

 

 

than or equal to 0.15), we can conclude that the alternative hypothesis is true (the means are significantly different). If the p-value is greater than 0.15, the null hypothesis is true (the populations have identical means). Letís look at an example.

 

The owner of my company, which publishes computer books, wants to know whether the position of our books in the computer book section of bookstores influences sales. More specif ically, does it really matter whether the books are placed in the front, back, or middle

of the computer book section?

The publishing company wants to know whether its books sell better when a display is set up in the front, back, or middle of the computer book section. Weekly sales (in hundreds) were monitored at 12 different stores. At 5 stores, the books were placed in the front; at 4 stores, in the back; and at 3 stores, in the middle. Resulting sales are contained

in the Signif worksheet in the file Onewayanova.xlsx, which is shown in Figure 50-1.

Does the data indicate that the location of the books has a significant effect on sales?

 

Figure 50-1    Book sales data

 

We assume that the 12 stores have similar sales patterns and are approximately the same size. This assumption allows us to use one-way ANOVA because we believe that at most one factor (the position of the display in the computer book section) is affecting sales. (If the stores were different sizes, we would need to analyze our data with two-way ANOVA, which ll discuss in Chapter 51, ďRandomized Blocks and Two-Way ANOVA.Ē)

 

To analyze the data, on the Data tab, click Data Analysis, and then select Anova: Single

Factor. Fill in the dialog box as shown in Figure 50-2.

 

Figure 50-2    Anova: Single Factor dialog box


 
To visually learn more about Excel, please click here.
To view all advanced complex functions, please click here

 

We use the following configurations:

 

     The data for our input range, including labels, is in cells B3:D8.

 

     Select the Labels option because the first row of our input range contains labels.

 

     ve selected the Columns option because the data is organized in columns.

 

     ve selected C12 as the upper-left cell of the output range.

 

     The selected alpha value is not important. You can use the default value. After clicking OK, we obtain the results shown in Figure 50-3.

Figure 50-3    One-way ANOVA results

 

In cells F16:F18, we see average sales depending on the location of the display. When the display is at the front of the computer book section, average sales are 900; when the display is at the back of the section, sales average 1400; and when the display is in the middle, sales average 1100. Because our p-value of 0.003 (in cell H23) is less than 0.15, we can conclude that these means are significantly different.

 

If I am determining whether populations have signif icantly different means, why is the technique called analysis of variance?

Suppose that the data in our book sales study is the data shown in the worksheet named Insig, shown in Figure 50-4 on the next page (also in the file Onewayanova.xlsx). If we run a one-way ANOVA on this data, we obtain the results shown in Figure 50-5 on the next page.

Note that the mean sales for each part of the store are exactly as before, yet our p-value

of .66 indicates that we should accept the null hypothesis and conclude that the position

of the display in the computer book section doesnít affect sales. The reason for this strange result is that in our second data set, we have much more variation in sales when the display is at each position in the computer book section. In our first data set, for example, the variation in sales when the display is at the front is between 700 and 1100, whereas in the second data set, the variation in sales is between 200 and 2000. The variation of sales within each store position is measured by the sum of the squares of data


 

 

 

within a group. This measure is shown in cell D24 in the first data set and in cell F24 in the second. In our first data set, the sum of squares of data within groups is only 22, whereas in the second data set, the sum of squares within groups is 574! This large variation within the data points at each store position masks the variation between the groups (store positions) themselves and makes it impossible to conclude for the second data set that the difference between sales in different store positions is significant.

 

Figure 50-4    Book store data for which the null hypothesis is accepted

 

Figure 50-5    Anova results accepting the null hypothesis

 

 

How can I use the results of a one-way ANOVA for forecasting?

If there is a significant difference between group means, our best forecast for each group

is simply the groupís mean. Therefore, in the first data set, we predict the following:

 

     Sales when the display is at the front of the computer book section will be 900

books per week.

 

     Sales when the display is at the back will be 1400 books per week.

 

     Sales when the display is in the middle will be 1100 books per week.

 

If there is no significant difference between the group means, our best forecast for each observation is simply the overall mean. Thus, in the second data set, we predict weekly sales of 1117, independent of where the books are placed.

We can also estimate the accuracy of our forecasts. The square root of the Within Groups

MS (mean square) is the standard deviation of our forecasts from a one-way ANOVA. As


 

 

 

shown in Figure 50-6, our standard deviation of forecasts for the first data set is 156. By the rule of thumb, this means that we would expect, for example:

     During 68 percent of all the weeks in which books are placed at the front of the computer section, sales will be between 90156=744 and 900+156=1056 books.

 

     During 95 percent of all weeks in which books are placed at the front of the

computer book section, sales will be between 900Ė2(156)=588 book s and

900+2(156)=1212 books.

 

Figure 50-6    Computation of forecast standard deviation

 

 

Problems

 

You can find the data for the following problems in the file Chapter50data.xlsx.

 

1.    For patients of four cardiologists, we are given the number of days the patients stayed in the hospital after open-heart surgery.

 

     Is there evidence that the doctors have different discharge policies?

 

     You are 95 percent sure that a patient of Doctor 1 will stay in the hospital between what range of days?

2.    A drug can be produced by using a 400-degree, 300-degree, or 200-degree oven. You are given the pounds of the drug yielded when various batches are baked at different temperatures.

 

     Does temperature appear to influence the process yield?

 

     What is the range of pounds of the product that you are 95 percent sure will be produced with a 200-degree oven?

     If you believe that pressure within the container also influences process yield, does this analysis remain valid?

 

 

 


 

List of our Excel topics
(Excel 2003) Advance tutorials
(Excel 2003) Algebra
(Excel 2003) Analyzing external data in Excel
(Excel 2003) Analyzing Your Database
(Excel 2003) Arithmetic
(Excel 2003) Basic Actions
(Excel 2003) Basic Sort Filter
(Excel 2003) Catching data entry errors
(Excel 2003) Charting
(Excel 2003) Charts overview  
(Excel 2003) Conditional Formatting 
(Excel 2003) Constraint and References 
(Excel 2003) Copying Cells
(Excel 2003) Creating a workspace in Excel
(Excel 2003) Creating custom lists
(Excel 2003) Creating Simple Workbook
(Excel 2003) Data Subtotals 
(Excel 2003) DATA VALIDATION  
(Excel 2003) Displaying Symbols
(Excel 2003) Don't use AutoFormat on an entire worksheet
(Excel 2003) EDATE function
(Excel 2003) Ensure proper data entry with validation
(Excel 2003) Establishing Criteria
(Excel 2003) Express yourself with comments
(Excel 2003) Filtering
(Excel 2003) Filtering Database
(Excel 2003) Filters  
(Excel 2003) Format cells  
(Excel 2003) Formatting The Appearance of a Workbook
(Excel 2003) Formatting  Appearance Workbook
(Excel 2003) Functions 
(Excel 2003) Getting Random Sample
(Excel 2003) Guidelines Charting
(Excel 2003) Headers&Footers
(Excel 2003) Hiding duplicate records
(Excel 2003) Hyperlink to a specific cell
(Excel 2003) Insert Date&Time
(Excel 2003) Linear Regression
(Excel 2003) Linking Documents
(Excel 2003) Macro security settings
(Excel 2003) Macros 
(Excel 2003) new features
(Excel 2003) Opening a workbook at startup
(Excel 2003) Page Setup
(Excel 2003) Page set-up Overview 
(Excel 2003) Pivot Tables and Charts
(Excel 2003) PointerShapes
(Excel 2003) Printing
(Excel 2003) Printing overview 
(Excel 2003) Protect a worksheet  
(Excel 2003) Quickly setting a print area
(Excel 2003) Quickly shuffling toolbar buttons 
(Excel 2003) Rank Function
(Excel 2003) SOLVER  
(Excel 2003) Split  Worksheet
(Excel 2003) Statistics
(Excel 2003) Summarize data with grouping
(Excel 2003) Trigonometry
(Excel 2003) text export
(Excel 2003) Using functions for Spreadsheet solutions etc
(Excel 2003) Working PivotTable
(Excel 2003) Working with Clip Art
(Excel 2003) Working with Tables
(Excel 2003) Presentations
(Excel 2003) Step-by-Step Examples
(Excel 2007) Convert Text  Columns
(Excel 2007) Getting Random Sample
(Excel 2007) Getting started Excel
(Excel 2007) New features
(Excel 2007) Running Macros
(Excel 2007) Set Print Titles
(Excel 2007) Split  Worksheet
(Excel 2007) Presentations 
(Excel 2007) An Overview Excel
(Excel 2010) Date-Related Functions
(Excel 2010) Adding Hyperlinks
(Excel 2010) Adding Information Worksheets
(Excel 2010) AutoCorrect AutoFill AutoFit
(Excel 2010) Conditional Formatting
(Excel 2010) Data-entry tips
(Excel 2010) Details the New features
(Excel 2010) Excel (Excel 2010)  Tips Tricks
(Excel 2010) EXCEL SHORT CUTS
(Excel 2010) Formatting Cells
(Excel 2010) Getting Started
(Excel 2010) Grouping Worksheets
(Excel 2010) History Excel
(Excel 2010) IF Statements
(Excel 2010) Inserting Dates Times
(Excel 2010) Internal Rate Return
(Excel 2010) Manipulating Dates Times Text
(Excel 2010) Mastering Art Replacement
(Excel 2010) Moving Worksheets from One Workbook to Another
(Excel 2010) Operator precedence
(Excel 2010) Operator precedence
(Excel 2010) Parts of the Excel Screen
(Excel 2010) Paste Special
(Excel 2010) Smart Formatting Tricks
(Excel 2010) Spell Check
(Excel 2010) Undo Redo AutoRecover
(Excel 2010) What Is Excel Good For
(Excel 2010) Whatís new
(Excel 2010) Working with Names
(Excel 2003) Creating Charts
(Excel 2003) Writing Formulas
(Excel 2007) Introduction to charts
(Excel 2003) Using Functions
(Excel 2007) Functions
(Excel 2010) Match Function
(Excel 2010) Text Functions
(Excel 2010) INDEX Function
 Excel Functions Glossary
(Excel 2010) Evaluating Investments
(Excel 2007) Sort filter
(Excel 2010) Range Names
(Excel 2003) Graphing Data Curve Fitting
(Excel 2007) Adding Picture
(Excel 2003) Basic Graphing
(Excel 2010) VBA, Macros, and Other Ways to automate Excel
(Excel 2010) Calculating Various Workbook Elements and Publishing 
(Excel 2010) Consolidating Sheets
(Excel 2010) Function Groups
(Excel 2010) Function Junction
(Excel 2010) Leveraging Excel Functions Using Excel Services
(Excel 2010) Using Excel Functions to Enhance Project Management Productivity
(Excel 2010) The SUMIF, AVERAGEIF, SUMIFS, and AVERAGEIFS Functions
(Excel 2010) The OFFSET Function
(Excel 2010) The Auditing Tool
(Excel 2010) The Goal Seek Command
(Excel 2010) Using the Scenario Manager for Sensitivity Analysis 
(Excel 2010) Financial Functions
(Excel 2010) Circular References
(Excel 2010) COUNTIF, COUNTIFS, COUNT, COUNTA, and COUNTBLANK Functions
(Excel 2010) Sensitivity Analysis with Data Tables
(Excel 2010) An Introduction to Random Variables
(Excel 2010) Analysis of Variance One-Way ANOVA
(Excel 2010) Array Formulas and Functions
(Excel 2010) Calculating an Optimal Bid
(Excel 2010) Consolidating Data
(Excel 2010) Creating Subtotals
(Excel 2010) Determining Customer Value
(Excel 2010) Absolute-relative reference problems
(Excel 2010) Estimating Straight Line Relationships
(Excel 2010) Borrowing and Investing Formulas
(Excel 2010) Forecasting in the Presence of Special Events
(Excel 2010) Examples of Formulas Using the COUNTIF Function
(Excel 2010) Importing Data from a Text File or Document
(Excel 2010) Importing Data from the Internet
(Excel 2010) Incorporating Qualitative Factors into Multiple Regression
(Excel 2010) Introduction to Monte Carlo Simulation
(Excel 2010) Introduction to Multiple Regression
(Excel 2010) Date-Related Functions
(Excel 2010) Modeling Exponential Growth
(Excel 2010) Modeling Nonlinearities and Interactions
(Excel 2010) Nonlinear Pricing
(Excel 2010) Excel Data Types
(Excel 2010) Function Procedure
(Excel 2010) Pricing Stock Options
(Excel 2010) Examples of Formulas Using the COUNTIF Function
(Excel 2010) Randomized Blocks and Two-Way ANOVA
(Excel 2010) Simulating Stock Prices and Asset Allocation Modeling
(Excel 2010) Sorting in Excel
(Excel 2010) Spin Buttons, Scroll Bars, Option Buttons, Check Boxes, Combo Boxes, and Group List Boxes
(Excel 2010) Summarizing Data by Using Descriptive Statistics
(Excel 2010) Summarizing Data by Using Histograms
(Excel 2010) Tables
(Excel 2010) The Binomial, Hypergeometric, and Negative Binomial Random Variables
(Excel 2010) Creating a Custom Number Format
(Excel 2010) The INDIRECT Function
(Excel 2010) The Normal Random Variable
(Excel 2010) The Poisson and Exponential Random Variable
(Excel 2010) The Power Curve
(Excel 2010) Using Correlations to Summarize Relationships
(Excel 2010) Using Moving Averages to Understand Time Series
(Excel 2010) Using PivotTables to Describe Data
(Excel 2010) Creating a Calculated Field or Calculated Item
(Excel 2010) Using Solver for Capital Budgeting
(Excel 2010) Using Solver for Financial Planning
(Excel 2010) Using Solver to Determine the Optimal Product Mix
(Excel 2010) Using Solver to Schedule Your Workforce
(Excel 2010) Using Solver to Solve Transportation or Distribution Problems
(Excel 2010) Validating Data
(Excel 2010) Weibull and Beta istributions: Modeling Machine Life and Duration of a Project
(Excel 2010) Winterís Method
(Excel 2010) Excel Counting and Summing Functions
(Excel 2010) Formatting dates and times
(Excel 2010) Formula Problems and Solutions
(Excel 2010) Formulas returning an error
(Excel 2010) Miscellaneous Calculations
(Excel 2010) Mismatched parentheses
(Excel 2010) Megaformula Examples
(Excel 2010) Introducing Arrays
(Excel 2010) Pivot table terminology
(Excel 2010) Functions Used in Lookup Formulas
(Excel 2010) Project Management Establishing Project Management Fundamentals
(Excel 2010) Project Management Establishing Excel and Office 2007 SharePoint Server Fundamentals
(Excel 2010) Project Management Initiating the Project
(Excel 2010) Project Management Determining Project Requirements
(Excel 2010) Project Management Planning and Acquiring Resources
(Excel 2010) Project Management Assessing and Tracking Risk
(Excel 2010) Project Management Constructing the Project Schedule and Budget
(Excel 2010) Project Management Establishing Change Control Processes
(Excel 2010) Project Management Controlling Project Outcomes and Archiving Documents
(Excel 2010) Project Management IF Statements.
(Excel 2010) VBA Simulation Basic Tutorial
(Excel 2010) VBA Overview
(Excel 2010) VBA Programming Spreadsheets
(Excel 2010) VBA Programming Language
(Excel 2010) VBA Writing Macros
(Excel 2010) VBA QUE
(Excel 2010) Excel Function Reference
 Excel SOFTWARE SUPPORTING Excel 
(Excel 2010) Several concepts contribute to the time value of money:
(Excel 2010) The Pros and Cons of Megaformulas
(Excel 2010) Types of validation criteria you can apply
(Excel 2010) Using Assignment Expressions
(Excel 2010) Using Lookup Functions
(Excel 2010) Understanding Some Database Terminology
(Excel 2010) The Pros and Cons of Megaformulas
(Excel 2010) Using Built-In VBA Functions
(Excel 2010) Exercises
(Excel 2003) PivotTable_Feature.

 

[Excel/KeywordExcel.htm]

Welcome to

MsOffice.us

This is a free website for learning and practicing all the basics of Microsoft Office 2010 training courses, online help, lessons and more...

Links

www.msoffice.us

dbmanagement.info

italiankitchenrecipes.com

relationshipadvices.info

www.coptics.info

 

 

  Home  Excel  | FrontPage Access Word | PowerPoint  | Outlook | Visio |  Project |  Publisher  |  InfoPath | Exchange  |  SharePoint Commerce  | Silverlight Database  | Data Warehouse  |  SQL  |  OO Design SQL_ServerMYSQL | PHP  | Oracle | VBA ASP.NET  | C programming | Java  |  Photoshop | HTMLDreamweaver | Web DesignExpression Web  | XML  | Java script  |  Security | UML | Flash Silverlight |UNIX | Networking 

  Home  |  Excel  | FrontPage Access Word | PowerPoint  | Outlook | Visio |  Project |  Publisher  |  InfoPath | Exchange  |  SharePoint Commerce  | Silverlight Database  | Data Warehouse  |  SQL  |  OO Design SQL_ServerMYSQL | PHP  | Oracle | VBA ASP.NET  | C programming | Java  |  Photoshop | HTMLDreamweaver | Web DesignExpression Web  | XML  | Java script  |  Security | UML | Flash Silverlight |UNIX | Networking