In Chapters 11 and 12, you were introduced to the process to follow when summarizing and generalizing your data using XL Data Analyst. It is now time to fine tune your skills. Begin by summarizing two different sets of data from the AAConcepts Data Set. Following the directions on p. 248 and pp. 253-254, provide summarization data for the following variables: Age and Size of Household.
dataset link: www.pearsonhighered.com/burns: Basic Marketing Research with Excel (companion website) chapter 11 and 12
In other words, a frequency or percentage distribution is a tabulation of the responses to a categorical scale question in a survey. It quickly communicates how many respondents voted for each of the different answers for that question, and it reveals how much agreement or disagreement there is among the respondents. That is, it expresses the variability of their responses. The percentage distribution is preferred over frequencies because percentages are intuitive and easy to handle. Moreover, Figure 11.2 LU illustrates how quickly percentage distributions communicate variability when they are converted to bar charts. For instance, if our percentage distribution happened to have a great deal of agreement in it, then it would appear as a very steep, spike-shaped histogram such as the one for our \"little variability\" bar graph (drinking coffee); however, if the set happened to consist of many dissimilar numbers, then the bar graph would be much more spread out, with small peaks and valleys. How often do you drink tea? How often do you drink coffee? 80 80 7O 70 60 60 E 50 - g 50 m- gm 330 30 20 20 _ '" '\" - - Very often Occasionally Not at all Very often Occasionally Not at all Much Variability Little Variability Figure 11.2 Bar Chart Shows Variability If you study this formula, you will realize that you first calculate the average, then you compare each respondent's value to the average by subtracting the average from it, and square that difference. It may seem strange to you to square differences, sum them up, divide them by (n 1), and then take the square root. However, if we did not square the differences, we would have positive and negative values; and if we summed them, there would be a cancellation effect. That is, large negative differences would cancel out large positive differences, and the numerator would end up being close to zero. But this result is contrary to what we know is the case with large differences: There is variation that must be expressed by the standard deviation. The formula remedies this problem by squaring the subtracted differences before they are summed. Squaring converts all negative numbers to positives and, of course, leaves the positives positive. Next, all of the squared differences are summed and divided by one less than the number of total observations in the string of values (1 is subtracted from the number of observations to achieve what is typically called an \"unbiased" estimate of the standard deviation). But we now have an inflation factor to worry about because every comparison has been squared. To adjust for this, the equation specifies that the square root be taken after all other operations are performed. This final step adjusts the value back down to the original measure (e.g., units rather than squared units). By the way, if you did not take the square root at the end, the value would be referred to as the \"variance." In other words, the variance is the standard deviation squared. Now that you know how to compute a standard deviation, it is time to review the properties of a normal curve. Table 11.3 [9 shows the properties of a bell-shaped or normal distribution of values. Because it is symmetric, exactly 50% of the distribution lies on either side of the midpoint (the apex of the curve). With a normal curve, the midpoint is also the average. Standard deviations are standardized units of measurement that are located on the horizontal axis. They relate directly to assumptions about the normal curve. For example, the range of one standard deviation above and one standard deviation below the midpoint includes 68% of the total area underneath that curve. Because the bell-shaped distribution is a theoretical or ideal concept, this property never changes. Moreover, the proportion of area under the curve and within plus or minus any number of standard deviations from the mean is perfectly known. Table 11.3 Normal Curve Interpretation of Standard Deviation Number of Standard Deviations Percent of Area Under Percent of Area to Right from the Mean Curvea (or Left)\" a This is the area under the curve with the number of standard deviations as the lower Heft-hand) and upper (right-hand!) limits and the mean equidistant from the limits. \"This is the area left outside of the limits described by plus or minus the number of standard deviations. Because of the normal curve's symmetric properties, the area remaining below the lower limit (left-hand tail) is exactly equal to the area remaining above the upper limit (right-hand tail). For the purposes of this presentation, normally only three standard deviation values are of interest to marketing researchers. Specifically, :l: 2.58 standard deviations describes the range in which 99% of the area underneath the curve is found, :l: 1.96 standard deviations is associated with 95% of the area underneath the curve, and :l: 1.64 standard deviations corresponds to 90% of the bell-shaped curve's area. We have provided Figure 11.5 LU as a visual aid to the :l: 1.96 standard deviations case that accounts for 95% of the area under the curve. 4.96 +1.96 Standard Deviation Standard Deviation Average 'J 95% of the Normal Curve Distribution Figure 1 1 .5 A Normal Curve with Its 95% Properties Identified Whenever a standard deviation is reported along with an average, a specific picture should appear in your mind. Assuming that the distribution is bell shaped, the size of the standard deviation number helps you envision how similar or dissimilar the typical responses are to the average. If the standard deviation is small, the distribution curve is greatly compressed. On the other hand, with a large standard deviation value, the distribution curve is consequently stretched out at both ends. The procedure for summarizing metric variables in XL Data Analyst is identical to the one for summarizing categorical variables, except you will select \"Averages,\" as this is the proper analysis for a metric variable. That is, click on the XL Data Analyst option on your Excel program menu and move your cursor over \"Summarize.\" This will activate the drop-down menu under Summarize to reveal \"Percents\" and \"Averages." Because we are now dealing with metric variables, the Figure 11.6 shows the Averages tables generated by XL Data Analyst for the Advanced Automobile Concepts survey metric variable "Gasoline emissions can contribute to global warming," measured on a 7-point scale, where 1 = very strongly disagree and 7 = very strongly agree. As you can see, this table is quite different from the Percents table, because metric data summarization involves the average, standard deviation, and range (minimum and maximum). The table in Figure 11.6 _ reveals that the average of the 1,000 respondents is 4.8, while the standard deviation is 2.3, and the minimum and maximum values are 1 and 7, respectively. Thus, the typical respondent's 4.8 corresponds to "agree" on the 7-point scale. (You can refer to the Define Variables worksheet to confirm this.) If you select more than one metric variable, the variables will be included in the same MConcepts - Microsoft Excel X File Home Insert Page Layout Formulas Data Review View Developer Add Ins A B C D E C H Average(s) Analysis Results WN Standard Variable Average Deviation Minimum Maximums Sample Gasoline emissions contribute to global warming 1090 The XL. Data Analyst Averages Analysis Table includes average, standard deviation, minimum, maximum, and range. If several variable are select for analysis, they will be listed in descending order of their averages.correct selection is \"Averages.\" A click on the Averages menu item will open up a standard Excel selection window that you can use to select one or more metric variables. When you have completed your selection, a click on the \"OK" button will cause XL Data Analyst to perform the Averages Summarization analysis. Refer to Figure 11.3 LU
