стр. 49 |

of each value in a data set. You can analyze the relative standing of

values in a data set. The Percentile values can assist in learning about

the spread of the series across its range. For a series provides information

on the ranges for the lowest 25%, the next 25%, the next 25%, and the

174

Chapter 10: Statistics Tools

highest 25%.

Go to14 the menu option TOOLS/DATA ANALYSIS15. Select the option

вЂњRank and Percentile.вЂќ The dialog is shown in the next figure.

Figure 148: Rank and Percentile tool

The result is reproduced in the next table. Each output table contains

four columns:

вЂ” The place of the data point in the data series,

вЂ” The value of the data (with the label for the series as the label

on the output column),

вЂ” The rank of the data point within the range, and

I do not supply the sample data for most of the examples in chapter 42 to chapter 46.

14

My experience is that many readers glaze over the examples and do not go through

the difficult step of drawing inferences from a result if the sample data results are

the same as those in the examples in the book.

If you do not see this option, then use TOOLS / ADD-INS to activate the Add-In for

15

data analysis. Refer to section 41.4.

175

Statistical Analysis with Excel

вЂ” The percentage rank of the data point. The columns are sorted

in order of ascending rank.

Table 30: Output of the Rank and Percentile tool

Point s1 Rank Percent Point s2 Rank Percent

24 21.00 1 96.40% 1 23.00 1 96.40%

48 21.00 1 96.40% 25 23.00 1 96.40%

72 21.00 1 96.40% 49 23.00 1 96.40%

96 21.00 1 96.40% 73 23.00 1 96.40%

120 21.00 1 96.40% 97 23.00 1 96.40%

144 21.00 1 96.40% 121 23.00 1 96.40%

168 21.00 1 96.40% 145 23.00 1 96.40%

23 18.63 8 92.20% 2 20.07 8 92.20%

47 18.63 8 92.20% 26 20.07 8 92.20%

71 18.63 8 92.20% 50 20.07 8 92.20%

95 18.63 8 92.20% 74 20.07 8 92.20%

119 18.63 8 92.20% 98 20.07 8 92.20%

143 18.63 8 92.20% 122 20.07 8 92.20%

167 18.63 8 92.20% 146 20.07 8 92.20%

22 16.53 15 88.00% 3 17.51 15 88.00%

46 16.53 15 88.00% 27 17.51 15 88.00%

70 16.53 15 88.00% 51 17.51 15 88.00%

94 16.53 15 88.00% 75 17.51 15 88.00%

118 16.53 15 88.00% 99 17.51 15 88.00%

142 16.53 15 88.00% 123 17.51 15 88.00%

166 16.53 15 88.00% 147 17.51 15 88.00%

Interpreting the output:

The last rowвЂ™s last four columns can be interpreted asвЂ”

176

Chapter 10: Statistics Tools

The 147th data point in the selected range has a value of 17.51,

which gives it rank 15 in the selected range, with 88% of the cells

in the range having a value less than or equal to this data point.

BIVARIATE RELATIONSвЂ” CORRELATION,

10.3

COVARIANCE

Correlation analysis

This tool and its formulas measure the relationship between two data sets

that are scaled to be independent of the unit of measurement. The

correlation coefficient depicts the basic relationship across two variables:

вЂњDo two variables have a tendency to increase together or to change in

opposite directions and, if so, by how much?вЂќ Bivariate correlations

measure the correlation coefficients between two variables at a time,

ignoring the effect of all other variables.

Go to the menu option TOOLS/DATA ANALYSIS16. Select the option

вЂњCorrelation.вЂќ

Select the вЂњInput RangeвЂќ вЂ” it must have more than one data series.

If you do not see this option, then use TOOLS / ADD-INS to activate the Add-In for

16

data analysis. Refer to section 41.4.

177

стр. 49 |