LAB 6 FOR EXCEL 2003 TWO WAY TABLES EXPERIMENTAL DESIGNS NAME LAB TIME LAB BLDG 1 8 9 TWO WAY TABLE SHOWING MARITAL STATUS AT 4 LEVELS AND JOB GRADE AT 4 LEVELS TABLE 6 4 IN THE TEXTBOOK Using the table make a bar graph showing the MARGINAL DISTRIBUTION of JOB GRADE Highlight G5 G8 go to chart wizard select column graph Put the title MARGINAL DISTRIBUTION OF JOB GRADE on the graph Label X axis JOB GRADE Label Y axis NUMBER IN JOB GRADE Size this graph to the range B10 F24 Then make a bar graph showing the CONDITIONAL DISTRIBUTION OF MARITAL STATUS for job grade 2 Highlight C6 F6 go to chart wizard select column graph Put the title CONDITIONAL DISTRIBUTION OF MARITAL STATUS FOR JOB GRADE 2 on the graph Label X axis MARITAL STATUS Label Y axis NUMBER IN MARITAL STATUS Size this graph to the range G10 L24 10 GRAPHING DATA FROM EXPERIMENTS 11 We will work with three types of experimental designs Graphs will be constructed for each and we will draw subjective conclusions from the graphs Later in the course we will learn some statistical tests to reach definitive conclusions 12 EXPERIMENT 1 is from Moore Basic Practice Of Statistics Example 25 4 page 634 It is a 1 Factor design color with 4 values blue green white yellow Experimenters were trying to determine which colors attracted insects best They placed 6 boards of each color coated with a sticky material at random locations in a field of oats After a certain exposure they counted the number of cereal leaf beetles caught The results are shown in columns B through G for each color B29 G32 In I29 calculate the mean for the six blue boards In J29 calculate the standard deviation for the same boards Copy I29 and J29 down to line line 32 to get the same calculations for all colors Make a line graph of the means as follows 1 Highlight A29 A32 and I29 I32 2 Click on chart wizard select line graph next next create titles and finish in the worksheet Size the graph so that it fits in A34 F 51 Make an xy scatterplot of the data set as follows 1 Highlight A29 G32 2 Click on chart wizard select xy scatterplot accept the default no lines type next 3 On the Data Range window select columns instead of rows next create titles and finish in the worksheet Size the graph so that it fits in G34 L51 2 3 4 5 6 7 13 14 15 16 17 18 19 20 Based on the graph of means does COLOR make a difference 21 Which color is best Which is next best 22 The xy scatterplot of all the data shows that the spread in the data is similar for all colors The calculated standard deviations show the same thing 23 EXPERIMENT 2 is a single factor gasoline brand paired design to compare vehicle fuel efficiency miles per gallon using Brand A vs vehicle fuel efficiency using Brand B Six vehicles having different weights different engine sizes and different fuel efficiencies were selected for the experiment All 6 vehicles were driven using one brand for three full tanks and then they were driven using three full tanks of the other brand Each vehicle was assigned the starting gas brand randomly The data is shown in A56 G57 In Columns I and J calculate mean and standard deviation as you did for experiment 1 You could use copy paste to do this Make a scatterplot of the data using the procedure given in step 19 above except select graph type with lines connecting the dots Size the graph so that it fits in the range A60 G77 From the scatterplot and the calculated means do you think there is an improvement 24 25 26 27 28 29 by using one brand vs the other Which gives higher MPG 30 31 32 33 34 EXPERIMENT 3 is a two factor design with one factor at 2 values and the other at 3 values The design attempts to investigate the effect of advertising on the proportion of voters that favor a certain candidate for an elected position such as President Factor 1 is the type of advertisement and the values are 1 issue ads promoting the candidate and 2 attack ads against the opponent Factor 2 is the type of media used to present the message and the values are 1 network tv spots 2 selective cable programs to reach a specific audience and 3 large city newspapers Each type of ad was planned to run with each type of media in at least 5 different markets In each market polls were to be run before and after the ads and the response variable was the mean increase in the percent favoring the candidate The hypothetical mean increases are shown in C89 E90 Make an line graph of the means in the six cells as follows 1 Highlight A89 E90 2 Click on chart wizard select line graph click on the 1st type in second row connecting straight lines next 3 Create titles next finish in the spreadsheet You can fit it into G82 M102 35 For network tv which type of ad is more effective 36 For selective cable programs which type of ad is more effective 37 38 You can see that the question of effectiveness depends on the media being used This is referred to as an interaction Since an interaction is present we should ask which specific combination of the two factors is most effective 39 40 Which combination is least effective Print your spreadsheet Go To File Page setup Page Click on Portrait Click on Fit to 1 wide X 2 tall Click on Margins and set left and right margins to 0 Click on Sheet and set Print Area to A1 M103 Click on Print Preview Set Page Break at line 52 Another way to set the page break is to click on VIEW at the top header bar Then click on Page Break Review and you will be able to move the page break around or insert one Another way is to print with two separate commands Print A1 M52 then Print A53 M103