Page 13 2/24/2017 resulting numbers. Then subtract 1 from this result to obtain an estimate of the stock’s average annual return. The formula =GEOMMEAN(range) finds the geometric mean of numbers in a range. So, to estimate the average annual return on each stock, you proceed as follows: 1. Compute 1 + each annual return by copying from C12 to C12:D15 the formula =1+C5. 2. Copy from C16 to D16 the formula =GEOMEAN(C12:C15)–1. The annual average return on Stock 1 is estimated to be 5 percent, and the annual average return on Stock 2 is –7.8 percent. Note that if Stock 2 yields the mean return of –7.8 percent during two consecutive years, $1 becomes 1*(1–0.078)2=0.85, which agrees with common sense. How can I use boxplots to summarize and compare datasets? Recall from Chapter 41 that a histogramis a chart that shows the frequencies of a data set in various ranges. A boxplotis a chart that graphically displays five important descriptive values for a data set. These values include the following: •The minimum value •The maximum value •The first quartile (the 25th percentile) •The median (the 50th percentile) •The third quartile (the 75th percentile) An example of a boxplot is shown in Figure 42-11. The length of the box in the boxplot is the interquartile range(IQR) = 75th percentile –25th percentile. Using the data in the file Boxplottemp.xlsx (in the Templates folder), we create a boxplot to analyze a classic data set: the military draft numbers from the 1969 draft lottery. In the 1969 lottery, a container was filled with the numbers 1–366, which were then supposedly thoroughly mixed. Then a ball was drawn for January 1 (number 305), next a ball was drawn for January 2 (number 159), and so on. Men with their birth date selected as number 1 were drafted first, then men with draft number 2, and so on. Lower draft numbers made it more likely that a man would be drafted. The cell range A7:B373 contains the draft-lottery number for each birth date (in column B) and the month of the year (in column A). After selecting this range, from the Insert tab, selected Insert Statistic Chart (in the Charts group) and then choose the Box And Whisker option shown in Figure 42-10.
© 2016 Microsoft Corporation. All Rights Reserved. Page 14 2/24/2017 F42xx10 FIGURE 42-10 Selecting a Box And Whisker chart. F42xx10:This figure shows how to select a Box and Whisker chart. To create the boxplot in Figure 42-11, from the Design tab, I chose the fourth option in the Chart Styles group. Then I hovered over the plot area of the chart that shows the diagrams, and when the ScreenTip displayed Series “Number”, I right-clicked to select Format Data Series. In the Format Data Series pane, I chose the Show Outlier Points check box because any point more than 1.5*(IQR) from either end of box is declared an outlier. I then chose Show Mean Markers (the Xs on the chart) and Show Mean Line (the line connecting the Xs).
You've reached the end of your free preview.
Want to read all 19 pages?
- Summer '16
- naveen rathi
- Standard Deviation, Mean