(The results in cells G12:G24 were not created by the Excel Data Analysis feature. The output obtained is shown in Figure 41.3. The alpha value is not important, so just use the default value.įigure 41-2: Randomized blocks dialog box settings Enter B12 as the upper-left cell of the output range.ģ. Check Labels because the first row of the input range contains labels.Ģ. Use the following information to set up your analysis (the input range data is in cells C5:G10):ġ. Fill in the dialog box as shown in Figure 41.2. To analyze this data in Microsoft Office Excel, click Data Analysis on the Data tab, and then select the Anova: Two-Factor Without Replication option. In a sense, you “block” the effect of districts when you try to compare sales representatives and use randomization to account for the possible impact of time on sales. This randomization hopefully lessens the effect of time (a representative presumably becomes better over time) on the analysis. In other words, you can ensure that the month in which Rep 1 is assigned to District 1 is equally likely to be the first, second, third, fourth, or fifth month. This model is also referred to as a randomized block design because you can randomize (chronologically) the assignment of representatives to districts. This model is called a two-way ANOVA without replication because two factors (district and sales representative) can potentially influence sales, and you have only a single instance pairing each representative with each district. For example, Rep 1 sold 20 units during the month she was assigned to District 4. The resulting sales are given in the Randomized Blocks worksheet in the Twowayanova.xlsx file, as shown in Figure 41.1. To answer the question in this example, you can have each of four sales reps spend 1 month selling in each of five sales districts. Suppose you want to determine how a sales representative and the sales district to which the representative is assigned influence product sales. A two-way ANOVA without replication can, however, be used to determine which (if any) of two factors have a significant effect on a dependent variable. Unfortunately, there is never enough data to test for the significance of interactions. In a two-way ANOVA without replication you can observe each possible combination of factors exactly once. Interaction was discussed in the study of multiple regression in Chapter 10, “Using Multiple Regression to Forecast Sales.” In a two-way ANOVA without replication there is no way to examine the significance of interactions. Price and advertising interact significantly if the effect of advertising depends on the product price. For example, suppose you want to predict sales by using product price and advertising budget. When k > 1 you can determine whether two factors exhibit a significant interaction (discussed in more detail in the section, “Two-way ANOVA with Interactions”).

If k>1 the situation is called two-way ANOVA with replication. If k = 1, the situation is called two-way ANOVA without replication.

In two-way ANOVA the dependent variable must be observed the same number of times (call it k) for each combination of the two factors. When two factors might influence a dependent variable you can use two-way analysis of variance (ANOVA) to easily determine which, if any, of the two factors influence the dependent variable. How can the type of button and shape of a banner ad affect the number of click-throughs?.How can price and advertising affect sales?.How can the salespeople and their territory affect sales?.In many marketing situations the marketing analyst believes that two factors may affect a dependent variable of interest. In this chapter you learn about how two-way ANOVA can be used to analyze situations in which two factors may possibly affect a dependent variable. When two factors might influence a dependent variable, you can use two-way analysis of variance (ANOVA) to determine which, if any, of the factors have a significant influence on the dependent variable. In Chapter 40, “Analysis of Variance: One-way ANOVA,” you studied one-way ANOVA where only one factor influenced a dependent variable. Marketing Analytics: Data-Driven Techniques with Microsoft Excel (2014) Part X.

How to set up a two way anova in excel