Excel Exercise Week 3

Yadley123

9781284108217_WOBK_Mac.pdf

Home >Mathematics homework help >Statistics homework help >Excel Exercise Week 3

Essentials of Biostatistics Workbook

Statistical Computing Using Excel® Mac 2008

Lisa M. Sullivan, PhD Professor of Biostatistics

Associate Dean for Education Boston University School of Public Health

Boston, Massachusetts

23944_ch00_FM_5976 1 8/3/11 11:36:24 AM

Chapter 1 Basics 1 1.1 Workbooks and Worksheets 1 1.2 Cell Addresses 1 1.3 Entering and Editing Data 1 1.4 Saving Files 5 1.5 Practice Problems 6

Chapter 2 Formulas and Functions 9 2.1 Basic Mathematical Operations 9 2.2 Relative and Absolute Cell References 10 2.3 Creating Formulas and Functions 13 2.4 Practice Problems 18

Chapter 3 Creating Tables and Graphs 19 3.1 Creating and Formatting Tables 19 3.2 Frequency Distribution Tables 21 3.3 Histograms and Bar Charts 26 3.4 Scatter Diagrams 40 3.5 Practice Problems 47

Chapter 4 Summarizing Continuous Variables in a Sample 49 4.1 Descriptive Statistics Using Excel Functions 49 4.2 Practice Problems 58

Chapter 5 Working with Probability Functions 59 5.1 Computing Probabilities with the Binomial Distribution 59

Contents

23944_ch00_FM_5976 3 8/3/11 11:36:24 AM

iv Contents

5.2 Computing Probabilities with the Normal Distribution 61 5.3 Finding Percentiles of the Normal Distribution 63 5.4 Practice Problems 64

Chapter 6 Confidence Interval Estimates 65 6.1 Confidence Intervals for One Sample, Continuous Outcome 65 6.2 Confidence Intervals for One Sample, Dichotomous Outcome 71 6.3 Confidence Intervals for Two Independent Samples, Continuous Outcome 72 6.4 Confidence Intervals for Matched Samples, Continuous Outcome 74 6.5 Confidence Intervals for Two Independent Samples, Dichotomous Outcome 76 6.6 Practice Problems 77

Chapter 7 Hypothesis Testing Procedures 79 7.1 Tests with One Sample, Continuous Outcome 80 7.2 Tests with One Sample, Dichotomous Outcome 81 7.3 Tests with One Sample, Categorical or Ordinal Outcome: The Chi-Square

Goodness of Fit Test 82 7.4 Tests with Two Independent Samples, Continuous Outcome 84 7.5 Tests with Matched Samples, Continuous Outcome 86 7.6 Tests with Two Independent Samples, Dichotomous Outcome 88 7.7 Tests with More Than Two Independent Samples, Continuous Outcome:

Analysis of Variance 90 7.8 Tests with Two or More Independent Samples, Categorical or Ordinal Outcome:

The Chi-Square Test of Independence 96 7.9 Practice Problems 98

Chapter 8 Power and Sample Size Determination 102 8.1 Sample Size Estimates for Confidence Intervals with a Continuous Outcome in One

Sample 102 8.2 Sample Size Estimates for Confidence Intervals with a Dichotomous Outcome in One

Sample 103 8.3 Sample Size Estimates for Confidence Intervals with a Continuous Outcome in Two

Independent Samples 104 8.4 Sample Size Estimates for Confidence Intervals with a Continuous Outcome in

Matched Samples 105 8.5 Sample Size Estimates for Confidence Intervals with a Dichotomous Outcome in

Two Independent Samples 106 8.6 Issues in Estimating Sample Size for Hypothesis Testing 107 8.7 Sample Size Estimates for Tests of Means in One Sample 107 8.8 Sample Size Estimates for Tests of Proportions in One Sample 109 8.9 Sample Size Estimates for Tests of Differences in Means in Two Independent Samples 110 8.10 Sample Size Estimates for Tests of Mean Differences in Matched Samples 112

23944_ch00_FM_5976 4 8/3/11 11:36:24 AM

Contents v

8.11 Sample Size Estimates for Tests of Proportions in Two Independent Samples 113 8.12 Practice Problems 115

Chapter 9 Regression Analysis 117 9.1 Simple Linear Regression Analysis 117 9.2 Multiple Linear Regression Analysis 119 9.3 Practice Problems 124

Chapter 10 Nonparametric Procedures 127 10.1 Ranking Data 127 10.2 Tests with Two Independent Samples 129 10.3 Tests with Matched Samples 132 10.4 Tests with More Than Two Independent Samples 139 10.5 Practice Problems 145

Chapter 11 Survival Analysis 147 11.1 Estimating the Survival Function 147 11.2 Plotting a Survival Function 163 11.3 Comparing Survival Curves 188 11.4 Comparing Two Survival Curves Graphically 197 11.5 Practice Problems 200

23944_ch00_FM_5976 5 8/3/11 11:36:24 AM

23944_ch00_FM_5976 6 8/3/11 11:36:24 AM

the worksheet are labeled with letters (A, B, C, and so on), and the rows of the worksheet are numbered. The workbook name appears in the top center of the screen (Workbook1), and the tab along the bottom of the screen shows the work- sheet name.

It is useful to rename the worksheet to reflect the in- formation that is stored there. For example, we will rename Sheet1 to Data. This can be done by double clicking on the tab with the worksheet name, which places the cursor on the worksheet name at the bottom of the screen (Sheet1 in this case) where we can type the new name (Figure 1–2).

1.2 Cell Addresses A worksheet can be thought of as a set of cells. Each cell is defined by a specific column and row. When we first open Excel, the cursor appears in the top left cell, which makes it the current or active cell. Notice in Figure 1–1 that the top left cell is outlined with a bold line. The column and row make up the cell’s address. The top left cell’s address is A1. As we move the cursor around the worksheet into different cells, the address of the current or active cell is shown just below the top menu bar in the top left portion of the screen.

1.3 entering And editing dAtA For statistical analysis, we enter data into the cells of the Excel worksheet. After the data are entered, we can manipulate the values and perform statistical analyses. Example 1.1 contains data from a small study that we use to illustrate entering and manipulating data.

In this workbook we describe how Excel Mac 2008 can be used to perform the statistical computations and analyses described in the textbook. Excel is a popular program that is often used for organizing and summarizing numerical or financial information. It has substantial graphing capabilities and many statistical functions designed to perform a number of statistical analyses. One of the primary reasons we use Excel is its accessibility. Although other statistical packages (e.g., SAS, SPSS, and S-Plus) offer more advanced analytic tech- niques and procedures, Excel is suitable for the introductory procedures we present here.1–3 In fact, Excel offers many more applications than those we present in this workbook. Here we focus on the concepts and procedures discussed in the textbook. Readers who are interested in broader applications of Excel should see Dretzke.4 Before we proceed with specific analyses, we first present some basic terminology and general procedures to get started.

1.1 Workbooks And Worksheets Excel files are also called workbooks. A workbook is a set of worksheets, where each worksheet can be thought of as a table or grid of rows and columns. When we open the Excel program, a workbook with a blank worksheet is presented (this is the default or preset starting point). Excel calls the new workbook Workbook1. You can change the name of the workbook when you save it (see Section 1.4 for details). The blank worksheet is called Sheet1. The name of the worksheet can also be changed. When Excel is opened, Sheet1 appears on the screen, and it looks like an empty grid of rows and columns. A sample is shown in Figure 1–1. The columns of

Chapter 1 Basics

23944_ch01_5976 1 8/3/11 11:37:20 AM

2 Basics

example 1.1. Suppose we have a sample of five participants (n = 5), and we measure the age, sex, weight, and height of each participant. We also assign a unique identification number to each participant (shown in the first column of Table 1–1). The identification numbers are not used in statistical analysis; they keep track of data measured for each participant. The data are shown in Table 1–1.

In Excel, we use the columns to hold different variables (e.g., identification number, age, sex, weight, and height) and the rows to hold observations measured in different partici- pants. We use the first row for the variable names. This is im- portant because the variable names will appear on the output to make interpretation easier. The data shown in Table 1–1 are entered into Excel by moving the cursor around the work-

sheet. Figure 1–3 shows the data entered into the worksheet we named Data. Notice that the variable names are contained in row 1, and the data measured on participants are shown in rows 2 through 6. There is no restriction on the format or length of variable names. However, it can be easier to work with shorter names, simply in terms of viewing the names and data on the worksheet. It is important to choose informative names that reflect the information entered.

In Figure 1–3, the current or active cell is E6. The cell name is shown just below the menu bar in the top left portion of the screen. The contents of the cell (71 in the figure) are shown just to the right of the active cell’s address.

When the data are entered, we can change or modify en- tries simply by retyping over the contents of the current cell

Workbook Name

Figure 1–1 Excel Workbook with Blank Worksheet

Worksheet Name

23944_ch01_5976 2 8/3/11 11:37:20 AM

Entering and Editing Data 3

Figure 1–2 Renaming a Worksheet

tAble 1–1 Data from Study of Five Participants

Subject Identification Number Age Sex Weight Height

1 24 F 125.45 63 2 21 F 140.05 68 3 32 M 165.16 68 4 27 M 170.39 72 5 25 M 195.47 71

or by typing into the top row where the active cell’s contents are shown (Figure 1–3). We can move from cell to cell in the worksheet by moving the mouse or by using the arrow keys on the keyboard.

There are some instances where the same data are re- peated. In Example 1.1 there are two females and three males. If we enter “F” as the sex of participant 1 (i.e., the participant with identification number 1 whose data are in row 2 of the Data worksheet) into cell C2, rather than entering the sex of participant 2 into cell C3 directly, we can copy the data from cell C2. This is done as follows. First, we make cell C2 active by moving the cursor to that cell. We then click the Copy icon on the menu bar. To let us know that the contents of the active cell have been copied, Excel shows the borders of the cell with a bold, flashing dotted line (as opposed to a bold solid line). We then move the cursor to the destination cell (e.g., C3) and click the Paste icon on the menu bar. The contents of cell C2 are copied and pasted into cell C3. The same process can be used to copy the contents of one cell to several cells. Suppose we enter “M” as the sex of participant 3 into cell C4, and we want to copy the contents of cell C4 to cell C5 and cell C6. We make cell C4 the active cell and click the Copy icon. We then highlight the destination cells—in this case cell C5 and cell C6. To do this we place the cursor on cell C5 (the top or first cell in the range), and while holding the left mouse key

Figure 1–3 Data in Example 1.1 Entered into Excel Worksheet

Contents of Active Cell

23944_ch01_5976 3 8/3/11 11:37:21 AM

4 Basics

Figure 1–4 Inserting a Row down we drag the cursor to cell C6. This highlights both cells C5 and C6. We then click the Paste icon on the menu bar, and the contents of cell C4 are copied into cell C5 and cell C6.

To insert a row into a worksheet, we select the Rows option under Insert along the top menu bar (Figure 1–4). When the option is selected, a blank row is inserted above the current row.

The same approach can be taken to insert a column. Se- lecting the Columns option under Insert along the top menu bar inserts a column to the left of the active cell.

For presentation purposes, we often want to format data or results consistently. There are a number of formatting op- tions available on the Formatting Palette, which is available under the Toolbox icon. Clicking on the Toolbox icon shows the Formatting Palette (Figure 1–5).

Figure 1–5 Formatting Palette

23944_ch01_5976 4 8/3/11 11:37:22 AM

Entering and Editing Data 5

Various options can be used to format the contents of any cell or cells in a worksheet. For example, we entered weights in pounds into our Data worksheet, as follows:

Weight

125.45

140.05

165.16

170.39

195.47

If we want to present the weights to the nearest tenths (i.e., round the weights to one decimal place), we highlight the cells we want to format (in this case, cell D1 through cell D6) and change the format from General to Number in the

Number section, about half way down in the Formatting Palette. Underneath the Format: Number specification we can increase (more precision) or decrease (less precision) the number of decimal places by clicking on the left and right icons, respectively (Figure 1–6).

After clicking on the Decrease Decimal Places icon (right side under Format: Number), the weights in the worksheet change to the following:

Weight

125.5

140.1

165.2

170.4

195.5

Figure 1–6 Formatting Cells in a Worksheet

23944_ch01_5976 5 8/3/11 11:37:22 AM

6 Basics

tAble 1–2 Data for Practice Problems

Participant ID Systolic Blood

Pressure Diastolic Blood

Pressure Total Serum Cholesterol Weight Height

1 141 76 199 138 63.00 2 119 64 150 183 69.75 3 122 62 227 153 65.75 4 127 81 227 178 70.00 5 125 70 163 161 70.50 6 123 72 210 206 70.00 7 105 81 205 235 72.00 8 113 63 275 151 60.75 9 106 67 208 213 69.00 10 131 77 159 142 61.00

Figure 1–7 Saving the Excel Workbook as a File

We can also present weight to the nearest pound by click- ing again on the Decrease Decimal Places icon. The Format- ting Palette is particularly useful for formatting results. For example, when we compute the mean or standard deviation of a sample, Excel will carry more decimal places than we will want to present. Recall that, as a general rule, we report sum- mary statistics that have one more decimal place than the raw data. Reporting too many decimal places implies a false level

of precision. We illustrate how to format results in Chapters 4 through 9 of this workbook.

1.4 sAving Files To save a file, we click the Save As option under File along the top menu bar (Figure 1–7). The Save As option is used to save a workbook (and its associated worksheets) in one of several possible formats. Generally, we save the workbook as an Excel Workbook compatible with Excel 1997–2004. After selecting the Save As option and a format, Excel prompts us to enter a file name to store the workbook.

When a workbook is saved as a file, we can open it using the Open option under File along the top menu bar.

1.5 PrACtiCe Problems

1. Use Excel to create a worksheet with data shown in Table 1–2. The data were presented in Table 4–13 in the text- book and were measured in a subsample of 10 participants (n = 10) who attended the seventh examination of the Framingham Offspring Study. Place the variable names in the first row of the worksheet. (Try using the Copy and Paste icons on the menu bar to enter repeated values.)

2. Rename the worksheet with the data from Problem 1 as “Data.”

3. Save the Excel workbook of the previous problems as a file.

23944_ch01_5976 6 8/3/11 11:37:22 AM

Practice Problems 7

3. S-Plus [computer program]. Version 7.0. Seattle, WA: Insightful Corp; 1999–2006.

4. Dretzke BJ. Statistics with Microsoft Excel. 3rd ed. Upper Saddle River, NJ: Pearson Prentice Hall; 2005.

reFerenCes 1. SAS [computer program]. Version 9.1. Cary, NC: SAS Institute Inc;

2002–2003. 2. SPSS [computer program]. Version 15.0. Chicago, IL: SPSS Inc;

2006.

23944_ch01_5976 7 8/3/11 11:37:23 AM

23944_ch01_5976 8 8/3/11 11:37:23 AM

implement mathematical operations, we program specific operations into the cells of a worksheet. We can use these operations, for example, to convert variables measured on one scale to another scale or to create new variables from existing variables.

In Example 1.1 of the Excel workbook we presented data on n = 5 participants. We measured age (in years), sex (M/F), weight (in pounds), and height (in inches). The data for par- ticipant 1 are shown in Table 2–2.

We could convert age measured in years to age in months by multiplying age in years by 12 (e.g., Agemonths = Ageyears ¥ 12). We could also convert weight in pounds to weight in kilograms as follows: Weightkilograms = Weightpounds / 0.4636. Excel can be used to make these transformations easily. We illustrate how this is done in Section 2.2.

Example 2.1. Consider a study designed to assess the im- pact of a medication designed to lower systolic blood pres- sure. Suppose we measure participants’ baseline systolic blood pressure, and then we measure their systolic blood pressure

After data are entered into an Excel Mac 2008 worksheet, we can create formulas and functions to organize, manipulate, and analyze the data. For example, Excel can be used to create new variables from existing variables (e.g., to convert variables from one scale of measurement to another, to standardize variables into Z scores, or to create new variables from those that are measured directly) or to compute summary statistics (e.g., the mean, standard deviation, or median of a dataset; the minimum and/or maximum values).

2.1 Basic MathEMatical OpEratiOns Basic mathematical operations are performed in Excel in the same way as they are on a calculator or in other statistical computing packages. In Excel, the following basic mathemati- cal operations are implemented with the operators shown in Table 2–1.

The order of operations is exponentiation, multiplication and division, and then addition and subtraction. In order to

Chapter 2 Formulas and Functions

taBlE 2–1 Mathematical Operators in Excel

Operation Operator

Multiplication ¥ Division / Addition + Subtraction – Exponentiation ^

taBlE 2–2 Participant Data

Subject Identification Number Age Sex Weight Height

1 24 F 125 63

23944_ch02_5976 9 8/3/11 11:38:50 AM

10 Formulas and Functions

In Section 2.2 we illustrate how these operations (i.e., converting variables measured on one scale to another scale, creating new variables) are carried out in Excel. We must first discuss relative and absolute cell references.

2.2 rElativE and aBsOlutE cEll rEfErEncEs The operations described in Section 2.1 can be implemented in Excel by programming the operations into cells in an Excel worksheet. The programming amounts to specifying expressions in Excel to perform the desired operations. We are essentially creating new variables as functions of existing variables using specific operations (e.g., converting from one scale to another, creating difference scores). To implement these operations, we first choose a column location for the new variable and specify a name for the new variable. The new variable name is placed in the first row of the worksheet alongside the other variable names. We then input the opera- tion or formula to create the new variable. In Excel, these op- erations are indicated by an equals sign (=). When Excel sees an equals sign at the beginning of a cell, it expects a formula to follow. The formula is implemented to produce the desired result, which is placed into that cell. Figure 2–2 shows the data from Example 1.1 in a worksheet. Suppose we want to create a new variable, age in months, and we label it “Age, months”. We first choose a location for the variable. Suppose we want to place the new variable in column F of the worksheet. We enter the new name into row 1 of column F, as shown in Fig- ure 2–2. Age in months is computed by multiplying Age in years (which is contained in column B) by 12. Specifically,

after 6 months of treatment. The data on n = 3 participants are entered into Excel and are shown in Figure 2–1.

Notice that the distinct variables (e.g., ID [identification number], Baseline SBP, and 6 Months SBP) are shown in the columns of the worksheet, and data for each participant are shown in the rows. To analyze these data, we use methods for dependent, matched, or paired samples and focus specifically on differences in blood pressures. For each participant we need to compute the differences. We can take differences as follows: Difference = Systolic Blood Pressure6 Months – Systolic Blood PressureBaseline.

figurE 2–1 Systolic Blood Pressure Measured at Baseline and 6 Months Later

figurE 2–2 Creating a New Variable

23944_ch02_5976 10 8/3/11 11:38:50 AM

Relative and Absolute Cell References 11

Figure 2–4 shows the new variable (column D) and the for- mula to compute it in cell D2. The difference score is com- puted by subtracting the baseline SBP (column B) from the SBP measured at 6 months (column C). The formula for par- ticipant 1 is “=C2-B2”.

If we copy the contents of cell D2 into cell D3 and cell D4, Excel automatically updates the cell referencing. The formulas are “=C3-B3” and “=C4-B4”, respectively. After the formulas are entered, Excel computes the differences, and the results are shown in Figure 2–5.

Example 2.2. Suppose we measure the lengths, in centi- meters (cm), of six infant boys (n = 6) who are 12 months of age. The data are entered into an Excel worksheet, as shown in Figure 2–6.

the formula to create Age, months is Age (in years) ¥ 12. This formula is entered into cell F2 as “=B2*12”. B2 in the formula represents the address of the cell containing the age in years for participant 1. When the formula is entered, Excel takes the value from cell B2 (24 years) and multiplies it by 12. The result (288) is placed into cell F2.

To complete this operation for each participant, we copy the contents of cell F2 and paste into cell F3 through cell F6. This is done by making F2 the active cell and clicking the Copy icon on the top menu. The border of cell F2 is shown in a bold, flashing dotted line. We next highlight cell F3 through cell F6 and click the Paste icon on the top menu bar. The for- mula is copied from cell F2 into cell F3 through cell F6. Excel automatically updates the cell referencing (i.e., the locations of the cells that contain the ages in years for each participant). Specifically, when we enter the formula to compute age in months for participant 1 into cell F2, we specify that Excel should take the data in cell B2 and multiply by 12. We want to do the same for the remaining participants. We want to multiply each participant’s age in years by 12. When we copy and paste the formula from cell F2 into cell F3 through cell F6, Excel updates the cell references, as shown in Figure 2–3.

Excel automatically updates the formula to compute age in months for participants 2 through 5 by updating the cell references (i.e., B3 through B6). These references are called relative cell references. The formula to compute age in months for each participant uses the relevant information, the age in years for that participant, contained in column B.

Consider again the data in Example 2.1 shown in Figure 2–1. Suppose we now want to create the difference variable.

figurE 2–3 Using Relative Cell References

figurE 2–4 Computing Differences

23944_ch02_5976 11 8/3/11 11:38:51 AM

12 Formulas and Functions

use the lengths in centimeters in cell A3 through cell A7, respectively.

There is a second way to perform the standardization. Suppose we enter the data into an Excel worksheet as shown in Figure 2–8.

The mean and standard deviation are now shown in cell B9 and cell B10, respectively. We again create Z scores by

Suppose we want to standardize the lengths by subtract- ing the mean and dividing by the standard deviation. The mean length for 12-month-old boys is reported as 75 cm, and the standard deviation is reported as 2.1 cm.

The lengths in centimeters are shown in column A. We now want to standardize the lengths by subtracting the mean and dividing by the standard deviation as follows: Z = (length – 75) / 2.1. To create the new variable, Z, we enter the formula as shown in Figure 2–7.

Now, if we copy the formula from cell B2 into cell B3 through cell B7, Excel updates the cell references to

figurE 2–5 Difference Scores figurE 2–7 Computing Z Scores

figurE 2–8 Lengths, cm, of Boys 12 Months of Age with Mean and Standard DeviationfigurE 2–6 Lengths,

cm, of Boys 12 Months of Age

23944_ch02_5976 12 8/3/11 11:38:51 AM

Relative and Absolute Cell References 13

taking each length in column A, subtracting the mean of 75, and dividing by the standard deviation of 2.1. Here we refer Excel to the cells containing the mean and standard devia- tion (i.e., cell B9 and cell B10) in the worksheet. If we again place the Z scores in column B, the formula entered in cell B2 is “=(A2-$B$9)/$B$10”. A dollar sign before a column or row in a cell address freezes or fixes that column or row (as opposed to allowing Excel to update a relative address as per the previous examples). In this example, we are fixing both the columns and rows of the addresses of the mean and standard deviation. These are called absolute cell references. Figure 2–9 displays the formulas that are copied into cell B3 through cell B7; notice that the cell addresses for the mean and standard deviation do not change from cell to cell.

When we enter the formulas, the results are shown in column B. Both of the methods illustrated in Figures 2–7 and 2–9 produce the results shown in Figure 2–10.

The boy of length 71 is 1.9 standard deviations below the mean, and the boy of length 79 is 1.9 standard deviations above the mean. For presentation purposes, we can format the cells in column B (using the Formatting Palette, which is available under the Toolbox icon on the top menu bar as shown in Figures 1–5 and 1–6) to two decimal places.

2.3 crEating fOrMulas and functiOns We now describe how Excel is used to compute summary sta- tistics (e.g., X , s, median) using formulas and functions.

figurE 2–9 Using Absolute Cell References

figurE 2–10 Standardizing Lengths

Example 2.3. In many studies of cardiovascular disease (e.g., the Framingham Heart Study), body mass index (BMI) is assessed as a risk factor. BMI is defined as follows: BMI = weightkg/heightm

2, (where kg = kilograms and m = meters).

23944_ch02_5976 13 8/3/11 11:38:52 AM

14 Formulas and Functions

Often weights are measured in pounds, and heights are measured in inches. Thus, the observed measurements must be converted to kilograms and meters, respectively, and then divided to produce BMI scores. In Example 1.1 we measured weight in pounds and height in inches in n = 5 participants. Suppose we now want to calculate the BMI for each par- ticipant. The conversion from pounds to kilograms is 1 lb = 0.4536 kg, and the conversion from inches to meters is 1 in = 0.0254 m. The formula to compute BMI from weight in pounds and height in inches is as follows:

BMI (weight ) (height

pounds

inches

= × 0.4536

× 0.02554)2

Figure 2–11 shows the computation of BMI. The formula in cell H2 can be copied to cell H3 through cell H6 to compute BMI for each participant. Notice that the power operator “̂ 2” is used to square height in the denominator of the formula.

When the formula is copied, the BMI scores are computed as shown in Figure 2–12.

figurE 2–12 BMI Values

figurE 2–11 Computing BMI from Weight in Pounds and Height in Inches

23944_ch02_5976 14 8/3/11 11:38:52 AM

Relative and Absolute Cell References 15

We now use Excel to compute the sample mean BMI (i.e., X X

n = Σ ). Excel has a number of built-in formulas and func-

tions that can be used for mathematical, statistical, and other (e.g., financial) operations. We use some of the mathematical and statistical formulas and functions to compute summary statistics. To compute the sample mean, we use the SUM and COUNT functions, which sum data and count the number of observations available (n), respectively.

We first sum the BMI scores and place the sum in cell H8. This is done with the SUM function. In cell H8 we enter the formula “=SUM(H2:H6)”. The SUM function sums the data in the cells specified in the data range (in parentheses). In this example, we want to sum the data in cell H2 through cell H6. We then compute the sample size using the COUNT function and place the sample size into cell H9. In cell H9 we enter the formula “=COUNT(H2:H6)”. The COUNT function tallies the number of cells with non-missing data. The sample mean is computed by dividing the sum by the sample size, and we place the sample mean into cell H10. Specifically, in cell H10 we enter the formula “=H8/H9”. We use column G for labels (Figure 2–13).

Suppose we now wish to compute the standard deviation

of BMI ( s X X n

= − −

Σ( )2

1 ). We need to first subtract the

mean BMI (in cell H10) from each BMI and square the differ- ence. We place the squared differences in column I, and the formula to be entered in cell I2 is as follows: “=(H2-$H$10)^2”.

We then sum the squared differences using the SUM func- tion (i.e., “=SUM(I2:I6)”) and place the result in cell I8. The variance is computed by dividing the sum of the squared dif- ferences by (n – 1). We compute the variance in cell I11 as fol- lows: “=I8/(H9-1)”. The standard deviation is then computed as “=sqrt(I11)”. Figure 2–14 displays the results.

Like many statistical computing packages, there are sev- eral ways to compute summary statistics in Excel. One way is to use the mathematical operations to program the formulas

(e.g., X X n

= Σ , s X X n

= − −

Σ( )2

1 ). A second method is

to use one of Excel’s many built-in statistical formulas that directly compute summary statistics on a continuous vari- able. Under the AutoSum icon on the top menu bar, Excel offers several functions and formulas, including Sum, Aver- age (mean), Count (e.g., to compute the sample size n), Max, and Min. There are other functions available as shown in Figure 2–15.

Figure 2–16 shows the complete list of formulas and func- tions (shown in the Formula Builder dialog box) that are available when we click the More Functions option shown in the figure.

In the Formula Builder dialog box, Excel shows avail- able functions along with a brief description of the use of each function. Clicking the More help on this function option in the Description window brings up more detailed help along with examples.

figurE 2–13 Computing the Sample Mean

23944_ch02_5976 15 8/3/11 11:38:53 AM

16 Formulas and Functions

figurE 2–14 Computing the Sample Variance and Standard Deviation

figurE 2–15 Statistical Formulas Available in Excel

23944_ch02_5976 16 8/3/11 11:38:54 AM

Relative and Absolute Cell References 17

in cell B8. The AvErAGE function sums the data specified in parentheses (in the worksheet, the age data are in cell B2 through cell B6) and divides by the sample size (i.e., the total number of non-missing values), as shown in Figure 2–17.

We can also compute the sample standard deviation using the STDEv function, as shown in Figure 2–18 (see the func- tion call in the active cell in the top menu bar).

Excel has an AvErAGE function that computes a mean. The function is used as follows. First, we select a cell for the result. Suppose we wish to compute the mean age for the data shown in Figure 2–14, and we want to place the mean age in cell B8. We enter the following into cell B8: “=AvErAGE(B2:B6)”. When the formula is entered, the mean of the observations contained in cells B2 through B6 is computed and placed

figurE 2–16 More Excel Functions

figurE 2–18 Computing the Sample Standard Deviation with the STDEV Function

figurE 2–17 Computing the Sample Mean with the AVERAGE Function

23944_ch02_5976 17 8/3/11 11:38:54 AM

18 Formulas and Functions

MAP = {(2 ¥ Diastolic Blood Pressure) + Systolic Blood Pressure} / 3

3. Compute the sample size for the MAP data using the COUNT function, and store the result in the Data worksheet.

4. Compute the mean MAP by programming the formula for the mean (i.e., X X

n = Σ ) and store the result in the

Data worksheet.

5. Compute the standard deviation of the MAP values by programming the formula for the standard deviation

(i.e., s X X n

= − −

Σ( )2

1 ), and store the result in the Data

worksheet.

Other functions in Excel are useful for computing sum- mary statistics; these are discussed in Chapter 4.

2.4 practicE prOBlEMs

1. Use Excel to create a worksheet, called Data, with the data shown in Table 2–3. The data were presented in Table 4–13 of the textbook and were measured in a subsample of 10 participants (n = 10) who attended the seventh ex- amination of the Framingham Offspring Study. Place the variable names in the first row of the worksheet.

2. Compute two new variables in the Data worksheet for each participant, BMI and mean arterial pressure (MAP). The formulas for the variables are shown in the following equations:

BMI (weight ) (height

pounds

inches

= × 0.4536

× 0.02554)2 and

taBlE 2–3 Data for Practice Problems

Participant ID Systolic Blood Pressure Diastolic Blood Pressure Total Serum Cholesterol Weight Height

23944_ch02_5976 18 8/3/11 11:38:55 AM

of whether data are presented for a single group or for sepa- rate groups, the investigator must select the most appropriate statistics to summarize key information.

Example 3.1. Suppose we conduct a cross-sectional study of 125 undergraduate students to estimate the prevalence of cigarette smoking. Before presenting data on the prevalence of smoking, the investigators wish to provide a description of the study sample. Suppose that the following background variables are analyzed (with Excel or another statistical com- puting package), and the results are as follows. For each con- tinuous variable, sample means and standard deviations are produced, and for each ordinal and categorical variable, fre- quencies and relative frequencies are produced. The results are shown in Table 3–1.

Table 3–2 was developed in Excel to present the informa- tion shown in Table 3–1. For the continuous variables, we pres- ent means and standard deviations rounded to one decimal place. For ordinal and categorical variables, we present the frequencies and relative frequencies. The table can be copied from Excel into a Word document for presentation.

It is always important to include a clear and concise title in a table. It is also important to specify clear variable names with appropriate units. Finally, the data (i.e., summary statis- tics) presented in the table must be clearly defined.

Table 3–2 was prepared in Excel, as shown in Figure 3–1. The title is entered in the first row of the table. To accommo- date the length of the title and lengths of the variable names, the widths of the columns (e.g., A, B) in the worksheet can be enlarged. However, it is not necessary to enlarge all of column A to accommodate the long title, because the title occupies

Excel Mac 2008 is very useful for creating tables and graphs to summarize and present statistical information. Many in- vestigators use Excel to prepare tables and graphs for reports, presentations, and manuscripts. Often presentations and man- uscripts are prepared in other packages (e.g., PowerPoint, Word), but tables and charts are prepared in Excel and then imported into those presentations or manuscripts.

3.1 CrEating and Formatting tablEs In Chapter 4 of the textbook, we presented a number of sta- tistics to summarize continuous, dichotomous, ordinal, and categorical variables. Investigators must determine which statistics most accurately and completely describe sample data. For example, for continuous variables we can compute the sample mean, median, and mode to describe central ten- dency; and we can compute the sample range, interquartile range, variance, and standard deviation to describe variability. For dichotomous, ordinal and categorical variables we can compute frequencies, relative frequencies, and cumulative relative frequencies (appropriate for ordinal variables). For presentation purposes, we must decide which statistics to present and how.

In almost all research reports, investigators include a description of the study sample. The description usually in- cludes sociodemographic or background characteristics (e.g., age, sex, educational level) and might include data to describe clinical history (e.g., prevalent disease, symptom severity at the start of the study). With a cross-sectional or cohort study, the description is often based on the full sample or cohort. In clinical trials, descriptions are usually provided for each treatment group, which are considered separately. Regardless

Chapter 3 Creating Tables and Graphs

23944_ch03_5976 19 8/3/11 11:39:39 AM

20 Creating Tables and Graphs

only one row (the first row). To accommodate the length of the title, we merge the A1 and B1 cells into one larger cell. This is done by first entering the title into cell A1. Because the title is longer than the width of cell A1, the title runs into cell B1 and is cut off, as shown in Figure 3–2.

Before we merge cells to accommodate the length of the title, we first resize columns A and B to accommodate the variable names, units, and descriptive statistics (Figure 3–3).

tablE 3–1 Summary Statistics

Characteristic Statistics

Age X̀ = 19.567 s = 1.867

Sex Men 79 (63.2%) Women 46 (36.8%)

Year in school Freshman 35 (28.0%) Sophomore 41 (32.8%) Junior 31 (24.8%) Senior 18 (14.4%)

Number of hours of exercise per week

X̀ = 5.821 s = 2.989

Weight X̀ = 165.352 s = 14.857

Height X̀ = 67.463 s = 4.655

tablE 3–2 Description of Study Sample

Characteristic Mean (SD) or n (%)

Age, years 19.6 (1.9) Sex Men 79 (63%) Women 46 (37%) Year in school Freshman 35 (28%) Sophomore 41 (33%) Junior 31 (25%) Senior 18 (14%) Exercise per week, hours 5.8 (3.0) Weight, pounds 165.4 (14.9) Height, inches 67.5 (4.7)

FigurE 3–1 Table Describing Study Sample

FigurE 3–2 Entering Title of Table

23944_ch03_5976 20 8/3/11 11:39:40 AM

Frequency Distribution Tables 21

We now enter the variable names, units, and summary statistics into columns A and B.

Excel can be used to create tables for presentation pur- poses, and it offers options similar to those offered in Mi- crosoft Word for formatting text, in terms of font size, type, justification, and so on. Excel also has options to round nu- meric information, for example, to round statistics to one or two decimal places. Suppose we compute the mean and standard deviation of several continuous variables, as shown in Figure 3–5.

For presentation, we wish to display the summary statis- tics to one decimal place. This can be done by first highlight- ing the desired range of cells (cell B2 through cell C5) and clicking the Decrease Decimal Places icon on the second line of the top menu bar (Figure 3–6). Each click reduces the number of decimal places in the highlighted cells by one. The results (summary statistics reported to one decimal place) are shown in Figure 3–6.

3.2 FrEquEnCy distribution tablEs Excel has a built-in menu option that can be used to create frequency tables for presenting information. This is especially useful for ordinal and categorical variables. The option is il- lustrated in Example 3.2.

Example 3.2. Suppose we have a small study of 10 patients (n = 10) with rheumatoid arthritis, and we record their sex and the severity of their symptoms of arthritis. The data are shown in Figure 3–7.

To enlarge the width of column A, we place the cursor on the vertical line between columns A and B. The cursor changes shape to a bold cross with arrows running right and left. Mov- ing the cursor on the vertical line between columns A and B to the right or to the left increases or decreases the width of column A. We do the same for column B with the vertical line between columns B and C.

We now merge cell A1 and cell B1 to accommodate the width of the title. This is done by highlighting cell A1 and cell B1 and clicking the Merge and Center icon on the second line of the top menu bar (Figure 3–4).

FigurE 3–3 Widening Columns to Accommodate Title

FigurE 3–5 Summary Statistics on Continuous Variables

FigurE 3–4 Merging Cells

Merge and Center Icon

23944_ch03_5976 21 8/3/11 11:39:40 AM

22 Creating Tables and Graphs

FigurE 3–6 Formatting Numerical Data to One Decimal Place

Decrease Decimal

FigurE 3–7 Data on n = 10 Participants with Rheumatoid Arthritis

Suppose we want to present the distribution of sex in the sample in a frequency distribution table. We first select Pivot Table Report under the Data tab on the Excel menu bar (top of the screen), as shown in Figure 3–8.

When we select Pivot Table Report, Excel brings up a wizard that prompts us for the information needed to create the frequency distribution table (Figure 3–9).

Excel asks for the details necessary to generate the fre- quency distribution table. The first detail concerns the data. Excel expects the data to come either from the existing work- sheet (this option is already checked) or from an external data source. The first response applies when the data are in an Excel worksheet, as is the case in this example. Thus, we do not need to modify the default response. We would choose an alternate response if, for example, the data were stored in a different file. When we click Next, Excel prompts us for the range of the data, and we provide the cell addresses for the data to be analyzed after Range. We want to generate a frequency dis- tribution table for sex, so we specify cell B1 through cell B11. Notice that we include cell B1, which actually contains the name of the variable as opposed to data (Figure 3–10).

Excel then asks where we would like the frequency distri- bution table to be placed. We need only to specify the cell ad- dress for the top left corner of the frequency distribution table. In Figure 3–11, we request that Excel places the top left corner of the frequency distribution table in cell E1. (Note that we could have checked the first option and requested that the frequency distribution table be placed in a different worksheet.)

When we click Finish, Excel sets up a template in the current worksheet for the frequency distribution table. The template is shown in Figure 3–12. Notice that the top left corner of the template is in cell E1.

In addition to the template (shown in the background), Excel also displays the variable we specified for the analysis (in this case, Sex) in the Pivot Table dialog box on the left side of the worksheet. In order to produce the frequency dis- tribution table, we must specify that Sex is the row variable

23944_ch03_5976 22 8/3/11 11:39:41 AM

Frequency Distribution Tables 23

FigurE 3–8 Creating a Frequency Distribution Table

FigurE 3–9 Pivot Table

23944_ch03_5976 23 8/3/11 11:39:41 AM

24 Creating Tables and Graphs

FigurE 3–10 Specifications for Frequency Distribution Table

FigurE 3–11 Location of the Frequency Distribution Table

23944_ch03_5976 24 8/3/11 11:39:42 AM

Frequency Distribution Tables 25

FigurE 3–12 Template for the Frequency Distribution Table

for the table and that Sex is the data variable. We first specify that Sex is the row variable by dragging Sex from the Pivot Table dialog box to the Drop Row Fields Here portion of the

template. When we drop Sex into the Drop Row Fields Here box, Excel automatically makes Sex the row variable in the frequency distribution table, as shown in Figure 3–13.

FigurE 3–13 Specification of Row Variable for Frequency Distribution Table

23944_ch03_5976 25 8/3/11 11:39:43 AM

26 Creating Tables and Graphs

FigurE 3–14 Specification of Data Variable for Frequency Distribution Table

FigurE 3–15 Frequency Distribution Table for Symptom Severity

We now need to specify that Sex is also the data variable. The data variable is the variable that will be summarized. For a frequency distribution table with one variable, the row and data variable are the same. In other instances, it may be of interest to summarize a second variable (e.g., compute frequencies of symptom severity by sex, in which case the

data variable would be different from the row variable). This is done by again selecting and dragging Sex from the Pivot Table dialog box and dropping it into the Drop Data Items Here portion of the template. When we drop Sex into the Drop Data Items Here box, Excel automatically sums the re- sponses and produces the frequency distribution table shown in Figure 3–14.

Using the same sequence of steps, we can also generate a frequency distribution table for symptom severity. The results are shown in Figure 3–15. In the specifications, we requested that Excel place the top left corner of the frequency distribu- tion table in cell E7.

Suppose we also want to present relative frequencies. The relative frequencies for men and women can be computed by entering “=F3/$F$5” and “=F4/$F$5” into cell G3 and cell G4, respectively. The same can be done for the symptom severity data, using “=F9/$F$12”, for example, in cell G9 and copying to cell G10 and cell G11. The results are shown in Figure 3–16.

3.3 Histograms and bar CHarts Excel is very powerful for generating graphical displays. Inves- tigators often run statistical analyses in other packages, such as SAS, and then use Excel to produce graphical displays of the statistical results. There are several ways to generate his- tograms and bar charts for ordinal and categorical variables, respectively. We use the Charts options available in Excel and the frequency distribution table we just created.

23944_ch03_5976 26 8/3/11 11:39:43 AM

Histograms and Bar Charts 27

FigurE 3–16 Frequency Distribution Table with Relative Frequencies

FigurE 3–17 Selecting Data for the Histogram

Suppose we wish to create a frequency histogram of symptom severity. First, we select the frequency distribution table we just produced by clicking the top left corner of the frequency distribution table. This highlights the frequency distribution table, as shown in Figure 3–17.

Next, we open up the options available under the Charts tab (just above the worksheet), as shown in Figure 3–18.

We select the Column options (these are the templates for bar charts and histograms), as shown in Figure 3–19.

We then click one of the Column options to produce the display. Here we select the second option from the left, which produces the display shown in Figure 3–20.

There are a number of issues with the display shown in Figure 3–20. First, the chart is a bar chart and not a histogram.

23944_ch03_5976 27 8/3/11 11:39:44 AM

28 Creating Tables and Graphs

FigurE 3–18 Displaying the Charts Options

FigurE 3–19 Column Charts for Bar Charts and Histograms

This can be addressed. More problematic is the fact that the Grand Total is shown as a separate response. We want the frequency histogram to display the frequencies (or number of responses) to each of the three response options (Mild, Moder- ate, or Severe symptoms). The problem is that the frequency

distribution table we produced includes a Grand Total entry. The Grand Total is useful for the numerical summary (i.e., the frequency distribution table), but when a graphical display is desired, we do not wish to include the Grand Total. Excluding the Grand Total from the chart is achieved as follows.

23944_ch03_5976 28 8/3/11 11:39:44 AM

Histograms and Bar Charts 29

FigurE 3–20 Default Display

We create a second frequency distribution table of symp- tom severity. However, we request that Excel does not include the Grand Total as part of the table. Once again, we select Pivot Table Report from the Data tab on the top menu bar. We then specify the range of the data for the table (in this case, cell C1 through cell C11) and specify a location for the new frequency distribution table. Here, we request that the top left corner of the table be placed in cell E14, as shown in Figure 3–21.

We now click the arrow to the right of Pivot Table in the dialog box and select Table Options (Figure 3–22).

This brings up the dialog box shown in Figure 3–23. We uncheck the Grand totals for columns option and click OK.

We then create the frequency distribution table by dragging the Symptom Severity variable to the Drop Row Fields Here box and then dragging the Symptom Severity variable to the Drop Data Items Here box. This produces

the frequency distribution table shown in Figure 3–24. Notice that the table contains frequencies of responses for each of the three response options and does not include the Grand Total.

We now follow the previously outlined steps to create a display of the data. Specifically, we highlight the frequency distribution table by clicking the top left corner of the table (Figure 3–25).

We now open the Charts options by clicking the Charts tab above the worksheet (Figure 3–18) and select the desired Column Chart. This produces the display shown in Figure 3–26.

We now format the display for presentation. Notice that the chart is a bar chart and not a histogram, which would be appropriate for this ordinal variable. To produce a histo- gram, we first place the cursor over any one of the bars and right click. This brings up the menu options shown in Figure 3–27.

23944_ch03_5976 29 8/3/11 11:39:45 AM

30 Creating Tables and Graphs

FigurE 3–21 Creating a Frequency Distribution Table

FigurE 3–22 Formatting the Frequency Distribution Table

23944_ch03_5976 30 8/3/11 11:39:46 AM

Histograms and Bar Charts 31

FigurE 3–23 Removing Grand Total from Frequency Distribution Table

FigurE 3–24 Frequency Distribution Table Without Grand Total

23944_ch03_5976 31 8/3/11 11:39:46 AM

32 Creating Tables and Graphs

FigurE 3–25 Selecting Data for the Display

FigurE 3–26 Default Display

23944_ch03_5976 32 8/3/11 11:39:47 AM

Histograms and Bar Charts 33

FigurE 3–27 Formatting the Bars

We select Format Data Series, which brings up the For- mat Data Series dialog box shown in Figure 3–28. We select Options from the list on the left and want to reduce the gap width between the bars from 150%, which is the default, to 0% (no gap).

Changing the gap width to 0% and clicking OK produces the histogram shown in Figure 3–29.

Next we need to add a title to the x axis (horizontal axis) to indicate that the variable being displayed is Symptom Se- verity, a title for the y axis (vertical axis) to indicate that we are displaying frequencies (as opposed to relative frequencies), and a title for the display. We also want to remove the legend

(“Total”) on the right side of the display. The latter is done by clicking the legend and then pressing the Delete key.

To add titles to the display, we open up the Formatting Palette. First, we click the display (which highlights the bor- der around the display) and click the Toolbox icon on the top menu bar. This opens the Formatting Palette shown in Figure 3–30, which has a number of formatting options for charts. (Note that the display must be highlighted before clicking the Toolbox icon to open the Formatting Palette specifically designed for formatting charts.)

Under Chart options, Excel shows the current Chart Title (“Total” in this example). To insert a new title, we enter

23944_ch03_5976 33 8/3/11 11:39:48 AM

34 Creating Tables and Graphs

FigurE 3–28 Reducing the Gap Width

FigurE 3–29 Histogram

23944_ch03_5976 34 8/3/11 11:39:49 AM

Histograms and Bar Charts 35

(Value) Axis from the options available and enter the desired title in the space provided. The formatted display is shown in Figure 3–33.

Example 3.3. In Example 4.2 in the textbook we presented data from the seventh examination of the offspring cohort in the Framingham Heart Study (n = 3539) on blood pres- sure. Systolic and diastolic blood pressures were measured as continuous variables and organized into ordinal categories. Table 4–5 in the textbook showed a frequency distribution

FigurE 3–30 Formatting Palette to Format Chart

the desired title in the space provided under Chart Title, as shown in Figure 3–31.

Notice that the new title now appears on the display in Figure 3–32. We now wish to add titles for the x axis and the y axis. This is done by clicking the arrow to the right of Chart Title and selecting Horizontal (Category) Axis from the list, as shown in Figure 3–32.

When Horizontal (Category) Axis is selected, the de- sired title is inserted in the space below. The same procedure is followed to insert a title on the y axis. We select Vertical

23944_ch03_5976 35 8/3/11 11:39:49 AM

36 Creating Tables and Graphs

FigurE 3–31 Adding a Chart Title

23944_ch03_5976 36 8/3/11 11:39:50 AM

Histograms and Bar Charts 37

FigurE 3–32 Adding a Title to the Horizontal Axis

23944_ch03_5976 37 8/3/11 11:39:50 AM

38 Creating Tables and Graphs

FigurE 3–33 Formatted Display

tablE 3–3 Frequency Distribution Table for Blood Pressure Categories

Blood Pressure Frequency Relative Frequency, %

Normal 1,206 34.1 Prehypertension 1,452 41.1 Stage I hypertension 653 18.5 Stage II hypertension 222 6.3 Total 3,533 100.0

table for the ordinal blood pressure variable and is shown in Table 3–3.

We want to generate a relative frequency histogram to present the blood pressure data. We first enter the informa- tion in Table 3–3 into Excel. The worksheet in Figure 3–34 contains the data.

To generate the relative frequency histogram, first high- light the information for the chart. Specifically, we highlight cell A1 to cell A5, which contain the variable label (Blood Pressure Category) and the four response options. Because we want to generate a relative frequency histogram, as opposed to a frequency histogram, we next need to highlight the relative frequencies in column C. To do so, we place the cursor on cell C1. Then while we hold down the Command and Control keys (simultaneously), we drag the cursor from cell C1 to cell C5. This highlights cells C1 through C5 while keeping cells A1 through A5 also highlighted, as shown in Figure 3–35.

Next, we open the Charts options (by clicking the Charts tab) and select the desired Column Charts option, which produces the display shown in Figure 3–36.

23944_ch03_5976 38 8/3/11 11:39:51 AM

Histograms and Bar Charts 39

FigurE 3–36 Chart of Relative Frequencies

FigurE 3–34 Blood Pressure Data on n = 3,533 Participants in the Framingham Heart Study

FigurE 3–35 Selecting the Data for the Display

23944_ch03_5976 39 8/3/11 11:39:51 AM

40 Creating Tables and Graphs

FigurE 3–37 Relative Frequency Histogram

Once again, we need to convert the bar chart into a his- togram by reducing the gap width to zero. This is done by placing the cursor over any of the bars and right clicking (see the previous instructions, starting with Figure 3–27). From the available options, we select Format Data Series (Figure 3–27). We then select Options and reduce the gap between the bars from 150%, which is the default, to 0% (no gap). The relative frequency histogram is shown in Figure 3–37.

Excel offers a number of other options for formatting the display. For example, we can remove the legend on the right hand side (“Relative Frequency”) by right clicking the legend and then pressing the Delete key. We can also remove the horizontal lines by right clicking any line and then pressing the Delete key. After implementing these steps, the histogram is as shown in Figure 3–38.

We can also change the title and add titles to the x axis and y axis using the Formatting Palette. To open the Format- ting Palette, we click the display and then click the Toolbox icon. Under Chart options, we select Chart Title and enter the desired title in the space provided. We then select Hori- zontal (Category) Axis and enter the desired title for the x axis. Following the same procedure, we select the Vertical (Value) Axis and enter the desired title for the y axis. The result is shown in Figure 3–39.

3.4 sCattEr diagrams A popular graphical display to illustrate the relationship be- tween two continuous variables is a scatter diagram. Scatter diagrams are useful in linear regression analysis applications (see Chapter 9 of the textbook) to assess the relationship be-

23944_ch03_5976 40 8/3/11 11:39:52 AM

Scatter Diagrams 41

FigurE 3–38 Relative Frequency Histogram

tween a continuous independent variable and a continuous dependent variable.

Example 3.4. Suppose we wish to examine the association between body mass index (BMI) and systolic blood pressure (SBP) in a sample of 12 persons (n = 12) who are not taking antihypertensive medication. The data are entered into an Excel worksheet, as shown in Figure 3–40.

To generate the scatter diagram, we first open the options available under the Charts tab (just above the worksheet, as shown in Figure 3–18). We select the XY Scatter option, which offers a number of graphical displays, including a scatter dia- gram (see the first option in Figure 3–41).

Recall that in regression analysis we use x to denote the independent variable and y to denote the dependent variable (see Chapter 9 of the textbook). In this example, BMI is the independent variable, and SBP is the dependent variable. Excel assumes that the first variable specified is the independent variable (x) and the second is the dependent variable (y). This is the way the data in Example 3.3 are organized. The XY Scat-

ter chart options include a number of graphical displays; we will use the first option, which displays all XY pairs.

To produce the scatter diagram, we first highlight the data for the display (i.e., cell B1 through cell C13, or columns B and C), as shown in Figure 3–42.

We then click the desired chart type (the first display op- tion under XY Scatter), which produces the scatter diagram shown in Figure 3–43.

For presentation purposes, we need to format the plot. Excel automatically scales the X- and y-axes from 0 to a value larger than the maximum value in the dataset. There are many variables whose theoretical minimum is much larger than 0, and BMI and SBP are two examples. Thus, we rescale the X- and y-axes to start at more reasonable values. For example, we rescale the X-axis from 20 to 45 and the y-axis from 100 to 170.

First, we rescale the X-axis to accommodate the range of the data. Specifically, we rescale the X-axis from 0 to 45 in units of 5. This is done by right clicking the X-axis, which brings up the options shown in Figure 3–44.

23944_ch03_5976 41 8/3/11 11:39:52 AM

42 Creating Tables and Graphs

FigurE 3–40 Data on BMI and SBP

FigurE 3–41 Scatter Diagram

FigurE 3–39 Relative Frequency Histogram for Presentation

23944_ch03_5976 42 8/3/11 11:39:53 AM

Scatter Diagrams 43

FigurE 3–42 Selecting the Data for the Scatter Diagram

FigurE 3–43 Scatter Plot

23944_ch03_5976 43 8/3/11 11:39:54 AM

44 Creating Tables and Graphs

FigurE 3–44 Formatting the X-Axis

Selecting Format Axis brings up the dialog box shown in Figure 3–45. We select the Scale option from the menu bar on the left hand side, and we input the minimum (20), maximum (45), and major unit (5), as shown in the figure. Clicking OK produces the scatter plot shown in Figure 3–46.

We follow the same approach to rescale the y-axis from 100 to 170 in units of 10. We can also delete the horizontal lines and the legend (“SBP”). We also insert a more ap- propriate title for the display, and we create titles for the

X-axis and y-axis using the Formatting Palette. To open the Formatting Palette, we click the display and then click the Toolbox icon. Under Charts options, we select Chart Title and enter the desired title in the space provided. We then select Horizontal (Category) Axis and enter the de- sired title for the X-axis. Following the same procedure, we select the Vertical (Value) Axis and enter the desired title for the y-axis. The formatted scatter plot is shown in Figure 3–47.

23944_ch03_5976 44 8/3/11 11:39:54 AM

Scatter Diagrams 45

FigurE 3–45 Rescaling the X-Axis

FigurE 3–46 Rescaling the X-Axis

23944_ch03_5976 45 8/3/11 11:39:55 AM

46 Creating Tables and Graphs

FigurE 3–47 Formatted Scatter Diagram

23944_ch03_5976 46 8/3/11 11:39:55 AM

Practice Problems 47

4. Using the data in Table 3–4, create a frequency distribu- tion table for drinking status, defined by the following numbers of drinks per night:

Abstinent 0 Light 1–3 Moderate 4–5 Heavy 6 or more drinks per night

5. Using the data in Table 3–4, generate a frequency histo- gram for drinking status using the appropriate display under the Charts options.

6. Using the data in Table 3–4, generate a scatter diagram to display the association between age at first drink and number of drinks per night. (Note that the sample size for analysis is n = 13.)

3.5 PraCtiCE ProblEms

1. The following data were measured in 15 college seniors (n = 15) in a cross-sectional study of alcohol consumption. All participants were asked to provide their sex, year in school, the age at which they first consumed alcohol, and the number of alcoholic drinks they consume on a typical drinking night. The data are shown in Table 3–4.

Generate frequency distribution tables for sex and year in school using the Pivot Table Report option under Data on the top menu bar.

2. Generate a frequency bar chart for sex using the frequency distribution table from Problem 1.

3. Generate a frequency histogram for year in school using the frequency distribution table from Problem 1.

tablE 3–4 Data for Practice Problems

ID Number Sex Year in School Age at First Drink* Number of Drinks/Night

1 M Freshman 14 5 2 M Senior 11 8 3 F Junior 17 3 4 M Junior 13 9 5 F Freshman 0 6 F Sophomore 15 4 7 F Freshman 15 0 8 F Freshman 0 9 M Senior 15 7 10 F Junior 21 5 11 M Junior 18 3 12 M Senior 14 6 13 F Junior 19 3 14 F Sophomore 18 2 15 M Junior 20 4

*Students who never had a drink were excluded.

23944_ch03_5976 47 8/3/11 11:39:55 AM

23944_ch03_5976 48 8/3/11 11:39:55 AM

4.1 Descriptive statistics Using excel FUnctions We now generate summary statistics on a continuous vari- able using the formulas and functions available in Excel Mac 2008.

example 4.1. In Example 4.3 in the textbook we analyzed data from a subset of 10 participants (n = 10) attending the seventh examination of the offspring cohort in the Framing- ham Heart Study. The data values were presented in Table 4–13 of the textbook and are shown in Table 4–1.

The means, standard deviations, and other statistics for each of the variables in Table 4–1 can be computed using Excel. Figure 4–1 shows the data entered into an Excel worksheet.

There are a number of functions available in Excel. The functions are accessible under the AutoSum icon on the top menu bar. Clicking the arrow to the right of the AutoSum icon opens another menu (Figure 4–2).

If we click More Functions, Excel opens the Formula Builder dialog box, shown in Figure 4–3.

The Formula Builder has a number of built-in functions and formulas that compute summary statistics for a continu- ous variable. Along with each formula, Excel provides a brief description of its use. For example, if we click the AVERAGE function, Excel provides a description of it (Figure 4–4).

The Formula Builder contains many functions that are organized by category: Arithmetic, Database, Date and Time,

In Chapter 4 of the textbook we presented summary statistics for dichotomous, ordinal, categorical, and continuous vari- ables. We discussed both numerical and graphical summa- ries. Numerical summaries for continuous variables include the sample mean, standard deviation, median, and quartiles. Numerical summaries for ordinal and categorical data use fre- quency distribution tables, and these were discussed in detail in Chapter 3 of this workbook. We also presented several graphi- cal displays for sample data. Numerical summaries for continu- ous variables measured in a sample are discussed here.

The most appropriate numerical summary for a continu- ous variable depends on whether there are outliers or not. Regardless of whether there are outliers, the summary should always include the sample size, a measure of central tendency or a typical value (e.g., the mean or median), and a measure of variability (e.g., standard deviation or interquartile range). Here we generate summaries that include numerous statistics; the investigator must choose those that are most appropriate to summarize a particular characteristic.

Excel for the PC has a built-in Data Analysis Toolpak, which contains a number of tools to generate statistical analy- ses. Excel Mac 2008 does not have the built-in toolpak, but there are commercially available statistical analysis tools that can be purchased to conduct statistical analysis using Excel for the Mac. A popular program is StatPlus®:mac 2009 (Ana- lystSoft Inc., 2001–2009). StatPlus:mac offers a number of tools to generate statistical analyses. Here we illustrate the calculation of summary statistics using available Excel Mac 2008 formulas and functions.

Chapter 4 Summarizing Continuous

Variables in a Sample

23944_ch04_5976 49 8/3/11 11:40:59 AM

50 Summarizing Continuous Variables in a Sample

table 4–1 Subsample of Ten Participants Attending the Seventh Examination of the Framingham Offspring Study

Participant ID Systolic Blood

Pressure Diastolic Blood

Pressure Total Serum Cholesterol Weight Height Body Mass Index

1 141 76 199 138 63.00 24.4 2 119 64 150 183 69.75 26.4 3 122 62 227 153 65.75 24.9 4 127 81 227 178 70.00 25.5 5 125 70 163 161 70.50 22.8 6 123 72 210 206 70.00 29.6 7 105 81 205 235 72.00 31.9 8 113 63 275 151 60.75 28.8 9 106 67 208 213 69.00 31.5 10 131 77 159 142 61.00 26.8

FigUre 4–1 Data for Analysis Entered into Excel

Math and Trigonometry, Statistical, and Text functions. Figure 4–5 shows the beginning of the list of statistical functions.

Table 4–2 contains a list of the Excel Mac 2008 statistical functions that are relevant to summarizing a continuous vari- able in a sample. The use of each function is also indicated in Table 4–2. We illustrate the use of these functions using the data in Example 4.1.

Notice that all of the statistical functions require the spec- ification of the data range. The data range is defined by the addresses of the cells containing the first and last observations in the dataset, separated by a colon (e.g., B1:B11). It is also im- portant to note that Excel uses an algorithm to determine the median and the quartiles (with the MEDIAN and QUARTILE functions, respectively) that involves interpolation. When

23944_ch04_5976 50 8/3/11 11:41:00 AM

Descriptive Statistics Using Excel Functions 51

FigUre 4–2 Functions Available in Excel Mac 2008

FigUre 4–3 The Formula Builder

23944_ch04_5976 51 8/3/11 11:41:00 AM

52 Summarizing Continuous Variables in a Sample

FigUre 4–4 The Average Function

FigUre 4–5 Statistical Functions

23944_ch04_5976 52 8/3/11 11:41:01 AM

Descriptive Statistics Using Excel Functions 53

table 4–2 Excel Mac 2008 Functions for Descriptive Statistics

Statistic Excel Mac 2008 Function Formula/Description

Sample size COUNT(data range) n Sample mean AVERAGE(data range)

X X n

= Σ

Sample standard deviation

STDEV(data range) s X X

n = −

− Σ( )2

Median MEDIAN(data range) or QUARTILE(data range,2)

Middle value (50% above and 50% below)

First quartile QUARTILE(data range,1) Holds 25% of the values below it Third quartile QUARTILE(data range,3) Holds 25% of the values above it Mode MODE(data range) Most frequent value; if there is no value that appears more than any other, Excel

displays “#N/A” Sample variance VAR(data range)

s X X n

2 2

1 = −

− Σ( )

Kurtosis KURT(data range) Reflects the thickness of the tails of a distribution of a continuous characteristic as compared to a normal distribution (see Chapter 5 of the textbook); kurtosis of a normal distribution is 0

Skewness SKEW(data range) Reflects the symmetry of a distribution of a continuous characteristic as compared to a normal distribution (see Chapter 5 of the textbook); skewness of a normal distribution is 0

Minimum MIN(data range) or QUARTILE(data range,0)

Smallest value in the dataset

Maximum MAX(data range) or QUARTILE(data range,4)

Largest value in the dataset

Sum SUM(data range) Sum of the observations, ΣX

we compute the median and quartiles by hand, as described in Chapter 4 of the textbook, we do not interpolate, and thus Excel may produce different values for the median and quartiles than those we determine by hand. The results are not likely to be different when the sample size is large.

We now return to Example 4.1 and the data shown in Table 4–1 and summarize, or generate descriptive statis- tics, for the systolic blood pressures (SBP) measured in the sample. Specifically, we first compute the sample size using the COUNT function. We enter “n” in cell A13, and then “=COUNT(B2:B11)” in cell B13, as shown in Figure 4–6.

Next, we compute the sample mean using the AVER- AGE function. We enter “Sample Mean” in cell A14, and then “=AVERAGE(B2:B11)” in cell B14, as shown in Figure 4–7.

Next, we compute the sample standard deviation using the STDEV function. We enter “Sample Std Dev” in cell A15, and then “=STDEV(B2:B11)” in cell B15, as shown in Figure 4–8.

We now add the first quartile, median, and third quartile to the summary using the QUARTILE function. Note in Table 4–2 that the QUARTILE function requires two arguments: the data range and an integer to request the specific quartile (e.g., 1 = first quartile, 2 = median, and 3 = third quartile). The first quartile, median, and third quartile are shown in Figure 4–9.

The final statistics are the minimum and maximum, computed using the MIN and MAX functions, respectively (Figure 4–10).

23944_ch04_5976 53 8/3/11 11:41:02 AM

54 Summarizing Continuous Variables in a Sample

FigUre 4–6 Computing the Sample Size

FigUre 4–7 Computing the Sample Mean

23944_ch04_5976 54 8/3/11 11:41:02 AM

Descriptive Statistics Using Excel Functions 55

FigUre 4–8 Computing the Sample Standard Deviation

FigUre 4–9 Computing the Median, First, and Third Quartiles

23944_ch04_5976 55 8/3/11 11:41:03 AM

56 Summarizing Continuous Variables in a Sample

first format them to a reasonable number of decimal places. Recall that in Chapter 4 of the textbook, we suggest report- ing summary statistics to one more decimal place than the number of decimal places measured. For example, SBPs are measured in units. Summary statistics (e.g., the mean and standard deviation) can be reported with one decimal place (i.e., to the nearest tenths place), as shown in Figure 4–12.

Finally, it is important to note that in Excel, and in most statistical computing packages, there are often several ways to perform the same analysis. The analyst can choose the method with which they are the most comfortable or that best suits their style.

When the formulas are entered into the worksheet to summarize SBP, we can copy these formulas to column C through column G to generate summary statistics for diastolic blood pressure, total serum cholesterol, weight, height, and body mass index. Specifically, we highlight cell B13 through cell B22 and click the Copy icon on the top menu bar. We then highlight cell C13 through cell G22 and click the Paste icon on the top menu bar. Excel updates the cell referencing so that the statistical functions compute summary statistics based on the data in the appropriate column of the worksheet. The sum- mary statistics for all variables are shown in Figure 4–11.

Prior to presenting the statistics shown in Figure 4–11, we

FigUre 4–10 Descriptive Statistics on Systolic Blood Pressure

23944_ch04_5976 56 8/3/11 11:41:03 AM

Descriptive Statistics Using Excel Functions 57

FigUre 4–11 Descriptive Statistics on All Variables

FigUre 4–12 Formatted Summary Statistics

23944_ch04_5976 57 8/3/11 11:41:04 AM

58 Summarizing Continuous Variables in a Sample

before any treatment is administered). The data are as follows:

120 148 112 128 138 143 145 156 135 160 150 142 145 150 163

Use Excel functions to compute the sample mean, stan- dard deviation, median, and quartiles.

4. The following are baby height measurements, in centi- meters, for a sample of women participating in a study of pregnancy outcomes:

28 48 30 62 41 49 48 51 29 39

Use Excel functions to compute summary statistics.

4.2 practice problems

1. A study is conducted to estimate the mean total choles- terol level in children 2–6 years of age. A sample of nine participants (n = 9) is selected, and their total cholesterol levels are measured as follows:

185 225 240 196 175 180 194 147 223

a. Use Excel functions to compute the sample mean, standard deviation, and median.

b. Use the QUARTILE function to compute the first and third quartiles.

2. Data were collected as part of a study of coffee consump- tion among graduate students. The following reflect cups per day consumed:

3 4 6 8 2 1 0 2

a. Use Excel functions to compute the sample mean, standard deviation, and median.

b. Use the QUARTILE function to compute the first and third quartiles.

3. In a study of a new antihypertensive medication, SBPs are measured at baseline (or at the start of the study,

23944_ch04_5976 58 8/3/11 11:41:04 AM

The inputs for the function are the same as those we would use in computing probabilities by hand with the bino- mial distribution model (i.e., x, n, and p). Excel requires one additional input, labeled “cumulative”. The last entry in the BINOMDIST function is a logical value (i.e., one whose re- sponses are true or false). We use the cumulative distribution function by specifying “true”, or we do not use the cumulative distribution function by specifying “false”. The cumulative distribution function returns the probability of observing x or fewer successes. For example, if we specify “true” and indicate x = 5 in the function, then Excel computes P(X ≤ 5). In con- trast, if we specify “false” and indicate x = 5 in the function, then Excel computes the individual or point probability, P(X = 5). We illustrate the use of the function in Example 5.1.

Example 5.1. In Example 5.9 in the textbook, we presented an example assessing the extent to which adults with allergies report relief from allergic symptoms with a specific medi- cation. We know that the medication is effective in 80% of patients with allergies. If we provide the medication to 10 patients with allergies, what is the probability that it is effec- tive in exactly seven patients?

For this example, n = 10, p = 0.80, and x = 7. We now use Excel to compute the desired probability. In Figure 5–1, we entered n, p, and x into an Excel worksheet.

We wish to compute the probability of seven successes when n = 10, and the probability of success for any individual is 0.80. We use the BINOMDIST function and specify the cell locations for x (A2), n (B2), and p (C2). Because we want to compute the probability of exactly seven successes, we set “cumulative=false”. The specification of the formula is shown

Excel Mac 2008 has a number of probability functions that can be used to compute probabilities or to find percentiles. In Chapter 5 of the textbook we discussed in detail two prob- ability models, the binomial and normal distributions, which are appropriate for dichotomous and continuous outcomes, respectively. As we discussed in Chapter 5 of the textbook, there are many other probability distributions that describe discrete and continuous outcomes; we focused exclusively on these two. Excel has a number of probability functions that can be used to compute probabilities for various distributions; here we focus on the binomial and normal distributions.

5.1 Computing probabilitiEs with thE binomial Distribution In Chapter 5 of the textbook, we discussed the binomial distri- bution model and computed probabilities using the binomial distribution model:

P successes( ) ! ! !

( )x n x n x

p px n x= −( ) −

−1

where n denotes the number of times the application or pro- cess is repeated (sometimes called the number of trials), x denotes the number of successes (out of n) of interest, and p is the probability of success for any individual.

To use the binomial distribution model, we need to spec- ify n, p, and x. Excel has a probability function to compute probabilities from a binomial distribution. The function is used as follows:

=BINOMDIST(x, n, p, cumulative)

Chapter 5 Working with Probability

Functions

23944_ch05_5976 59 8/3/11 11:41:36 AM

60 Working with Probability Functions

in Figure 5–2. When we press the Enter key, the probability is computed. The result is shown in Figure 5–3.

We can also compute the probability of observing seven or fewer successes by specifying “cumulative=true” in the function call. This is shown in Figure 5–4. The result is shown in Figure 5–5.

Thus, P(X = 7) = 0.201 and P(X ≤ 7) = 0.322. In Fig- ure 5–6 we enter all possible values of x for n = 10 (i.e., 0 through 10) and compute the probability of exactly x successes as well as the probability of observing x successes or fewer, using the BINOMDIST function with “cumulative=false” and “cumulative=true”, respectively. Notice that some of the probabilities are very small (e.g., P(X = 0) = 1.024E-07 = 0.0000001024), and notice the relationship between the indi- vidual (or point) and cumulative probabilities.

FigurE 5–1 Inputs for Binomial Distribution Function

FigurE 5–2 Using the BINOMDIST Function

FigurE 5–3 Binomial Probabilities

FigurE 5–4 Using the Cumulative Distribution Function

23944_ch05_5976 60 8/3/11 11:41:36 AM

Computing Probabilities with the Normal Distribution 61

FigurE 5–5 Cumulative Binomial Probabilities

FigurE 5–6 Individual (Point) Probabilities and Cumulative Binomial Probabilities

5.2 Computing probabilitiEs with thE normal Distribution In Chapter 5 of the textbook, we presented the normal distri- bution model, and we showed how to compute probabilities using the standard normal distribution (Z) in Table 1 in the Appendix of the textbook. Here we use Excel to compute probabilities for variables that are assumed to follow a nor- mal distribution.

Excel has two probability functions to compute probabili- ties from normal distributions. The first computes probabili- ties from the standard normal distribution, and the function is as follows:

=NORMSDIST(z)

The input for the function is the z value. The function returns the probability of observing a value from the standard normal distribution less than or equal to z (i.e., Excel returns the area under the standard normal curve to the left of or less than z). Recall for the normal distribution that the area less than z is equivalent to the area less than or equal to z. Figure 5–7 displays a worksheet with various values of z entered into column A. The NORMSDIST function is used in column B to generate probabilities less than z. For example, in cell B2 we enter “=NORMSDIST(A2)”.

Recall that 0 is the mean of the standard normal dis- tribution, and thus P(Z < 0) = 0.5. In addition, the standard deviation is 1, so P(–1 < Z < 1) = P(Z < 1) – P(Z < –1) = 0.841

23944_ch05_5976 61 8/3/11 11:41:37 AM

62 Working with Probability Functions

The inputs for the function are the x value, the mean (m) and standard deviation (s) of the normal distribution, and a logical value labeled “cumulative”. For almost all ap- plications we use the cumulative distribution function by specifying “true”. This returns the probability of observing a value from the normal distribution with specified mean and standard deviation less than or equal to x (i.e., Excel returns the area under the normal curve to the left of or less than x). Again, this is equivalent to the area less than or equal to x. If we specify cumulative as “false”, the function returns the probability of observing x. Figure 5–8 displays a work- sheet with x entered into column A. We specify the mean and standard deviation of the distribution in columns B and C, respectively, and we use the NORMDIST function to gener- ate the probability of a value less than x. For example, in cell D2 we enter “=NORMDIST(A2,B2,C2,true)”. For a normal distribution with mean = 70 and standard deviation = 10, P(X < 85) = 0.9332.

Example 5.2. In Example 5.11 in the textbook, we analyzed body mass index (BMI), which is assumed to be normally distributed for specific sex and age groups. The mean BMI for men aged 60 years is 29 with a standard deviation of 6, and for women aged 60 years the mean is 28 with a standard deviation of 7. For men aged 60 years, we now use Excel to compute the following: P(X < 35), P(X < 41), and P(X < 30). The results are shown in Figure 5–9.

Suppose we now wish to compute the probability that a male has a BMI between 30 and 35 (i.e., P(30 < X < 35)). This can be done using the NORMDIST function to compute the

– 0.159 = 0.682 (68% of the observations in a normal distribu- tion fall between the mean minus one standard deviation and the mean plus one standard deviation).

In most applications, we analyze distributions that are normal with mean m and standard deviation s (which are not 0 and 1, respectively). Excel has a second function that computes probabilities from any normal distribution, and the function is:

=NORMDIST(x, m ,s, cumulative)

FigurE 5–7 Computing Probabilities for the Standard Normal Distribution

FigurE 5–8 Computing Probabilities for the Normal Distribution

FigurE 5–9 Probabilities of BMI in Men

23944_ch05_5976 62 8/3/11 11:41:37 AM

Finding Percentiles of the Normal Distribution 63

5.3 FinDing pErCEntilEs oF thE normal Distribution Excel can also be used to compute percentiles for the standard normal and any normal distribution. The two functions are NORMSINV and NORMINV, respectively. The inputs for the functions are shown below. Recall from Chapter 5 in the textbook that a percentile is a score that holds a specified per- centage or proportion of scores below it. For example, the 80th percentile is the score that holds 80% of the scores below it.

The function to compute percentiles for the standard normal distribution is as follows:

=NORMSINV(probability)

The input for the function is the desired percentile, entered as a probability or proportion. For example, to compute the 80th or the 95th percentile, we specify 0.80 or 0.95, respectively.

The function to compute percentiles for any normal dis- tribution is as follows:

=NORMINV(probability, m ,s)

The inputs for the function are the desired percentile entered as a probability, the mean (m), and standard deviation (s) of the normal distribution.

Example 5.3. Using the data in Example 5.2, we now use Excel to compute the 90th and 95th percentiles of BMI for men and women. The results are shown in Figure 5–12. In

probabilities that a male has a BMI less than 35 and less than 30 (as in Figure 5–9) and subtracting (Figure 5–10). The de- sired probability is computed by subtraction: P(30 < X < 35) = P(X < 35) – P(X < 30) = 0.275.

Now consider BMI in women. What is the probability that a female aged 60 years has a BMI less than 30 and less than 35? What is the probability that a female aged 60 years has a BMI between 30 and 35? We use the same approach, but recall that for women aged 60 years the mean is 28 and the standard deviation is 7. The results are shown in Figure 5–11.

FigurE 5–10 Working with Probabilities

FigurE 5–11 Working with Probabilities FigurE 5–12 Percentiles of BMI for Men and Women

23944_ch05_5976 63 8/3/11 11:41:38 AM

64 Working with Probability Functions

3. A study is conducted to assess the impact of caffeine con- sumption, smoking, alcohol consumption, and physical activity on cardiovascular disease. Suppose that 40% of participants consume caffeine and smoke. If eight par- ticipants are evaluated, what is the probability that:

a. Exactly half of them consume caffeine and smoke?

b. At most, six participants consume caffeine and smoke?

4. A recent study of cardiovascular risk factors reported that 30% of adults met criteria for hypertension. If 15 adults are assessed, what is the probability that:

a. Exactly five meet the criteria for hypertension?

b. None meet the criteria for hypertension?

c. Less than or equal to seven meet the criteria for hypertension?

5. Diastolic blood pressures are assumed to follow a normal distribution with a mean of 85 and a standard deviation of 12.

a. What proportion of people have diastolic blood pres- sure less than 90?

b. What proportion of people have diastolic blood pres- sure between 80 and 90?

c. If someone has a diastolic blood pressure of 100, what percentile is he or she in?

men, 90% of BMIs are below 36.7, and 95% are below 38.9. In women, 90% of BMIs are below 37.0, and 95% are below 39.5.

5.4 praCtiCE problEms

1. Total cholesterol in children aged 10–15 years is assumed to follow a normal distribution with a mean of 191 and a standard deviation of 22.4.

a. What proportion of children aged 10–15 years have total cholesterol between 180 and 190?

b. What proportion of children aged 10–15 years would be classified as hyperlipidemic? Assume that hyperlipi- demia is defined as a total cholesterol level over 200.

c. What is the 90th percentile of cholesterol?

2. Among coffee drinkers, men drink a mean of 3.2 cups per day with a standard deviation of 0.8 cups. Assume the number of coffee drinks per day follows a normal distribution.

a. What proportion of men drink two cups or more per day?

b. What proportion of men drink no more than four cups per day?

c. If the top 5% of coffee drinkers are considered heavy coffee drinkers, what is the minimum number of cups per day consumed by a heavy coffee drinker? Hint: Find the 95th percentile.

23944_ch05_5976 64 8/3/11 11:41:38 AM

we use specific Excel functions to produce the Z or t values that reflect the desired confidence level and then construct the confidence interval.

6.1 ConfidenCe intervals for one sample, Continuous outCome In Chapter 6 of the textbook, we presented the following for- mulas for confidence intervals for the mean of a continuous variable in one sample:

n X Z s n

≥ ±30 (Find Z in Table 1B in the Appendix)

n X t s n

< ±30 (Find t in Table 2 in the Appendix, df = n − 1)

When computing the confidence intervals by hand, we computed the sample size, mean, and standard deviation and then used Table 1B or Table 2 in the Appendix to find the ap- propriate Z or t value to reflect the desired confidence level. We use Excel to compute summary statistics and to determine the appropriate Z or t value for the confidence interval. When all of the requisite components are determined, we construct the confidence interval.

Excel has a function that computes Z values that can be used in confidence intervals. The function is as follows:

=NORMSINV(lower-tail area)

To use this function for confidence intervals, we specify the area under the curve in the lower tail of the standard normal distribution. For example, for a 95% confidence in- terval, the area in the lower tail is 0.975. Figure 6–1 shows

In Chapter 6 of the textbook we presented formulas to generate confidence intervals for means (m) and proportions (p) in one sample and for differences in means (m1 − m2) and differences in proportions (p1 − p2) in two independent samples. We also discussed confidence intervals for the mean difference (md) when two samples were matched or paired. For each applica- tion we used the same general approach. Confidence intervals for each parameter take the following form:

Point estimate ± Margin of error

The point estimate depends on the parameter being es- timated. For example, when estimating the mean of a popu- lation, m , the point estimate is the sample mean, X . When estimating the population proportion, p, the point estimate is the sample proportion, p̂. The margin of error includes two components. The first component is from a probability distribution (e.g., Z or t) and reflects the selected confidence level (e.g., 90%, 95%). The second component is the standard error of the point estimate. For example, the standard error of the sample mean, X , is SE

s n

= . The standard error of the

sample proportion, p̂, is as follows:

SE p p n

= − ˆ( ˆ)1

In Chapter 6 of the textbook we presented formulas for confidence intervals for various parameters (see table in Sec- tion 6.7 of the textbook for details). Here we use Excel Mac 2008 to generate confidence intervals for various parameters. Excel does not generate confidence intervals directly; instead,

Chapter 6 Confidence Interval Estimates

23944_ch06_5976 65 8/3/11 11:42:23 AM

66 Confidence Interval Estimates

example 6.1. In Example 6.1 in the textbook we analyzed data on n = 3,539 participants who attended the seventh ex- amination of the offspring cohort in the Framingham Heart Study. Descriptive statistics on variables that were measured in the sample are shown in Table 6–1.

We use Excel to generate 95% confidence intervals for each characteristic. Because the sample size is large, we use the confidence interval formula with Z as opposed to t. The data are entered into an Excel worksheet, as shown in Figure 6–2. First we compute the standard errors for each characteristic as SE s

n = and place these in column E. For example, for

systolic blood pressure (SBP), the following is entered into cell E2: “=D2/sqrt(B2)”. We then compute the Z scores for 95% confidence intervals and place these in column F. The computation is the same for each characteristic; for example, in cell F2 we enter “=NORMSINV(0.975)”. The standard er-

the standard normal distribution, Z, and the Z values that hold the middle 95% of the distribution (P(−1.96 < X < 1.96) = 0.95).

To produce the Z value for a 95% confidence interval, we specify “=NORMSINV(0.975)”, which returns 1.96.

Excel has a second function that computes t values that can be used in calculating confidence intervals. The function is invoked as follows:

=TINV(total tail area, df)

To use this function for confidence intervals, we specify the total area in the tail of the t distribution along with de- grees of freedom, df. For a 95% confidence interval, the total tail area is 0.05, and for one sample the degrees of freedom are df = n − 1. We now illustrate the use of these formulas to compute confidence intervals.

figure 6–1 Z Value for 95% Confidence

–3 –2 –1 0 1 2

0.025 0.025

0.95

table 6–1 Descriptive Statistics, Framingham Heart Study Offspring

Characteristic n Mean (X) Standard Deviation (s)

Systolic blood pressure 3,534 127.3 19.0 Diastolic blood pressure 3,532 74.0 9.9 Total serum cholesterol 3,310 200.3 36.8 Weight 3,506 174.4 38.7 Height 3,326 65.957 3.749 Body mass index 3,326 28.15 5.32

figure 6–2 Data for Confidence Intervals for m

23944_ch06_5976 66 8/3/11 11:42:23 AM

Confidence Intervals for One Sample, Continuous Outcome 67

example 6.2. In Example 6.2 in the textbook, we presented data on a subsample of 10 participants (n = 10) who attended the seventh examination of the Framingham Offspring Study. Descriptive statistics on variables measured in the subsample are shown in Table 6–2. We use Excel to generate 95% con- fidence intervals for each characteristic. Because the sample size is small, we use the confidence interval formula with t as opposed to Z.

The data are entered into an Excel worksheet as shown in Figure 6–5. First we compute the standard errors for each

rors and Z values are shown in Figure 6–3. (Note that had we wanted 90% confidence intervals, we would have specified “=NORMSINV(0.95)” in column F.)

We now compute the lower and upper limits of the 95%

confidence intervals using X Z s n

± . For SBP, the lower

limit is “=C2-(F2*E2)” and the upper limit is “=C2+(F2*E2)”. (The product of the Z value and the standard error produce the margin of error.) The confidence intervals are shown in Figure 6–4.

figure 6–3 Standard Errors and Z Values for 95% Confidence

figure 6–4 95% Confidence Intervals

23944_ch06_5976 67 8/3/11 11:42:24 AM

68 Confidence Interval Estimates

characteristic as SE s n

= and place these in column E. For

example, for SBP, the following is entered into cell E2: “=D2/ sqrt(B2)”. We then compute the t scores for 95% confidence intervals and place these in column F. The computation is the same for each characteristic; for example, in cell F2 we enter “=TINV(0.05,B2-1)”. The standard errors and t values are shown in Figure 6–6.

We now compute the lower and upper limits of the 95% confidence intervals using X t s

n ± . For SBP, the lower limit

is “=C2-(F2*E2)” and the upper limit is “=C2+(F2*E2)”. The confidence intervals are shown in Figure 6–7.

example 6.3. In Example 4.3 in the textbook we presented data on the subset of 10 participants (n = 10) that are sum-

table 6–2 Descriptive Statistics, Framingham Heart Study Offspring Subsample

Characteristic n Mean (X̀ ) Standard Deviation (s)

Systolic blood pressure 10 121.2 11.1 Diastolic blood pressure 10 71.3 7.2 Total serum cholesterol 10 202.3 37.7 Weight 10 176.0 33.0 Height 10 67.175 4.205 Body mass index 10 27.26 3.10

figure 6–5 Data for Confidence Intervals for m

figure 6–6 Standard Errors and t Values for 95% Confidence

23944_ch06_5976 68 8/3/11 11:42:25 AM

Confidence Intervals for One Sample, Continuous Outcome 69

B13, cell B14, and cell B15, as follows: “=COUNT(B2:B11)”, “=AVERAGE(B2:B11)”, and “=STDEV(B2:B11)”, respectively. The results are shown in Figure 6–9.

Next we copy the formulas in cell B13 through cell B15 into cell C13 through cell G15 to produce the same summary statistics for the remaining variables in the worksheet. The results are shown in Figure 6–10.

To compute confidence intervals for each variable, we follow the procedure illustrated in Example 6.2. Specifically, we compute the standard errors, the t statistics for the desired confidence level (e.g., 95%), the margins of error, and then

marized in Example 6.2. The data values were presented in Table 4–13 of the textbook and are shown here in Table 6–3. The data are entered into an Excel worksheet and shown in Figure 6–8.

In Chapter 4 of this workbook we used Excel functions to compute summary statistics on continuous variables. For ex- ample, we used the COUNT, AVERAGE, and STDEV functions to compute the sample size, mean, and standard deviations, respectively. Recall that each function requires the specifica- tion of the data range. Here we enter the formulas to compute the sample size, mean, and standard deviation for SBP in cell

figure 6–7 95% Confidence Intervals

table 6–3 Subsample of Ten Participants Attending the Seventh Examination of the Framingham Offspring Study

Participant ID Systolic Blood Pressure

Diastolic Blood Pressure

Total Serum Cholesterol Weight Height Body Mass Index

23944_ch06_5976 69 8/3/11 11:42:25 AM

70 Confidence Interval Estimates

through cell B20 and from cell C16 through cell G20 to com- pute 95% confidence intervals for the remaining variables in the dataset. The results are shown in Figure 6–12.

the lower and upper limits of the confidence interval. The formulas are entered in cell B16 through cell B20, as shown in Figure 6–11.

We now copy the formulas to compute the standard error; the t statistic (for a 95% confidence level); the margin of error; and the lower and upper 95% confidence limits from cell B16

figure 6–8 Data From Subsample of Ten Participants in Framingham Offspring Study

figure 6–9 Summary Statistics for SBP

figure 6–10 Summary Statistics for All Variables

figure 6–11 95% Confidence Interval for SBP

23944_ch06_5976 70 8/3/11 11:42:26 AM

Confidence Intervals for One Sample, Dichotomous Outcome 71

The sample proportions and Z values are shown in Figure 6–14.

In Figure 6–15 we compute the upper and lower lim-

its of the 95% confidence interval using ˆ ˆ( ˆ)p Z p p

n ± −1 .

6.2 ConfidenCe intervals for one sample, diChotomous outCome In Chapter 6 of the textbook, we presented the following formula for the confidence interval for a proportion (of a di- chotomous variable) in one sample:

ˆ ˆ( ˆ)p Z p p n

± −1 (Find Z in Table 1B in the Appendix)

When computing the confidence intervals by hand, we computed the sample size and sample proportion, and then we used Table 1B in the Appendix to find the appropriate Z value to reflect the desired confidence level. We now use Excel to compute the sample proportion and to determine the ap- propriate Z value for the confidence interval.

example 6.4. In Example 6.4 in the textbook we analyzed data on the prevalence of cardiovascular disease (CVD) mea- sured in men and women at the fifth examination of the Framingham Offspring Study. The data are shown in Table 6–4. The data are entered into an Excel worksheet and are shown in Figure 6–13.

In Figure 6–14 we compute the sample proportions with prevalent CVD by dividing the numbers of participants with prevalent CVD by the respective totals; we then generate the Z values for 95% confidence using the NORMSINV function.

figure 6–12 95% Confidence Intervals for All Variables

table 6–4 Prevalence of CVD in Men and Women Attending the Fifth Examination of the Framingham Offspring Study

Free of CVD Prevalent CVD Total

Men 1,548 244 1,792 Women 1,872 135 2,007 Total 3,420 379 3,799

figure 6–13 Prevalence of CVD in Men and Women

figure 6–14 Sample Proportions and Z Scores

23944_ch06_5976 71 8/3/11 11:42:27 AM

72 Confidence Interval Estimates

in each sample. We then computed the pooled estimate of the common standard deviation, Sp, and then used Table 1B or Table 2 in the Appendix to find the appropriate Z or t value to reflect the desired confidence level. We now use Excel to compute summary statistics and to determine the appropri- ate Z or t value for the confidence interval. When all of the requisite components are determined, we construct the con- fidence interval.

example 6.5. In Example 6.5 in the textbook we analyzed data on n = 3,539 participants who attended the seventh ex- amination of the offspring cohort in the Framingham Heart Study and compared men and women on the following char- acteristics. The data are shown in Table 6–5.

We use Excel to generate 95% confidence intervals for the difference in means between men and women. Because the sample sizes are large, we use the confidence interval formula with Z as opposed to t. The data are entered into an Excel worksheet as shown in Figure 6–16. First we compute the pooled estimates of the common standard deviations

Sp n s n s

n n =

− + − + −

( ) ( )1 12 2 22

1 2

1 1 2

, and place these in column H.

For SBP, the following is entered into cell H3: “=SQRT(((B3- 1)*D3^2+(E3-1)*G3^2)/(B3+E3-2))”. We then compute the Z scores for 95% confidence intervals and place these in column I. The computation is the same for each characteristic; for ex- ample, in cell I3 we enter “=NORMSINV(0.975)”. The pooled estimates of the common standard deviations and Z values are shown in Figure 6–17.

Notice how the formula is implemented. For example, the lower 95% confidence limit for the total sample is in cell G4 and is computed as “=E4-F4*sqrt(E4*(1-E4)/D4)”. The upper 95% confidence limit for the total sample is in cell H4 and is computed as “=E4+F4*sqrt(E4*(1-E4)/D4)”.

6.3 ConfidenCe intervals for two independent samples, Continuous outCome In Chapter 6 of the textbook, we presented the following for- mulas for confidence intervals for the difference in means of a continuous variable in two independent samples:

n n X X ZSp n n1 2 1 2 1 2

30 30 1 1≥ ≥ −( ) ± +and

(Find Z in Table 1B in the Appendix)

n1 < 30 or n2 < 30

or n X X tSp n n2 1 2 1 2

30 1 1< −( ) ± +

(Find t in Table 2 in the Appendix, df = n1 + n2 − 2)

where Sp n s n s

n n =

− + − + −

( ) ( )1 12 2 22

1 2

1 1 2

When computing the confidence intervals by hand, we computed the sample sizes, means, and standard deviations

figure 6–15 95% Confidence Interval for the Population Proportion

23944_ch06_5976 72 8/3/11 11:42:29 AM

Confidence Intervals for Two Independent Samples, Continuous Outcome 73

figure 6–16 Data for Confidence Intervals for (m1 – m2)

table 6–5 Summary Statistics on Men and Women Attending the Seventh Examination of the Framingham Heart Study

Men Women

Characteristic n X – s n X – s

Systolic blood pressure 1,623 128.2 17.5 1,911 126.5 20.1 Diastolic blood pressure 1,622 75.6 9.8 1,910 72.6 9.7 Total serum cholesterol 1,544 192.4 35.2 1,766 207.1 36.7 Weight 1,612 194.0 33.8 1,894 157.7 34.6 Height 1,545 68.9 2.7 1,781 63.4 2.5 Body mass index 1,545 28.8 4.6 1,781 27.6 5.9

We now compute the point estimates for the difference in means ( )X X1 2− and the lower and upper limits of the 95%

confidence intervals using X X ZSp n n1 2 1 2

1 1−( ) ± + . The confidence intervals are shown in Figure 6–18.

The 95% confidence limits are shown in columns J and K

for each characteristic. The same approach is used to compute confidence intervals for the difference in means when the sample sizes are small (i.e., when one or both of the sample sizes are less than 30), except that the TINV function is used to compute the appropriate value from the t distribution with degrees of freedom equal to n1 + n2 − 2.

23944_ch06_5976 73 8/3/11 11:42:30 AM

74 Confidence Interval Estimates

figure 6–17 Sp and Z Values for 95% Confidence

figure 6–18 Point Estimates and 95% Confidence Intervals

6.4 ConfidenCe intervals for matChed samples, Continuous outCome In Chapter 6 of the textbook, we presented the following for- mulas for confidence intervals for the mean difference of a continuous variable in two dependent or matched samples:

n X Z s

nd d≥ ±30 (Find Z in Table 1B in the Appendix)

n X t s

nd d< ±30 (Find t in Table 2 in the Appendix,

df = n − 1)

where n is the number of participants or pairs, and Xd and sd are the mean and standard deviation of the difference scores, respectively (where differences are computed on each partici- pant or between members of a matched pair).

23944_ch06_5976 74 8/3/11 11:42:31 AM

Confidence Intervals for Two Independent Samples, Continuous Outcome 75

table 6–6 Systolic Blood Pressures Measured at Examinations 6 and 7

Subject Identification Number Examination 6 Examination 7

1 168 141 2 111 119 3 139 122 4 127 127 5 155 125 6 115 123 7 125 113 8 123 106 9 130 131 10 137 142 11 130 131 12 129 135 13 112 119 14 141 130 15 122 121

We now use Excel to compute summary statistics and to determine the appropriate Z or t value for the confidence in- terval. When all of the requisite components are determined, we construct the confidence interval.

example 6.6. In Example 6.7 in the textbook we analyzed SBPs measured at the sixth and seventh examinations of the offspring in the Framingham Heart Study in a subsample of 15 randomly selected participants (n = 15). The data are shown in Table 6–6.

We use Excel to compute difference scores, to generate summary statistics on the difference scores, and to generate a 95% confidence interval for the mean difference in SBP over time. The data are entered into an Excel worksheet as shown in Figure 6–19. Difference scores are computed for each par- ticipant by subtracting the SBP measured at exam 6 from that measured at exam 7.

We next use Excel Mac 2008 COUNT, AVERAGE, and STDEV functions to compute the sample size, mean, and standard deviation, respectively, on the difference scores. The summary statistics on the difference scores are shown in Figure 6–20.

Next, we compute the standard error, t statistic for 95% confidence, margin of error, upper and lower limits of the

figure 6–19 Data on SBPs Measured at Exams 6 and 7 and Difference Scores

figure 6–20 Summary Statistics on Difference Scores

23944_ch06_5976 75 8/3/11 11:42:31 AM

76 Confidence Interval Estimates

to compute the sample proportions and to determine the ap- propriate Z value for the confidence interval.

example 6.7. In Example 6.4 of this workbook we analyzed data on the prevalence of CVD measured in men and women at the fifth examination of the Framingham Offspring Study. The data are shown in Table 6–4. They are entered into an Excel worksheet, as shown in Figure 6–22.

In Figure 6–23 we compute the sample proportions with prevalent CVD by dividing the numbers of participants with prevalent CVD by the respective totals. We then compute the point estimate as the difference in sample proportions and generate the Z value for 95% confidence using the NORM- SINV function.

95% confidence interval following the procedure illustrated in Example 6.2. The results are shown in Figure 6–21.

6.5 ConfidenCe intervals for two independent samples, diChotomous outCome In Chapter 6 of the textbook, we presented the following for- mula for the confidence interval for a difference in propor- tions in two independent samples:

ˆ ˆ ˆ ( ˆ ) ˆ ( ˆ )

p p Z p p

n p p

n1 2 1 1

2 2

1 1 − ±

− +

−

(Find Z in Table 1B in the Appendix)

When computing the confidence intervals by hand, we computed the sample sizes and sample proportions, and then we used Table 1B in the Appendix to find the appropriate Z value to reflect the desired confidence level. We now use Excel

figure 6–21 95% Confidence Interval for the Mean Difference

figure 6–22 Prevalence of CVD in Men and Women

figure 6–23 Sample Proportions, Difference in Proportions, and Z Value for 95% Confidence

23944_ch06_5976 76 8/3/11 11:42:32 AM

Practice Problems 77

Compute a 95% confidence interval for the difference in blood pressures over 4 weeks.

Baseline 120 145 130 160 152 143 126 121 115 135 Four weeks 122 142 135 158 155 140 130 120 124 130

3. After the pilot study (described in Problem 2), the main trial is conducted and involves a total of 200 patients, who are enrolled and randomized to receive either the experimental medication or the placebo. The data shown in Table 6–7 are collected at the end of the study after the participants have been on the assigned treatment for 6 weeks.

Generate a 95% confidence interval for the difference in proportions of patients with hypertension between the groups.

In Figure 6–24 we compute the upper and lower limits of the 95% confidence interval using

ˆ ˆ ˆ ( ˆ ) ˆ ( ˆ )

p p Z p p

n p p

n1 2 1 1

2 2

1 1 − ±

− +

− . Notice how the for-

mula is implemented (see the formula for the upper limit in the formula bar in Figure 6–24). Excel can be used to generate confidence intervals for relative risks and odds ratios using a similar approach. The exact formulas for these confidence intervals can be found in Chapter 6 of the textbook.

6.6 praCtiCe problems

1. A study is conducted to estimate the mean total choles- terol level in children aged 2–6 years. A sample of nine participants is selected, and the participants’ total cho- lesterol levels are measured as follows:

185 225 240 196 175 180 194 147 223

Generate a 95% confidence interval for the true mean total cholesterol level in children.

2. A clinical trial is planned to compare an experimental medication designed to lower blood pressure to a pla- cebo. Before starting the trial, a pilot study is conducted involving 10 participants. The objective of the study is to assess how SBP changes over time when left untreated. SBPs are measured at baseline and again 4 weeks later.

figure 6–24 95% Confidence Interval for the Difference in Proportions

table 6–7 Data for Practice Problem 3

Experimental (n = 100)

Placebo (n = 100)

% Hypertensive 14% 22%

23944_ch06_5976 77 8/3/11 11:42:33 AM

78 Confidence Interval Estimates

a. Generate a 95% confidence interval for the proportion of patients on the new medication who report pain relief.

b. Generate a 95% confidence interval for the difference in proportions of patients who report pain relief.

4. The following data were collected as part of a study of coffee consumption among male and female undergrad- uate students. The following data reflect cups per day consumed:

Males 3 4 6 3 2 1 0 2 Females 5 3 1 2 0 4 3 1

Generate a 95% confidence interval for the difference in mean numbers of cups of coffee consumed between men and women.

5. A clinical trial is conducted to compare a new pain re- liever for arthritis to a placebo. Participants are randomly assigned to receive the new medication or a placebo. The outcome is pain relief within 30 minutes. The data are shown in Table 6–8.

table 6–8 Data for Practice Problem 5

Pain Relief No Pain Relief

New medication 44 76 Placebo 21 99

23944_ch06_5976 78 8/3/11 11:42:33 AM

for the population mean (i.e., H1: m > m0) and observe a test statistic Z = 2.04. The p-value is P(Z ≥ 2.04). If we conduct a two-sided test for the population mean (i.e., H1: m ≠ m0) and observe a test statistic Z = 2.04, the p-value is P(Z ≥ 2.04) + P(Z ≤ –2.04) = 2 ¥ P(Z ≥ 2.04). We use Excel to compute the test statistics for each test and to compute p-values for each test to draw conclusions based on the following:

Reject H0 if p ≤ a

P-values for tests involving Z statistics are computed with the NORMSDIST function, and p-values for tests involving t statistics are computed with the TDIST function.

To compute p-values for tests involving a Z statistic, we use the NORMSDIST function as follows:

=NORMSDIST(z)

To use the NORMSDIST function, we specify the value of the test statistic z. The function returns the area under the standard normal curve below z. To use the NORMS- DIST function to compute p-values, we make the following modifications:

One-sided Z test: = 1 – NORMSDIST(ABS(z)) Two-sided Z test: = 2 ¥ (1 – NORMSDIST(ABS(z)))

The ABS function takes the absolute value of the test statistic. By using the ABS function, we can use the one-sided formula for both upper- and lower-tailed tests.

To compute p-values for tests involving t statistics, we use the TDIST function, as follows:

=TDIST(t, df, test type)

In Chapter 7 of the textbook we presented the approach for hypothesis testing for means (m) and proportions (p) in one sample, differences in means (m1 – m2) and differences in proportions (p1 – p2) in two independent samples, the mean difference in two dependent samples (md), and differences in means and proportions in more than two independent samples. For each test we used the same general five-step ap- proach, as follows:

Step 1. Set up hypotheses (H0 and H1) and select a level of significance, a.

Step 2. Choose the appropriate test statistic (e.g., Z, t, F, c2).

Step 3. Determine critical value(s) and set up the deci- sion rule (which depends on a; the test statistic; and whether the test is upper, lower, or two tailed).

Step 4. Compute the test statistic based on observed sam- ple data.

Step 5. Draw a conclusion by comparing the test statistic to the critical value.

The test statistics (Step 2) varied depending on the specific test. When we conducted tests of hypothesis by hand in Chapter 7 of the textbook, we ultimately drew a conclusion by comparing the test statistic to the critical value, which was derived from an appropriate probability distribution. There is an alterna- tive means of drawing a conclusion, and it involves comparing the p-value of a test (defined as the exact significance level) to the selected level of significance, a. The p-value is the prob- ability of observing a test statistic that is as or more extreme than the test statistic observed, and it can be one sided or two sided. For example, suppose we conduct an upper-tailed test

Chapter 7 Hypothesis Testing Procedures

23944_ch07_5976 79 8/3/11 11:43:26 AM

80 Hypothesis Testing Procedures

care and prescription drugs in 2005 were summarized as follows: X = $3,190 and s = $890. We now run the test using Excel.

The hypotheses are:

H0: m = 3,302 H1: m < 3,302 a = 0.05

Because the sample size is large (n ≥ 30), the appropriate test

statistic is Z X

s n =

− µ0 /

We use Excel to compute the test statistic and the p-value. The data are entered into an Excel worksheet, as shown in Figure 7–1.

We now compute the test statistic, Z, and place it in cell B8. The formula is “=(B3-B6)/(B4/sqrt(B2))”. The p-value is computed with the NORMSDIST function. Because this is a lower-tailed test, we use “=1-NORMSDIST(ABS(B8))”. The test statistic and p-value are shown in Figure 7–2.

We do not reject H0 because p = 0.1041 > a = 0.05. We do not have significant evidence to show that there is a reduction in expenditures from the reported value of $3,302 per year. (Recall that when this test was done by hand, we compared the test statistic Z = –1.26 to the critical value from the stan- dard normal distribution and did not reject H0 because –1.26 > –1.645.)

Example 7.2. In Example 7.2 in the textbook we tested whether the mean total cholesterol level in the Framingham Offspring Study was different from the national mean of 203. The following statistics on total cholesterol levels of partici-

To use the TDIST function, we specify the test statistic t, the degrees of freedom (e.g., for a one-sample test of means, df = n – 1), and then the test type. The test type indicates whether the test is one or two tailed (i.e., test type = 1 for upper- or lower-tailed tests, and test type = 2 for two-tailed tests). The function returns the area in the t distribution in one or two tails (depending on the test type). The TDIST function is used as follows to compute p-values:

One-sided t test: = TDIST(ABS(t), df, 1) Two-sided t test: = TDIST(ABS(t), df, 2)

Again, the ABS function is used to take the absolute value of the test statistic.

7.1 TEsTs wiTh OnE samplE, COnTinuOus OuTCOmE For a one-sample test of hypothesis with a continuous out- come, the hypotheses are as follows:

H0: m=m0 H1: m> m0, H1: m < m0, or H1: m ≠ m0

where m is the mean of the population of interest and m0 is a known mean (e.g., a historical control).

In Chapter 7 of the textbook, we presented the following formulas for test statistics:

n Z X

s n ≥ =

− 30 0

/ (Find critical value in Table 1C in

the Appendix)

n t X

s n < =

− 30 0

/ (Find critical value in Table 2 in

the Appendix, df = n – 1)

When we performed the test of hypothesis by hand, we computed the sample size, the mean and standard deviation, and then the test statistic. We used Table 1C or Table 2 in the Appendix to find the appropriate critical value(s) of Z or t and compared the test statistic to the critical value to draw a conclusion. Excel does not have a specific analysis func- tion for a one-sample test of means. However, Excel can be used to compute the test statistic and the p-value to draw a conclusion.

Example 7.1. In Example 7.1 in the textbook we analyzed data on expenditures on health care and prescription drugs. We specifically analyzed whether there was significant evi- dence of a reduction in expenditures from the reported value of $3,302 per year. To test the hypothesis, a sample of 100 Americans were selected, and their expenditures on health

FigurE 7–1 Data for One Sample Test for m

23944_ch07_5976 80 8/3/11 11:43:27 AM

Tests with One Sample, Dichotomous Outcome 81

pants in the Framingham Offspring Study were available: n = 3,310, X = 200.3, and s = 36.8. Here we use Excel to test if there is statistical evidence of a difference in mean cholesterol level in the Framingham Offspring Study as compared to the national mean of 203.

The hypotheses are:

H0: m = 203 H1: m ≠ 203 a= 0.05

Because the sample size is large (n ≥ 30), the appropriate test

statistic is Z X

s n =

− µ0 /

We use Excel to compute the test statistic and the p-value. The data are entered into an Excel worksheet, as shown in Figure 7–3.

We now compute the test statistic, Z, and place it in cell B8. The formula is “=(B3-B6)/(B4/sqrt(B2))”. The two-sided p-value is computed with the NORMSDIST function using “=2*(1-NORMSDIST(ABS(B8)))”. The test statistic and p-value are shown in Figure 7–4. We reject H0 because p = 0.00002 < a = 0.05. (Note that the p-value is given as 2.43E-05, which is equivalent to 2.43 ¥ 10–5 = 0.0000243.) We have significant evidence to show that the mean total cholesterol level in the Framingham Offspring Study is different from the national value of 203. (Recall that when this test was done by hand, we compared the test statistic Z = –4.22 to the critical value from the standard normal distribution, and we rejected H0 because –4.22 < –1.96.)

7.2 TEsTs wiTh OnE samplE, DiChOTOmOus OuTCOmE For a one-sample test of hypothesis with a dichotomous out- come, the hypotheses are as follows:

H0: p = p0 H1: p > p0, H1: p < p0, or H1: p ≠ p0

where p is the proportion of successes in the population of interest and p0 is a known proportion (e.g., a historical con- trol). In Chapter 7 of the textbook, we presented the following test statistic:

Z p p

p p n

= −

−

( ) 0

0 01 . (Find critical value in Table 1C in the

Appendix)

FigurE 7–2 Test Statistic and p-Value FigurE 7–3 Data for One Sample Test for m

FigurE 7–4 Test Statistic and p-Value

23944_ch07_5976 81 8/3/11 11:43:28 AM

82 Hypothesis Testing Procedures

In this test, the test statistic is Z = –10.89, and we reject H0 because p = 0 < a = 0.05. We have statistically significant evi- dence to show that the prevalence of smoking in the Framing- ham Offspring cohort is lower than the prevalence of smoking among American adults, which was reported at 21.1%.

7.3 TEsTs wiTh OnE samplE, CaTEgOriCal Or OrDinal OuTCOmE: ThE Chi- squarE gOODnEss OF FiT TEsT For a c2 goodness of fit test, the hypotheses are as follows:

H0: p1 = p10, p2 = p20, …, pk = pk0, H1: H0 is false

where pi are the sample proportions of successes in each re- sponse category and pi0 are the known proportions in each response category.

In Chapter 7 of the textbook, we presented the following formula for the test statistic:

χ2 2

= −∑ ( )O EE (Find critical value in Table 3 in the Appendix, df = k – 1)

where O = observed frequency, E = expected frequency (i.e., sample data) in each of the response categories, and k = the number of response options.

When we performed the goodness of fit test by hand, we computed the expected frequencies for each category and then computed the test statistic. We used Table 3 in the Appendix to find the appropriate critical value from the c2 distribution and compared the test statistic to the critical value to draw a conclusion.

When we performed this test of hypothesis by hand, we computed the sample size, sample proportion, and then the test statistic. We used Table 1C in the Appendix to find the appropriate critical value of Z and compared the test statistic to the critical value to draw a conclusion. We now use Excel to conduct the test of hypothesis.

Example 7.3. In Example 7.4 in the textbook, we tested whether the prevalence of smoking in the Framingham Off- spring Study was lower than the prevalence of cigarette smok- ing among American adults, which was reported as 21.1%. In the Framingham Offspring Study, 482 of 3,536 of the respon- dents (13.6%) were smoking at the time of the exam.

The hypotheses are:

H0: p = 0.211 H1: p < 0.211 a = 0.05

The appropriate test statistic is Z p p

p p n

= −

−

( ) 0

0 01 .

We use Excel to compute the test statistic and the p-value. The data are entered into an Excel worksheet, as shown in Figure 7–5. The sample proportion is shown in cell B4 and is computed by dividing the number of smokers in the sample by the sample size (i.e., B3/B2).

We now compute the test statistic, Z, and place it in cell B8. The formula is “=(B4-B6)/(sqrt(B6*(1-B6)/B2))”. The one- sided p-value is computed with the NORMSDIST function using “=1-NORMSDIST(ABS(B8))”. The test statistic and p- value are shown in Figure 7–6.

FigurE 7–5 Data for One Sample Test for p

FigurE 7–6 Test Statistic and p-Value

23944_ch07_5976 82 8/3/11 11:43:29 AM

Tests with One Sample, Categorical or Ordinal Outcome: The Chi-Square Goodness of Fit Test 83

and conducted another survey that was completed by 470 graduates. The data are shown in Table 7–1. Based on the data, is there evidence of a shift in the distribution of responses to the exercise question following the implementation of the health promotion campaign on campus?

The hypotheses are:

H0: p1 = 0.60, p2 = 0.25, p3 = 0.15 or equivalently H0:

Distribution of responses is 0.60, 0.25, 0.15 H1: H0 is false

a = 0.05

The appropriate test statistic is χ2 2

= −∑ ( )O EE . Recall that the expected frequencies (E) are computed

based on the assumption that H0 is true. The data for the test are entered into an Excel worksheet, as shown in Figure 7–7. The sample data (i.e., the number of students in each response category) are the observed frequencies. The total sample size is computed using the SUM function and is shown in cell B6.

We compute the expected frequencies by multiplying the hypothesized or expected proportions in each response category (from H0) by the total sample size. The expected pro- portions are first entered into the Excel worksheet in column C in Figure 7–8. We then multiply the expected proportions in column C by the sample size to produce the expected fre- quencies. For example, the expected frequency in cell D2 is computed using “=C2*$B$6”. (Notice that we use the absolute cell address for the total sample size so the same value is used to compute each expected frequency.) The expected propor- tions and expected frequencies are shown in Figure 7–8.

We now compute (O – E)2 / E in each response category and sum to produce the c2 statistic. The c2 test statistic is

Excel does not have a specific analysis tool to perform the c2 goodness of fit test; however, it does have a CHIDIST function that can be used to produce p-values. The CHIDIST function is used as follows:

=CHIDIST(c2, df)

To use the CHIDIST function, we specify the test sta- tistic, c2, and the degrees of freedom. For the c2 goodness of fit test, df = k – 1, where k represents the number of response categories. The CHIDIST function returns the area in the right hand tail of the distribution, which is the p-value for the c2 goodness of fit test. We now use Excel to conduct a goodness of fit test.

Example 7.4. In Example 7.6 of the textbook, we analyzed a university’s survey of its graduates in which demographic and health information were collected for future planning purposes. In response to a question about regular exercise as an undergraduate, 60% of all graduates reported getting no regular exercise, 25% reported exercising sporadically, and 15% reported exercising regularly. The next year the univer- sity launched a health promotion campaign on campus in an attempt to increase health behaviors among undergraduates

TablE 7–1 Data from University Survey

No Regular Exercise

Sporadic Exercise

Regular Exercise Total

Number of students

255 125 90 470

FigurE 7–7 Data for c2 Goodness of Fit Test

FigurE 7–8 Expected Frequencies for c2 Goodness of Fit Test

23944_ch07_5976 83 8/3/11 11:43:29 AM

84 Hypothesis Testing Procedures

where Sp n s n s

n n =

− + − + −

( ) ( )1 12 2 22

1 2

1 1 2

When performing tests of hypothesis by hand, we com- puted the sample sizes, means, and standard deviations in each sample. We then computed the pooled estimate of the common standard deviation, Sp, and the test statistic. We then used Table 1C or Table 2 in the Appendix to find the appropri- ate critical value(s) of Z or t and compared the test statistic to the critical value to draw a conclusion. We now use Excel to conduct the test of hypothesis.

Example 7.5. A clinical trial is run to compare an experi- mental drug to a placebo for its effectiveness in lowering systolic blood pressure (SBP). A total of 18 participants are enrolled in the study and are randomly assigned to receive either the experimental drug or placebo. After 6 weeks on the assigned treatment, each patient’s SBP is measured, and the data are as follows:

Experimental drug

125 130 135 121 140 137 129 145 115

Placebo 145 140 132 129 145 150 160 140 120

Is there statistical evidence of a difference in mean SBPs between treatments? We now run the test using Excel.

The hypotheses are:

H0: m1 = m2 H1: m1 ≠ m2 a = 0.05

Because the sample sizes are small (n1 < 30 and n2 < 30), the appropriate test statistic is as follows:

shown in cell E6 in Figure 7–9. The p-value is computed with the CHIDIST function using “=CHIDIST(E6,2)”, where 2 re- flects the degrees of freedom (df = k – 1 = 3 – 1 = 2). The test statistic and p-value are shown in Figure 7–9. In this test, the test statistic is c2 = 8.46, and we reject H0 because p = 0.0146 < a = 0.05. We have statistically significant evidence to show that the distribution of responses is not 0.60, 0.25, 0.15.

7.4 TEsTs wiTh TwO inDEpEnDEnT samplEs, COnTinuOus OuTCOmE For a two independent samples test of hypothesis with a con- tinuous outcome, the hypotheses are as follows:

H0: m1 = m2 H1: m1 > m2, H1: m1 < m2, or H1: m1 ≠ m2

where m1 and m2 are the means of the two independent popu- lations of interest. In Chapter 7 of the textbook, we presented the following formulas for test statistics:

n n Z X X

Sp n n

1 2 1 2

1 2

30 30 1 1

≥ ≥ = −

+ and (Find critical

value of Z in Table 1C in the Appendix)

n n t X X

Sp n n

1 2 1 2

1 2

30 30 1 1

< < = −

+ or (Find critical

value of t in Table 2 in the Appendix, df = n1 + n2 – 2)

FigurE 7–9 Test Statistic and p-Value

23944_ch07_5976 84 8/3/11 11:43:30 AM

Tests with Two Independent Samples, Continuous Outcome 85

Next, we compute Sp, the pooled estimate of the common standard deviation, using the following formula:

Sp n s n s

n n =

− + − + −

( ) ( )1 12 2 22

1 2

1 1 2

The results are shown in Figure 7–12. (Notice the formula in the top menu bar, which computes Sp using the summary statistics.)

The next step is to compute the t statistic:

t X X

Sp n n

= −

1 2

1 1

The results are shown in Figure 7–13. The final step is to compute the two-sided p-value using the TDIST function, as shown in Figure 7–14.

The mean SBP for patients on the experimental drug is 130.8, as compared to 140.1 for patients on placebo. The esti- mate of the pooled standard deviation is Sp = 10.78, and the test statistic is t = –1.84. The two-sided p-value is 0.085, and thus we do not have significant evidence to show that there is a significant difference in mean blood pressures between treatments because p = 0.085 > a = 0.05.

It is tedious to set up the formulas to compute the sum- mary statistics, Sp, the test statistic, and the p-value the first time, but after the formulas are programmed into Excel, this same worksheet can be reused to conduct tests of equality of two independent means in other datasets. The new data

t X X

Sp n n

= −

1 2

1 1

To perform the test, we first enter the data into an Excel worksheet, as shown in Figure 7–10. Next we compute sum- mary statistics, specifically the sample size, mean, and stan- dard deviation for each group using the COUNT, AVERAGE, and STDEV functions, respectively. The summary statistics for each comparison group are shown in Figure 7–11.

FigurE 7–10 Data for Two Independent Samples Test of (m1 – m2)

FigurE 7–11 Summary Statistics for Each Comparison Group

FigurE 7–12 Computing Sp

23944_ch07_5976 85 8/3/11 11:43:31 AM

86 Hypothesis Testing Procedures

H0: md = 0 H1: md > 0, H1: md < 0, or H1: md ≠ 0

where md is the mean difference of the two dependent, matched, or paired populations.

In Chapter 7 of the textbook, we presented the following formulas for test statistics:

n Z X

s n d d

≥ = −

30 µ

/ (Find critical value of Z in Table

1C in the Appendix)

n t X

s n d d

< = −

30 µ

/ (Find critical value of t in Table 2

in the Appendix, df = n – 1)

To perform the test of hypothesis by hand, we first com- puted difference scores and then the sample size, mean, and standard deviation of the difference scores in the sample. We then computed the test statistic and the appropriate critical value(s) of Z or t from Table 1C or Table 2 in the Appendix and compared the test statistic to the critical value to draw a conclusion. We now use Excel to compute difference scores, summary statistics on the difference scores, the test statistic, and a p-value to perform the test of hypothesis.

Example 7.6. In Example 7.11 of the textbook, we evaluated the efficacy of a new drug for lowering cholesterol. Fifteen patients had their pretreatment or Baseline total cholesterol level measured. After taking the drug for 6 weeks, each pa- tient’s total cholesterol level was measured again. The data are shown in Table 7–2.

The hypotheses are as follows:

H0: md = 0 H1: md > 0 a = 0.05

Because the sample size is small, the appropriate test sta-

tistic is t X

s n d d

= − µ

/ . We first enter the data into an Excel

worksheet, as shown in Figure 7–15. Next we compute dif- ference scores in column D by subtracting the 6 Weeks cho- lesterol level (column C) from the Baseline cholesterol level (column B). Specifically, in cell D2 we enter “=B2-C2”. We then copy the formula in cell D2 into cell D3 through cell D16. The difference scores are shown in Figure 7–16.

Next we compute summary statistics on the difference scores. Specifically, we use the COUNT, AVERAGE, and STDEV functions to compute the sample size, mean, and standard deviation on the difference scores, respectively. The summary statistics are shown in Figure 7–17. The next step

are entered into column A and column B. Depending on the numbers of observations in the dataset, only the data ranges for the summary statistics (e.g., COUNT(A2:A10), AVERAGE(B2:B10)) need to be changed to perform the test. The computation of Sp, the test statistic, and the p-value will update automatically based on the new summary statistics.

7.5 TEsTs wiTh maTChED samplEs, COnTinuOus OuTCOmE For a two dependent samples test of hypothesis with a con- tinuous outcome, the hypotheses are as follows:

FigurE 7–13 Computing the t Statistic

FigurE 7–14 Computing the p-Value

23944_ch07_5976 86 8/3/11 11:43:32 AM

Tests with Matched Samples, Continuous Outcome 87

TablE 7–2 Data from Cholesterol Study

Subject Identification Number Baseline 6 Weeks

1 215 205 2 190 156 3 230 190 4 220 180 5 214 201 6 240 227 7 210 197 8 193 173 9 210 204 10 230 217 11 180 142 12 260 262 13 210 207 14 190 184 15 200 193

FigurE 7–15 Data for Two Dependent Samples Test of md

FigurE 7–16 Difference Scores

FigurE 7–17 Summary Statistics on Difference Scores

23944_ch07_5976 87 8/3/11 11:43:33 AM

88 Hypothesis Testing Procedures

computed in column D. Depending on the numbers of observations in the dataset, only the data ranges for the summary statistics on the difference scores (e.g., COUNT(D2:D16), AVERAGE(D2:D16)) need to be changed to perform the test. The computation of the test statistic and the p-value update automatically based on the new summary statistics.

7.6 TEsTs wiTh TwO inDEpEnDEnT samplEs, DiChOTOmOus OuTCOmE For a two independent sample tests of hypoth- esis with a dichotomous outcome, the hypoth- eses are as follows:

H0: p1 = p2 H1: p1 > p2, H1: p1 < p2, or H1: p1 ≠ p2

where p1 and p2 are the proportions of suc- cesses in the two populations of interest. In Chapter 7 of the textbook, we presented the following test statistic:

Z p p

p p n n

= −

− +  

 

ˆ ˆ

ˆ( ˆ)

1 2

1 1 1 (Find critical

value of Z in Table 1C in

the Appendix) is to compute the t statistic: t

s n d d

= − µ

/ . The results are

shown in Figure 7–18. The final step is to compute the one-

sided p-value using the TDIST function, as shown in Figure 7–19.

The mean difference in cholesterol levels from Baseline to 6 Weeks is 16.9 units with a standard deviation of 14.2 units. The test sta- tistic is t = 4.63, and the one-sided p-value is 0.0002. We reject H0 because p = 0.0002 < a = 0.05. We have statistically significant evidence at a = 0.05 to show that there is a reduction in cholesterol levels over 6 weeks.

Again, although it is tedious to initially set up the formulas to compute the difference scores, and then compute the summary sta- tistics on the difference scores, the test sta- tistic, and the p-value, after the formulas are programmed into Excel, this same worksheet can be reused to conduct tests of hypothesis in matched samples with other datasets. The new data are entered into column B and column C (assuming that an identification number is again included in the dataset and entered into column A), and the difference scores are

FigurE 7–18 Computing the t Statistic

FigurE 7–19 Computing the p-Value

23944_ch07_5976 88 8/3/11 11:43:34 AM

Tests with Two Independent Samples, Dichotomous Outcome 89

We use Excel to compute the test statistic and the p-value. The data are entered into an Excel worksheet, as shown in Figure 7–20. The sample proportions are computed by divid- ing the numbers of successes (column C) by the sample sizes (column B) in each group.

Before computing the test statistic, Z, we need to compute the overall proportion. This is placed in cell D5 and is com- puted as follows “=(C2+C3)/(B2+B3)”. We next compute the test statistic, Z, and place it in cell D7. The formula is “(D2- D3)/sqrt(D5*(1-D5)*(1/B2+1/B3))”. The overall proportion and Z statistic are shown in Figure 7–21. The last step involves computing the two-sided p-value using the NORMSDIST function, as follows: “=2*(1-NORMSDIST(ABS(D7)))”. The p-value is shown in Figure 7–22.

In this test, the test statistic is Z = 2.53, and we reject H0 because p = 0.011 < a = 0.05. We have statistically significant evidence at a = 0.05 to show that there is a difference in the

where p̂1 is the proportion of successes in sample 1, p̂2 is the proportion of successes in sample 2, and p̂ is the proportion

of successes in the pooled sample, p̂ x x n n

= + +

1 2

. We use Excel

to compute the Z statistic and the p-value.

Example 7.7. In Example 7.13 of the textbook we analyzed data from a randomized trial designed to evaluate the effec- tiveness of a newly developed pain reliever as compared to a standard pain reliever in reducing pain in patients following joint replacement surgery. A total of 100 patients undergoing joint replacement surgery agreed to participate in the trial. They were randomly assigned to receive either the new pain reliever or the standard pain reliever following surgery and were blind to the treatment assignment. Before receiving the assigned treatment, the patients were asked to rate their pain on a scale of 0–10, with higher scores indicative of more pain. Each patient was then given the assigned treatment, and after 30 minutes the patient was again asked to rate their pain on the same scale. The primary outcome was a reduction in pain of three or more scale points (defined by clinicians as a clini- cally meaningful reduction). Table 7–3 displays the data observed in the trial.

We use Excel to test whether there is a statistically significant difference in the proportions of patients reporting a meaningful reduction (i.e., a reduction of three or more points). The hypotheses are as follows:

H0: p1 = p2 H1: p1 ≠ p2 a = 0.05

The appropriate test statistic is

Z p p

p p n n

= −

− +  

 

ˆ ˆ

ˆ( ˆ)

1 2

1 1 1 .

TablE 7–3 Data from Clinical Trial

Treatment Group n

Number with Reduction of

3+ Points

Proportion with Reduction

of 3+ Points

New pain reliever 50 23 0.46 Standard pain reliever

50 11 0.22

FigurE 7–20 Data for Two Sample Test for p1 = p2

FigurE 7–21 Computing the Test Statistic

23944_ch07_5976 89 8/3/11 11:43:35 AM

90 Hypothesis Testing Procedures

weeks), measured in pounds. A total of 20 patients agreed to participate in the study and were randomly assigned to one of the four diet groups. The data are shown in Table 7–4.

TablE 7–4 Data from Clinical Trial

Low Calorie Low Fat Low

Carbohydrate Control

8 2 3 2 9 4 5 2 6 3 4 −1 7 5 2 0 3 1 3 3

We use Excel to conduct an ANOVA to test whether there is a statistically significant difference in the mean weight loss among the four diets. We first enter the data into an Excel worksheet, as shown in Figure 7–23. Next we compute sum- mary statistics for each comparison group. Specifically, we compute the sample size, mean, and standard deviation using the COUNT, AVERAGE, and STDEV functions, respectively. The summary statistics are shown in Figure 7–24.

Next we compute the between treatments sums of squares, Σn X Xj j( )− 2 . We just computed the group means (as shown in Figure 7–24). We need to compute the overall mean ( X ) and then the product of the sample size and the squared dif- ferences between each group mean and the overall mean for each comparison group. The overall mean is computed using the AVERAGE function and is placed in cell G6. In cell G6, we enter “=AVERAGE(A2:D6)” (see Figure 7–25). Notice that

proportions of patients on the new pain reliever reporting a meaningful reduction (i.e., a reduction of three or more scale points), as compared to patients on the standard pain reliever.

7.7 TEsTs wiTh mOrE Than TwO inDEpEnDEnT samplEs, COnTinuOus OuTCOmE: analysis OF VarianCE In analysis of variance (ANOVA), the hypotheses are as follows:

H0: m1 = m2 = … = mk H1: Means are not all equal

where mj is the mean in the jth group and k = the number of independent comparison groups. In Chapter 7 of the text- book, we presented the test statistic for analysis of variance as follows:

F n X X k

X X N k j j

= − −

− − Σ ΣΣ

( ) / ( ) ( ) / ( )

1 (Find critical value in

Table 4 in the Appendix, df1 = k – 1, df2 = N – k)

where nj = the sample size in the jth group, Xj is the sample mean in the jth group, and X is the overall mean. The num- ber of independent groups is represented by k (k > 2), and N represents the total number of observations in the analysis.

Example 7.8. In Example 7.14 of the textbook, we ana- lyzed data from a clinical trial comparing four weight loss programs. The outcome of interest was weight loss, defined as the difference in weight measured at the start of the study (baseline) and weight measured at the end of the study (8

FigurE 7–22 Computing the p-Value

FigurE 7–23 Data for ANOVA

23944_ch07_5976 90 8/3/11 11:43:37 AM

Tests with More Than Two Independent Samples, Continuous Outcome: Analysis of Variance 91

To produce the ANOVA table, which contains the F sta- tistic to conduct the test of hypothesis, we need to compute either the error sums of squares ( SSE = −ΣΣ( )X Xj 2 ) or the total sums of squares ( SST = −ΣΣ( )X X 2 ). We do not need to compute both because SST = SSB + SSE. Using Excel, we can easily compute SST using the VAR (variance) function. Notice that SST is the sum of the differences between each observation and the overall mean for all groups combined.

The sample variance is defined as s X X n

2 2

1 = −

− Σ( ) , where

n refers to the total sample size. Thus, we can compute the

FigurE 7–24 Summary Statistics for Each Comparison Group

FigurE 7–25 Overall Mean and Product of Sample Size and Squared Difference Between Group Means and Overall Mean

we specify the data range to include the observations in all groups combined. Next we compute the product of the sample size and the squared difference between each group mean and the overall mean for group 1 in cell G7. Specifically, in cell G7 we enter “=G2*(G3-$G$6)^2”. Notice that we need to use absolute cell referencing to ensure that the overall group mean (located in cell G6) is used for each comparison group. We then copy the formula from cell G7 into cell H7 through cell J7, as shown in Figure 7–25. Next, we compute SSB by summing the values in cell G7 through cell J7 using the SUM function. The result is shown in Figure 7–26.

23944_ch07_5976 91 8/3/11 11:43:38 AM

92 Hypothesis Testing Procedures

FigurE 7–27 Computing the Total Sums of Squares (SST)

FigurE 7–26 Computing the Between Treatments Sums of Squares (SSB)

variance of the pooled sample (all groups combined), and then SST = (n – 1) ¥ s2, where s2 is the variance of the pooled sample (i.e., the data range is cell A2 through cell D6). The computation of SST is shown in Figure 7–27. Specifically, in cell G8 we enter “=(SUM(G2:J2)-1)*VAR(A2:D6)”.

We now have all of the elements to construct an ANOVA table in Excel. First, we enter the template of the ANOVA table into Excel, as shown in Figure 7–28. In the template, we designate places for the sums of squares (SS), degrees of freedom (df), mean squares (MS), the F statistic, and a cell for the p-value of the test. We now populate the ANOVA table with the statistics we have computed thus far. First we fill in

the between treatments sums of squares (SSB) in the ANOVA table by entering the following in cell G12: “=G8”. We then fill in the total sums of squares in the ANOVA table by entering the following in cell G14: “=G9”. Finally, we compute the error sums of squares in the ANOVA table by entering the following in cell G13: “=G14-G12”. The ANOVA table with the sums of squares is shown in Figure 7–29.

Next we add degrees of freedom to the ANOVA table. Recall the between treatments degrees of freedom are k – 1, the error degrees of freedom are N – k, and the total degrees of freedom are N – 1, where k is the number of comparison groups and N is the total number of observations or total

23944_ch07_5976 92 8/3/11 11:43:38 AM

Tests with More Than Two Independent Samples, Continuous Outcome: Analysis of Variance 93

FigurE 7–28 Template for ANOVA Table

FigurE 7–29 ANOVA Table with Sums of Squares

sample size. It is easy to compute the total sample size by using the COUNT function based on the data range for all groups combined (i.e., COUNT(A2:D6)) or using the SUM function to sum the sample sizes in each comparison group (i.e., SUM(G2:J2)). We also enter the number of groups (in this example, k = 4) into cell J6, as shown in Figure 7–30.

We now populate the cells in the ANOVA table for degrees of freedom. In cell H12 we enter “=J6-1”. In cell H13 we enter “=sum(G2:J2)-J6”, and in cell H14 we enter “=H12+H13”. The ANOVA table with the degrees of freedom is shown in Figure 7–31. Next we compute mean squares (MS = Sums of Squares (SS) / degrees of freedom (df)) for the Between Treatments and

23944_ch07_5976 93 8/3/11 11:43:39 AM

94 Hypothesis Testing Procedures

FigurE 7–30 Entering the Number of Comparison Groups, k

FigurE 7–31 ANOVA Table with Degrees of Freedom

Error rows of the ANOVA table. In cell I12 we enter “=G12/ H12”, and in cell I13 we enter “=G13/H13”. The ANOVA table with mean squares is shown in Figure 7–32.

The final step is to compute the F statistic by taking the ratio of the mean square between treatments to the mean square error. We enter the formula to compute the F statistic into cell J12 as follows: “=I12/I13”. We then compute the p-

value using the Excel function FDIST. The FDIST function is used as follows:

=FDIST(F, between treatments df, error df)

To use the FDIST function, we specify the test statistic F, the between treatments and error degrees of freedom. To complete the ANOVA table, we enter the following in cell K12:

23944_ch07_5976 94 8/3/11 11:43:40 AM

Tests with More Than Two Independent Samples, Continuous Outcome: Analysis of Variance 95

FigurE 7–32 ANOVA Table with Mean Squares

FigurE 7–33 Analysis of Variance Table

“=FDIST(J12,H12,H13)”. The final ANOVA table is shown in Figure 7–33. The p-value is 0.0013, and thus we reject H0. We have statistically significant evidence at a = 0.05 to show that there is a difference in mean weight loss among the four diets.

It is very tedious to enter the formulas to conduct an ANOVA in Excel. Once again, the worksheet we constructed

to test the equality of the four treatment means in Example 7.8 can be modified to conduct ANOVA tests in other datasets. The data ranges for the summary statistics must be modified, and the number of groups (k) must be entered into cell J6 in the worksheet. After the summary statistics and number of comparison groups are updated, the ANOVA table is popu- lated using the formulas and functions that we built.

23944_ch07_5976 95 8/3/11 11:43:40 AM

96 Hypothesis Testing Procedures

grand total in cell E6 is computed by summing the column totals using “=SUM(B6:D6)”. (This is equivalent to summing the row totals using “=SUM(E2:E5)”.)

We next compute the expected frequencies using the following:

Expected Frequency = (Row Total ¥ Column Total) / N

The expected frequencies are placed in another table, as shown in Figure 7–35. To compute the expected frequen- cies, we need to use both relative and absolute cell addresses. For example, the expected frequency in cell H2 is computed using “=$E2*B$6/$E$6”. The row total is referenced as “$E2”. We place “$” in front of the column to freeze the column ad- dress on row totals that are contained in column E. We do the same for the column total, B$6, except we freeze the row

7.8 TEsTs wiTh TwO Or mOrE inDEpEnDEnT samplEs, CaTEgOriCal Or OrDinal OuTCOmE: ThE Chi-squarE TEsT OF inDEpEnDEnCE For a test of independence, the hypotheses are as follows:

H0: The distribution of the outcome is independent of the groups

H1: H0 is false.

In Chapter 7 of the textbook, we presented the following formula for the test statistic:

χ2 2

= −∑ ( )O EE (Find critical value in Table 3 in the Appendix, df = (r – 1)(c – 1))

where O = observed frequency (i.e., sample data), E = expected frequency in each of the cells of the table, r = the number of rows in the two-way table, and c = the number of columns in the two-way table (where r and c correspond to the number of comparison groups and the number of response options in the outcome).

When we performed the c2 test of independence by hand, we computed the expected frequencies for each category and then computed the test statistic. We then used Table 3 in the Appendix to find the appropriate critical value from the c2 distribution and compared the test statistic to the critical value to draw a conclusion. We now use Excel to conduct a test of independence.

Example 7.9. In Example 7.4 of this workbook we exam- ined data from a survey of university graduates that assessed, among other things, how frequently students exercised. Here we want to test whether there is a relationship between exer- cise and students’ living arrangements. The data are shown in Table 7–5.

The hypotheses are as follows:

H0: Living arrangements and exercise are independent H1: H0 is false

a = 0.05

The appropriate test statistic is χ2 2

= −∑ ( )O EE . Recall that the expected frequencies (E) are computed

based on the assumption that H0 is true. The data for the test are entered into an Excel worksheet, as shown in Figure 7–34. The sample data (i.e., the number of students in each cell of the table) are the observed frequencies in the table. The row and col- umn totals are computed using the SUM function. For exam- ple, the column total in B6 is computed using “=SUM(B2:B5)”. The row total in E2 is computed using “=SUM(B2:D2)”. The

TablE 7–5 Data from University Survey

No Regular Exercise

Sporadic Exercise

Regular Exercise Total

Dormitory 32 30 28 90 On-campus

apartment 74 64 42 180

Off-campus apartment

110 25 15 150

At home 39 6 5 50 Total 255 125 90 470

FigurE 7–34 Data for c2 Test of Independence

23944_ch07_5976 96 8/3/11 11:43:41 AM

Tests with Two or More Independent Samples, Categorical or Ordinal Outcome: The Chi-Square Test of Independence 97

FigurE 7–35 Expected Frequencies for c2 Test of Independence

FigurE 7–36 (O – E)2/E in Each Cell

address on row 6, which contains the column totals. The total sample size is in cell $E$6. When we copy the formula to cell H3, for example, the formula is updated to “=$E3*B$6/$E$6”. The sums of the expected frequencies across rows and down columns are equal to the sums of the observed frequencies across rows and down columns.

We now compute (O – E)2 / E in each cell of the table. After we compute these, we sum to produce the c2 statistic.

The (O – E)2/ E values for each cell are shown in Figure 7–36. In cell F9, for example, the formula is “=(B2-H2)^2/H2”. When we copy this formula to the other cells in the bottom table, the cell references are automatically updated (e.g., the formula in cell H12 is “=(D5-J5)^2/J5”).

The c2 test statistic is computed by summing the (O – E)2 / E values in the nine cells. The test statistic is placed in cell G14 and is computed using “=SUM(F9:H12)”. We also compute a

23944_ch07_5976 97 8/3/11 11:43:41 AM

98 Hypothesis Testing Procedures

the new compound or placebo, the extent of wound heal- ing was measured. Is there a difference in the extent of wound healing between the treatments? (Hint: Are treat- ment and the percent wound healing independent?) Run the appropriate test at a 5% level of significance.

2. Use the data in Problem 1. Pool the data across the treat- ments into one sample of size n = 250. Use the pooled data to test whether the distribution of the percent wound healing is approximately normal. Specifically, use the following distribution 30%, 40%, 20%, and 10% and a = 0.05 to run the appropriate test.

p-value using the CHIDIST function. We also used this func- tion for the c2 goodness of fit test as follows:

=CHIDIST(c2, df)

Again, to use the CHIDIST function, we specify the test statistic, c2, and the degrees of freedom. For the c2 test of inde- pendence, df = (r – 1) ¥ (c – 1). The CHIDIST function returns the area in the right hand tail of the distribution, which is the p-value for the c2 test of independence. The p-value is com- puted with the CHIDIST function using “=CHIDIST(G14,6)”, where 6 reflects the degrees of freedom (df = (r – 1) ¥ (c – 1) = 3(2) = 6). The test statistic and the p-value are shown in Figure 7–37.

In this test, the test statistic is c2 = 60.44, and the p-value is practically zero (3.66 ¥ 10–11). We reject H0 because p = 0 < a = 0.05. We have statistically significant evidence at a = 0.05 to show that H0 is false or that living arrangements and exercise are not independent (i.e., they are dependent or related).

7.9 praCTiCE prOblEms

1. The data in Table 7–6 were collected in a clinical trial to evaluate a new compound designed to improve wound healing in trauma patients. The new compound was com- pared against a placebo. After treatment for 5 days with

FigurE 7–37 Test Statistic and p-Value

TablE 7–6 Data for Practice Problems 1 and 2

Percent Wound Healing

Treatment 0–25% 26–50% 51–75% 76–100%

New compound (n = 125)

15 37 32 41

Placebo (n = 125) 36 45 34 10

23944_ch07_5976 98 8/3/11 11:43:42 AM

Practice Problems 99

are shown in Table 7–8. Is there a significant difference in the proportions of patients reporting pain relief? Run the test at a 5% level of significance.

5. A clinical trial is planned to compare an experimental medication designed to lower blood pressure to a pla- cebo. Before starting the trial, a pilot study is conducted involving seven participants. The objective of the study is to assess how systolic blood pressure (SBP) changes over time if left untreated. SBP is measured at baseline and again 4 weeks later. Is there a statistically significant difference in SBP over time? Run the test at a 5% level of significance.

Baseline 120 145 130 160 152 143 126 Four weeks 122 142 135 158 155 140 130

6. A hypertension trial is mounted, and 12 participants are randomly assigned to receive either a new medication or a placebo. Each participant takes the assigned medication, and the participants’ SBP is recorded after 6 months on the assigned medication. The data are shown in Table 7–9. Is there a difference in mean SBP between treatments? Run the appropriate test at a = 0.05.

3. The data in Table 7–7 were collected in an experiment designed to investigate the impact of different positions of pregnant women on fetal heart rate. Fetal heart rate is measured by ultrasound in beats per minute. The study included 20 pregnant women who were assigned to one position and had the fetal heart rate measured in that position. Each woman was between 28 weeks and 32 weeks gestation. Is there a significant difference in mean fetal heart rates by position? Run the test at a 5% level of significance.

4. A clinical trial is conducted to compare a new pain re- liever for arthritis to a placebo. Participants are randomly assigned to receive the new medication or a placebo, and the outcome is pain relief within 30 minutes. The data

TablE 7–7 Data for Practice Problem 3

Back Side Sitting Standing

140 141 144 147 144 143 145 145 146 145 147 148 141 144 148 149 139 136 144 145

TablE 7–8 Data for Practice Problem 4

Pain Relief No Pain Relief

New medication 44 76

Placebo 21 99

TablE 7–9 Data for Practice Problem 6

Placebo New Medication

134 114 143 117 148 121 142 124 150 122 160 128

23944_ch07_5976 99 8/3/11 11:43:42 AM

23944_ch07_5976 100 8/3/11 11:43:42 AM

Excel, we use the NORMSINV function to compute Z values for confidence intervals, as follows:

=NORMSINV(lower tail area)

To use this function for computing sample sizes, we spec- ify the area under the curve in the lower tail of the standard normal distribution. For example, for a 95% confidence in- terval, the area in the lower tail is 0.975. Figure 8–1 shows the standard normal distribution, Z, and the Z values that hold the middle 95% of the distribution (P(–1.96 < X < 1.96) = 0.95).

To use the NORMSINV function for computing sample sizes, we specify the probability in the lower tail of the stan- dard normal distribution for the desired confidence level. For

In Chapter 8 in the textbook, we presented various formulas to determine the sample size for statistical inference. In ap- plications where the goal is to generate a confidence interval estimate for an unknown parameter, the sample size is com- puted to ensure that the margin of error is sufficiently small. In applications where the goal is to perform a test of hypoth- esis, the sample size is computed to ensure that the test has a high probability of rejecting the null hypothesis when it is false (in other words, to ensure that the test has high power). Excel Mac 2008 has a number of probability functions that can be used to implement the sample size formulas presented in Chapter 8 in the textbook.

8.1 Sample Size eStimateS for ConfidenCe intervalS with a ContinuouS outCome in one Sample In Chapter 8 in the textbook, we presented the following for- mula to estimate the sample size required to estimate the mean of a continuous outcome variable in a single population:

n Z E

=  

 

s 2

where Z is the value from the standard normal distribution reflecting the confidence level that will be used (e.g., Z = 1.96 for 95%), s is the standard deviation of the outcome variable, and E is the desired margin of error. The previous formula generates the minimum number of subjects required to ensure that the margin of error in the confidence interval for m does not exceed E. To determine sample size requirements with

Chapter 8 Power and Sample Size

Determination

figure 8–1 95% Confidence Limits of the Standard Normal Distribution

–3 –2 –1 0 1 2

0.025 0.025

0.95

23944_ch08_5976 101 8/3/11 11:45:10 AM

102 Power and Sample Size Determination

must round up. This is done using Excel’s ROUNDUP func- tion. The ROUNDUP function is used as follows:

= ROUNDUP(number to round, number of decimal places)

For sample size computations, we round the value pro- duced by the formula to the nearest integer (i.e., 0 decimal places). The sample size required for the study is shown in Figure 8–4. In order to ensure that the 95% confidence interval estimate of the mean SBP in children with congenital heart disease who are between the ages of 3 and 5 years is within five units of the true mean, a sample of size 62 is needed.

When the Excel formulas are programmed, other sce- narios can be considered. For example, suppose we wish to consider other margins of error (e.g., E = 5, 4, 3, 2) and other standard deviations (e.g., 20 and 15). The sample sizes for these other scenarios are determined by copying the formulas from cell D2 through cell F2 to cell D3 through cell F9. The sample sizes are shown in Figure 8–5.

If the standard deviation is 20, then in order to ensure that a 95% confidence interval estimate of the mean SBP in children between the ages of 3 and 5 years with congenital

example, if a 95% confidence interval is planned, we specify “=NORMSINV(0.975)”, which returns 1.96. If a 90% confi- dence interval is planned, we specify “=NORMSINV(0.95)”, which returns 1.645.

example 8.1. In Example 8.1 of the textbook, we deter- mined the sample size required to estimate the mean systolic blood pressure (SBP) in children with congenital heart dis- ease who are between the ages of 3 and 5 years. The analysis was planned to estimate a 95% confidence interval, and the investigator decided that a margin of error of 5 units was suf- ficiently precise. To determine the sample size, the standard deviation was assumed to be 20.

The margin of error, standard deviation, and confidence level are input into Excel, as shown in Figure 8–2. The Z value is estimated using the NORMSINV function, as shown in Figure 8–2. Recall that the argument for the NORMSINV function is the area in the lower tail of the standard normal curve (Figure 8–1). If a 95% confidence interval is planned, the area in the lower tail is computed by first determining the total tail area as (1 – 0.95) and then dividing by 2 to determine the area in the upper tail. The lower tail area is computed by subtracting the upper tail area (i.e., (1 – 0.95) / 2) from 1. The argument for the NORMSINV function is “(1-(1-C2)/2)”. This returns the value 1.96, which is the Z value for a 95% confi- dence interval. The sample size is computed using the previous formula. The result is in cell E2 and is implemented by using “=(D2*B2/A2)^2”, as shown in Figure 8–3.

Recall that the sample size formula always produces the minimum number of subjects required to ensure that the confidence interval has a margin of error not exceeding E. To determine the number of subjects required for the study, we

figure 8–2 Data to Estimate Sample Size to Estimate m

figure 8–3 Sample Size to Estimate m

figure 8–4 Sample Size Required to Estimate m

23944_ch08_5976 102 8/3/11 11:45:11 AM

Sample Size Estimates for Confidence Intervals with a Dichotomous Outcome in One Sample 103

No information was available on the prevalence of smoking, thus p = 0.5 was used.

The margin of error, proportion (p = 0.5), and con- fidence level are input into Excel, as shown in Figure 8–6. The Z value is estimated using the NORMSINV function, as shown in Figure 8–6. Recall that the ar- gument for the NORMSINV function is the area in the lower tail of the standard normal curve (Figure 8–1). The sample size is computed using the previous formula. The result is in cell E2 and is implemented by using “=B2*(1-B2)*(D2/A2)^2”, as shown in Figure 8–7.

The final step is to round up to the next integer using the ROUNDUP function. The sample size re- quired for the study is shown in Figure 8–8. In order to ensure that a 95% confidence interval estimate of the proportion of freshmen who smoke is within 5% of the true proportion, a sample of size 385 is needed.

heart disease is within 2 units of the true mean, a sample of size 385 is needed. If the standard deviation is 15, a sample of size 217 is needed. It is extremely important to accurately estimate the standard deviation because it can dramatically affect the sample size.

8.2 Sample Size eStimateS for ConfidenCe intervalS with a diChotomouS outCome in one Sample In Chapter 8 of the textbook, we presented the following for- mula to estimate the proportion of successes in a dichotomous outcome variable in a single population:

n p p Z E

= −  

 

( )1 2

where Z is the value from the standard normal distribution reflecting the confidence level that will be used (e.g., Z = 1.96 for 95%) and E is the desired margin of error. The proportion of successes in the population is represented by p. If there is no information available to approximate p, then p = 0.5 can be used to generate the most conservative, or largest, sample size.

example 8.2. In Example 8.3 of the textbook, we deter- mined the sample size required to estimate the proportion of freshmen at a university who currently smoke cigarettes (i.e., the prevalence of smoking). The investigator wanted to ensure that a 95% confidence interval estimate of the proportion of freshmen who smoke was within 5% of the true proportion.

figure 8–5 Sample Sizes for Various Scenarios

figure 8–6 Data to Estimate Sample Size to Estimate p

figure 8–7 Sample Size to Estimate p

23944_ch08_5976 103 8/3/11 11:45:11 AM

104 Power and Sample Size Determination

they will again be weighed. The number of pounds lost will be computed for each child. A 95% confidence interval will be estimated to quantify the difference in weight lost between the two diets, and the investigator would like the margin of error to be no more than 3 pounds. Based on adult studies, the common standard deviation was estimated at 8.1 pounds.

The margin of error, standard deviation, and confidence level are input into an Excel worksheet. The Z value is esti- mated using the NORMSINV function, as shown in Figure 8–9. The sample size required per group is computed using the previous formula. The result is in cell E2 and is implemented by using “=2*(D2*B2/A2)^2”, as shown in Figure 8–10.

The final step is to round up to the next integer using the ROUNDUP function. The sample size required in each group for the study is shown in Figure 8–11. Samples of size n1 = 57 and n2 = 57 ensure that the 95% confidence interval for the difference in weight lost between diets will have a margin of error of no more than 3 pounds. (Note that in Chapter 8 of

8.3 Sample Size eStimateS for ConfidenCe intervalS with a ContinuouS outCome in two independent SampleS In Chapter 8 of the textbook, we presented the following formula to estimate the sample size required to estimate the difference in means in two independent populations:

n Z Ei

=  

 

2 2

where ni is the sample size required in each group (i = 1, 2), Z is the value from the standard normal distribution reflecting the confidence level that will be used (e.g., Z = 1.96 for 95%), and E is the desired margin of error. Again, s reflects the stan- dard deviation of the outcome variable. Recall from Chapter 6 in the textbook that when we generated a confidence interval estimate for the difference in means, we used Sp, the pooled estimate of the common standard deviation, as a measure of variability in the outcome (where Sp is computed as follows:

Sp n s n s

n n =

− + − + −

( ) ( )1 12 2 22

1 2

1 1 2

). If data are available on the

variability of the outcome in each comparison group, then Sp can be computed and used in the sample size formula. However, it is more often the case that data on the variability of the outcome are available from only one group, often the untreated (e.g., placebo control) or unexposed group. This value can be used to determine the sample sizes.

example 8.3. In Example 8.6 of the textbook, we deter- mined the sample sizes required to compare two diet pro- grams in obese children. The plan is to enroll children and weigh them at the start of the study. Each child will then be randomly assigned to one of the competing diets (low fat or low carbohydrate) and followed for 8 weeks, at which time

figure 8–8 Sample Size Required to Estimate p

figure 8–9 Data to Estimate Sample Size to Estimate (m1 – m2)

figure 8–10 Sample Size Per Group to Estimate (m1 – m2)

23944_ch08_5976 104 8/3/11 11:45:12 AM

Sample Size Estimates for Confidence Intervals with a Continuous Outcome in Matched Samples 105

required to ensure that a 95% confidence interval estimate of the mean difference in weight lost between diets was within three units of the true mean difference. Suppose that the standard deviation of the differ- ence in weight loss between a low-fat diet and a low-calorie diet is approximately 9.1 pounds based on a crossover trial conducted in adults.

The margin of error, standard deviation of the differences in weights, and the con- fidence level are input into an Excel work- sheet. The Z value is estimated using the NORMSINV function, as shown in Figure

8–12. The sample size required is computed using the previous formula. The result is in cell E2 and is implemented by using “=(D2*B2/A2)^2”. The final step is to round up to the next in- teger using the ROUNDUP function. The sample size required for the study is shown in cell F2 in Figure 8–13. In order to ensure that the 95% confidence interval estimate of the mean

the textbook, we estimated the sample size at 56 per group because we carried only two decimal places in the by-hand computations. Excel carries more decimal places, and there- fore rounding up produces sample sizes of 57 per group.)

8.4 Sample Size eStimateS for ConfidenCe intervalS with a ContinuouS outCome in matChed SampleS In Chapter 8 of the textbook, we presented the following formula to estimate the sample size required to estimate the mean difference of a continuous outcome variable in two matched populations:

n Z

E d=

 

 

s 2

where Z is the value from the standard normal distribution re- flecting the confidence level that will be used (e.g., Z = 1.96 for 95%), E is the desired margin of error, and sd is the standard deviation of the difference scores. It is extremely important that the standard deviation of the difference scores (e.g., the difference based on measurements over time or the differ- ence between matched pairs) is used to appropriately estimate the sample size.

example 8.4. Consider again the diet study pro- posed in Example 8.3 of this workbook (and in Ex- ample 8.7 in the textbook). The investigator considered an alternative design, a crossover trial, where each participant will follow each diet for 8 weeks. At the end of each 8-week period, the weight lost during that pe- riod will be measured. The difference in weight lost on the low-fat diet and the low-carbohydrate diet will be computed for each child, and a confidence interval for the mean difference in weight lost will be computed. The investigator wanted to determine the sample size

figure 8–11 Sample Size Per Group Required to Estimate (m1 – m2)

figure 8–13 Sample Size to Estimate md

figure 8–12 Data to Estimate Sample Size to Estimate md

23944_ch08_5976 105 8/3/11 11:45:13 AM

106 Power and Sample Size Determination

previous formula. The result is in cell F2 and is implemented by using “=(B2*(1-B2)+C2*(1-C2))*(E2/A2)^2”, as shown in Figure 8–15. The final step is to round up to the next integer using the ROUNDUP function. The sample size required in each group for the study is shown in Figure 8–16.

difference in weight lost between diets is within three units of the true mean, a sample of size 36 children is needed.

8.5 Sample Size eStimateS for ConfidenCe intervalS with a diChotomouS outCome in two independent SampleS In Chapter 8 of the textbook, we presented the following for- mula to estimate the difference in proportions between two in- dependent populations (i.e., to estimate the risk difference):

n p p p p Z Ei

= − + −  

 

{ ( ) ( )}1 1 2 2 2

1 1

where ni is the sample size required in each group (i = 1, 2), Z is the value from the standard normal distribution reflecting the confidence level that will be used (e.g., Z = 1.96 for 95%), and E is the desired margin of error. The proportions of suc- cesses in each comparison group are represented by p1 and p2. Again, we are planning a study to generate a 95% confidence interval for the difference in unknown proportions, and the formula to estimate the sample sizes requires p1 and p2. In order to estimate the sample size, we need approximate values of p1 and p2. The values of p1 and p2 that maximize the sample size are p1 = p2 = 0.5. Thus, if there is no information available to approximate p1 and p2, then 0.5 can be used to generate the most conservative, or largest, sample sizes.

example 8.5. In Example 8.9 in the textbook, an investi- gator determined the sample size to estimate the impact of smoking on incidence of prostate cancer. Men who are free of prostate cancer will be enrolled at age 50 years and followed for 30 years. The plan is to enroll approximately equal numbers of smokers and nonsmokers in the study and to follow them prospectively for the outcome of interest, a diagnosis of pros- tate cancer. The plan is to generate a 95% confidence interval for the difference in proportions of smoking and nonsmok- ing men who develop prostate cancer. How many men should be enrolled in the study to ensure that the 95% confidence interval for the difference in proportions has a mar- gin of error of no more than 5%? Estimates of the incidence of prostate cancer from a previous study were used to design the study: p1 = 0.34 and p2 = 0.17.

The margin of error, estimates of pro- portions, and confidence level are input into an Excel worksheet. The Z value is es- timated using the NORMSINV function, as shown in Figure 8–14. The sample size required per group is computed using the

figure 8–14 Data to Estimate Sample Size to Estimate (p1 – p2)

figure 8–15 Sample Size Per Group to Estimate (p1 – p2)

figure 8–16 Sample Size Per Group Required to Estimate (p1 – p2)

23944_ch08_5976 106 8/3/11 11:45:14 AM

Sample Size Estimates for Tests of Means in One Sample 107

where a is the selected level of significance and Z1–α/2 is the value from the standard normal distribution holding 1 – a / 2 below it. The selected power is 1 – b, and Z1–β is the value from the standard normal distribution holding 1 – b below it. ES is the effect size, defined as follows:

ES = −µ µ s

1 0

where m1 is the mean under the alternative hypothesis, m0 is the mean under the null hypothesis, and s is the standard deviation of the outcome of interest.

example 8.6. In Example 8.10 of the textbook, we deter- mined the sample size to test whether the mean blood glucose level in people who drink at least two cups of coffee per day is different from the reported mean of 95 mg/dL (s = 9.8mg/dL). Investigators wanted a sample size that would ensure 80% power to detect a mean of 100 mg/dL. A two-sided test is planned with a 5% level of significance.

Before we compute the sample size, we first must compute the effect size. This is done by entering the mean under the null hypothesis, the mean under the alternative hypothesis, and the standard deviation into an Excel worksheet, as shown in Figure 8–17.

The effect size is shown in cell B7 and is computed as “=ABS(B3-B1)/B5” where ABS is the Excel function to com- pute the absolute value of the difference in means under the null and alternative hypotheses. The next step is to compute the Z value for the selected level of significance (i.e., Z1–α/2) and the Z value for the desired power (i.e., Z1–β). We first enter the level of significance, a , and the desired power. This is shown in Figure 8–18.

Samples of size n1 = 562 men who smoke and n2 = 562 men who do not smoke ensure that the 95% confidence in- terval for the difference in incidence of prostate cancer will have a margin of error of no more than 5%.

8.6 iSSueS in eStimating Sample Size for hypotheSiS teSting In Chapter 8 of the textbook, we presented formulas to deter- mine the sample size required to ensure a specified power in a test of hypothesis. Excel does not have a specific function to perform the computations, but the sample size formulas can be programmed into Excel to determine the appropriate sample size(s). The sample size formulas for hypothesis testing depend on the nature of the outcome variable (e.g., continuous or dichotomous) and also the number of comparison groups involved (e.g., one, two independent, or two matched). All of the sample size formulas contain the following two terms: Z1–α/2 and Z1–β, where a is the probability of a Type I error or the specified level of significance (e.g., 0.05), b is the prob- ability of a Type II error, and 1 – b is the specified power (e.g., 0.80, 0.90). Z1–α/2 is the value from the standard normal dis- tribution holding 1 – a/2 below it, and Z1–β is the value from the standard normal distribution holding 1 – b below it.

The NORMSINV function is used to compute these val- ues. The NORMSINV function returns the value from the standard normal distribution, Z, which holds a specified area below it (i.e., in the lower tail):

=NORMSINV(lower tail area)

For example, if a = 0.05, then Z1–α/2 = Z0.975 is computed by “=NORMSINV(0.975)”. If power = 0.80, then Z1–β is com- puted by “=NORMSINV(0.80)”.

8.7 Sample Size eStimateS for teStS of meanS in one Sample

In Chapter 8 of the textbook, we presented a formula to de- termine the sample size required to ensure adequate power to test the following hypotheses about the mean of a continuous outcome variable in a single population:

H0:m = m0 H1: m ≠ m0

where m0 is the known mean (e.g., a historical control). The formula for determining a sample size to ensure that the test has a specified power is as follows:

n Z Z

ES =

+

 



 

− −1 2 1

α β/

figure 8–17 Computing the Effect Size

23944_ch08_5976 107 8/3/11 11:45:15 AM

108 Power and Sample Size Determination

test has the specified power to detect the desired effect size at the specified level of significance, to determine the number of subjects required for the study, we must round up. This is done using Excel’s ROUNDUP function. The ROUNDUP function is used as follows:

=ROUNDUP(number to round, number of decimal places)

The sample size required for the study is shown in Figure 8–21. A sample of size n = 31 will ensure that a two-sided test

Recall that the argument for the NORMSINV function is the area in the lower tail of the standard normal curve (Figure 8–1). If a two-sided test is planned (which is generally the case for sample size planning) with a 5% level of significance, the area in the lower tail is defined as (1 – a / 2). Thus, we specify “(1-B9/2)” as the argument to the NORMSINV function, as shown in Figure 8–18. Z1–β is determined in the same way by using “=NORMSINV(B11)”. The computations are shown in Figure 8–19. The next step is to compute the sample size based on the effect size and the appropriate Z values for the selected a and power. This is shown in Figure 8–20.

Because the sample size formula always produces the minimum number of subjects required to ensure that the

figure 8–18 Computing Z1–α/2

figure 8–19 Computing Z1–β

figure 8–20 Determining Sample Size for Test About m

figure 8–21 Sample Size Required for Study

23944_ch08_5976 108 8/3/11 11:45:15 AM

Sample Size Estimates for Tests of Proportions in One Sample 109

tions under the null and alternative hypotheses. The next step is to compute the Z value for the selected level of significance (i.e., Z1–α/2) and the Z value for the desired power (i.e., Z1–β). We first enter the level of significance, a , and the desired power. We then use the NORMSINV function twice to com- pute Z1–α/2 and Z1–β. This is shown in Figure 8–23.

The next step is to compute the sample size based on the effect size and the appropriate Z values for the selected a and power. This is shown in Figure 8–24. As the final step, we round up to the next integer using the ROUNDUP func- tion. The sample size for the study is shown in Figure 8–25. A sample of size n = 379 stents will ensure that a two-sided test with a = 0.05 has 90% power to detect a 5% difference in the proportion of defective stents produced. (When we computed the sample size by hand in the textbook, we determined that n = 364 stents were needed. The difference is due to the fact that Excel is carrying more decimal places in the computations.)

with a = 0.05 has 80% power to detect a 5 mg/dL difference in mean fasting blood glucose levels.

8.8 Sample Size eStimateS for teStS of proportionS in one Sample In Chapter 8 of the textbook, we presented a formula to deter- mine the sample size required to ensure adequate power to test the following hypotheses about the proportion of successes in a dichotomous outcome variable in a single population:

H0: p = p0 H1: p ≠ p0

where p0 is the known proportion (e.g., a historical control). The formula for determining a sample size to ensure that the test has a specified power is as follows:

n Z Z

ES =

+

 



 

− −1 2 1

α β/

where a is the selected level of significance, and Z1–α/2 is the value from the standard normal distribution holding 1 – a / 2 below it. The selected power is 1 – b, and Z1–β is the value from the standard normal distribution holding 1 – b below it. ES is the effect size, defined as follows:

ES p p

p p =

−

− 1 0

0 01( )

where p0 is the proportion of successes under H0, and p1 is the proportion of successes under H1. The numerator of the effect size, the absolute value of the difference in proportions Ôp1 – p0Ô, again represents what is considered a clinically meaning- ful or practically important difference in proportions.

example 8.7. In Example 8.13 of the textbook, we deter- mined the sample size to test whether the percentage of defec- tive stents produced by a manufacturer was more than 10%. The manufacturer wanted the test to have 90% power to detect an absolute difference in proportions of 0.05 (i.e., from 10% to 15% defectives). How many stents must be evaluated? A two- sided test will be used with a 5% level of significance.

Before we compute the sample size, we first must com- pute the effect size. This is done by entering the proportion under the null hypothesis and the proportion under the al- ternative hypothesis into an Excel worksheet, as shown in Figure 8–22.

The effect size is shown in cell B5 and is computed as “=ABS(B3-B1)/sqrt(B1*(1-B1))”, where ABS is the Excel func- tion to compute the absolute value of the difference in propor-

figure 8–22 Computing the Effect Size

figure 8–23 Computing Z1–α/2 and Z1–β

23944_ch08_5976 109 8/3/11 11:45:16 AM

110 Power and Sample Size Determination

H0: m1 = m2 H1: m1 ≠ m2

where m1 and m2 are the means in the two comparison popu- lations. The formula for determining sample size required in each group to ensure that the test has a specified power is as follows:

n Z Z

ESi =

+

 



 

− −2 1 2 1 2

α β/

where ni is the sample size required in each group (i = 1, 2), a is the selected level of significance, and Z1–α/2 is the value from the standard normal distribution holding 1 – a / 2 below it, 1 – b is the selected power, and Z1–β is the value from the standard normal distribution holding 1 – b below it. ES is the effect size, defined as follows:

ES = −µ µ s

1 2

where Ωm1 – m2Ω is the absolute value of the difference in means between the two groups representing what is considered a clinically meaningful or practically important difference in means. The standard deviation of the outcome of interest is represented by s. If data are available on variability of the out- come in each comparison group, then Sp (the pooled estimate of the common standard deviation) can be computed and used to generate the sample sizes. However, it is more often the case that data on the variability of the outcome are available from only one group, usually the untreated (e.g., placebo control or unexposed) group.

example 8.8. In Example 8.14 in the textbook, we deter- mined the sample sizes required for a clinical trial to evaluate the efficacy of a new drug designed to reduce SBP. The plan was to enroll participants and to randomly assign them to receive either the new drug or a placebo and to measure SBP in each participant after 12 weeks on the assigned treatment. Investigators indicated that a five-unit difference in mean SBP would represent a clinically meaningful difference. How many patients should be enrolled in the trial to ensure that the power of the test is 80% to detect this difference? A two- sided test is planned with a 5% level of significance, and the standard deviation was assumed to be 19.0 based on data from the Framingham Heart Study.

We first compute the effect size based on the hypoth- esized difference in means under the alternative hypothesis and the standard deviation. The data are entered into an Excel worksheet, as shown in Figure 8–26. The effect size is shown

8.9 Sample Size eStimateS for teStS of differenCeS in meanS in two independent SampleS In Chapter 8 of the textbook, we presented a formula to de- termine the sample size required to ensure adequate power to test the following hypotheses about the difference in means in two independent populations:

figure 8–24 Determining Sample Size for Test About p

figure 8–25 Sample Size Required for Study

23944_ch08_5976 110 8/3/11 11:45:17 AM

Sample Size Estimates for Tests of Differences in Means in Two Independent Samples 111

the specified level of significance, to determine the numbers of subjects per group required for the study, we must round up. This is done using Excel’s ROUNDUP function. The sample sizes required per group are shown in Figure 8–29. Samples of size n1 = 227 and n2 = 227 will ensure that the test of hy- pothesis will have 80% power to detect a five-unit difference in mean SBPs in patients receiving the new drug, as compared to patients receiving the placebo.

in cell B5 and is computed as “=ABS(B1)/B3”. Notice that the hypothesized difference in means is specified in Figure 8–26. In some applications, the means under the null and alterna- tive hypotheses are specified, in which case the difference is computed and used as the numerator in the computation of the effect size. We next enter the level of significance, s, and the desired power to compute Z1–s/2 and the Z1–β. This is shown in Figure 8–27.

The next step is to compute the sample size per group based on the effect size and the appropriate Z values for the selected a and power. This is shown in Figure 8–28. Finally, because the sample size formula always produces the mini- mum number of subjects per group required to ensure that the test has the specified power to detect the desired effect size at

figure 8–26 Computing the Effect Size

figure 8–27 Computing Z1–s/2 and Z1–β

figure 8–28 Determining Sample Size per Group

figure 8–29 Sample Size Required per Group for Study

23944_ch08_5976 111 8/3/11 11:45:18 AM

112 Power and Sample Size Determination

data are entered into an Excel worksheet, as shown in Figure 8–30. The effect size is shown in cell B5 and is computed as “=ABS(B1)/B3”. We next enter the level of significance, a , and the desired power to compute Z1–α/2 and the Z1–β. This is shown in Figure 8–31.

The next step is to compute the sample size based on the effect size and the appropriate Z values for the selected a and power. This is shown in Figure 8–32. Finally, because the sample size formula always produces the minimum number of subjects required to ensure that the test has the specified power to detect the desired effect size at the specified level of significance, to determine the numbers of subjects required for the study, we must round up. This is done using Excel’s

8.10 Sample Size eStimateS for teStS of mean differenCeS in matChed SampleS In Chapter 8 of the textbook, we presented a formula to deter- mine the sample size required to ensure adequate power to test the following hypotheses about the mean difference in a con- tinuous outcome based on matched or paired populations:

H0: md = 0 H1: md ≠ 0

where md is the mean difference in the population. The for- mula for determining the sample size (i.e., number of par- ticipants, each of whom will be measured twice) required to ensure that the test has a specified power is as follows:

n Z Z

ES =

+

 



 

− −1 2 1

α β/

where a is the selected level of significance and Z1–α/2 is the value from the standard normal distribution holding 1 – a / 2 below it, and 1 – b is the selected power and Z1–β is the value from the standard normal distribution holding 1 – b below it. ES is the effect size, defined as follows:

ES d d

= µ s

where md is the mean difference expected under the alternative hypothesis, H1, and sd is the standard deviation of the differ- ence in the outcome (e.g., the difference based on measure- ments over time or the difference between matched pairs).

example 8.9. In Example 8.16 of the textbook we generated sample size requirements for a crossover trial to compare two diet programs for their effectiveness in promoting weight loss. The proposed study will have each child follow each diet for 8 weeks. At the end of each 8-week period, the weight lost dur- ing that period will be measured. The difference in weight lost between the diets will be computed for each child, and the plan is to test if there is a statistically significant differ- ence in weight lost between the diets. How many children are required to ensure that a two-sided test with a 5% level of significance has 80% power to detect a mean difference of 3 pounds in weight lost between the two diets? Based on a previous study, the standard deviation in the differences in weight loss is estimated at 9.1 pounds.

We first compute the effect size based on the hypoth- esized mean difference between weight loss programs and the standard deviation of the differences in weight loss. The

figure 8–30 Computing the Effect Size

figure 8–31 Computing Z1–α/2 and Z1–β

23944_ch08_5976 112 8/3/11 11:45:18 AM

Sample Size Estimates for Tests of Proportions in Two Independent Samples 113

the following hypotheses about the difference in proportions in two independent populations:

H0: p1 = p2 H1: p1 ≠ p2

where p1 and p2 are the proportions in the two comparison populations. The formula for determining the sample size required in each group to ensure that the test has a specified power is as follows:

n Z Z

ESi =

+

 



 

− −2 1 2 1 2

α β/

ES p p

p p =

−

− 1 2

1( )

where Ωp1 – p2Ω is the absolute value of the difference in pro- portions between the two groups expected under the alterna- tive hypothesis, H1, and p is the overall proportion, based on pooling the data from the two comparison groups (p can be computed by taking the mean of the proportions in the two comparison groups, assuming that the groups will be of ap- proximately equal size).

example 8.10. In Example 8.18 of the textbook, we deter- mined the sample size needed for a clinical trial proposed to evaluate the efficacy of a new drug designed to reduce SBP. The primary outcome is diagnosis of hypertension (yes/no), defined as an SBP above 140 or a diastolic blood pressure above 90. In planning the trial, investigators hypothesized that 30% of the participants would meet the criteria for hy- pertension in the placebo group and that the new drug would be considered efficacious if there was a 20% reduction in the proportion of patients receiving the new drug who meet the criteria for hypertension (i.e., if the proportion is 24% among patients receiving the new drug). How many patients should be enrolled in the trial to ensure that the power of the test is 80% to detect this difference in the proportions of patients with hypertension? A two-sided test will be used with a 5% level of significance.

We first compute the effect size based on the hypoth- esized difference in proportions. The proportion expected

ROUNDUP function. The sample size required is shown in Figure 8–33. A sample of size n = 73 children will ensure that a two-sided test with a = 0.05 has 80% power to detect a mean difference of 3 pounds between diets using a crossover trial (i.e., each child will be measured on each diet).

8.11 Sample Size eStimateS for teStS of proportionS in two independent SampleS In Chapter 8 of the textbook we presented a formula to deter- mine the sample size required to ensure adequate power to test

figure 8–32 Determining Sample Size for Test About md

figure 8–33 Sample Size Required for Study

23944_ch08_5976 113 8/3/11 11:45:19 AM

114 Power and Sample Size Determination

formula always produces the minimum number of subjects per group required to ensure that the test has the specified power to detect the desired effect size at the specified level of significance, to determine the numbers of subjects per group required for the study, we must round up. This is done using Excel’s ROUNDUP function. The sample sizes required per group are shown in Figure 8–39.

Samples of size n1 = 860 patients on the new drug and n2 = 860 patients on placebo will ensure that the test of hy- pothesis will have 80% power to detect a 20% reduction in the proportions of patients who meet the criteria for hyperten-

in the placebo group is entered into cell B1, and the propor- tion expected in the treatment group is computed as a 20% reduction in B1 using “=B1*(1-0.2)”. The data in the Excel worksheet are shown in Figure 8–34. Before computing the effect size, we need to compute the overall proportion. This is done by taking the mean of the proportions in the two treat- ment groups using “=(B1+B3)/2”. The computation is shown in Figure 8–35.

In Figure 8–36, we compute the effect size. We next enter the level of significance, a , and the desired power to compute Z1–α/2 and the Z1–β. This is shown in Figure 8–37. The next step is to compute the sample size per group based on the effect size and the appropriate Z values for the selected a and power. This is shown in Figure 8–38. Finally, because the sample size

figure 8–34 Data for Effect Size for Test About p1 = p2

figure 8–35 Computing Overall Proportion p

figure 8–36 Computing the Effect Size

figure 8–37 Computing Z1–α/2 and Z1–β

23944_ch08_5976 114 8/3/11 11:45:20 AM

Practice Problems 115

8.12 praCtiCe problemS

1. Suppose we want to design a new placebo-controlled trial to evaluate an experimental medication to increase lung capacity. The primary outcome is peak expiratory flow rate, a continuous variable measured in liters per minute. The primary outcome will be measured after 6 months of treatment. The mean peak expiratory flow rate in adults is 300 with a standard deviation of 50. How many subjects should be enrolled to ensure 80% power to detect a dif- ference of 15 liters per minute with a two-sided test and a = 0.05?

2. An investigator wants to estimate caffeine consumption in high school students. How many students would be required to ensure that a 95% confidence interval estimate for the mean caffeine intake (measured in milligrams) is within 15 units of the true mean? Assume that the stan- dard deviation in caffeine intake is 68 mg.

3. Consider the study proposed in Problem 2. How many students would be required to estimate the proportion of students who consume coffee? Suppose we want the estimate to be within 5% of the true proportion with 95% confidence.

4. A clinical trial was conducted comparing a new com- pound designed to improve wound healing in trauma patients to a placebo. After treatment for 5 days, 58% of the patients taking the new compound had a substantial reduction in the size of their wounds, as compared to 44% in the placebo group. The trial failed to show significance. How many subjects would be required to detect the dif- ference in proportions observed in the trial with 80% power? A two-sided test is planned at a = 0.05.

5. A crossover trial is planned to evaluate the impact of an educational intervention program to reduce alcohol con- sumption in patients determined to be at risk for alcohol problems. The plan is to measure alcohol consumption (the number of drinks on a typical drinking day) before the intervention and then again after participants com- plete the educational intervention program. How many participants would be required to ensure that a 95% con- fidence interval for the mean difference in the number of drinks is within two drinks of the true mean difference? Assume that the standard deviation of the difference in the mean number of drinks is 6.7 drinks.

6. An investigator wants to design a study to estimate the difference in the proportions of men and women who

sion. After the Excel formulas are programmed to compute the sample size(s) required to ensure a specified power in a test of hypothesis, other scenarios can be considered easily by changing the inputs (e.g., a , the desired power, the difference in the parameter reflecting a clinically meaningful change, or the standard deviation).

figure 8–38 Determining Sample Size per Group

figure 8–39 Sample Size Required per Group for Study

23944_ch08_5976 115 8/3/11 11:45:20 AM

116 Power and Sample Size Determination

test of hypothesis has 80% power to detect an increase in BMI of two units? Assume that the standard deviation in BMI is 5.7.

8. An investigator wants to design a study to estimate the difference in the mean BMI between boys and girls aged 12 years who live in New York City. How many boys and girls are needed to ensure that a 95% confidence inter- val estimate for the difference in mean BMI between boys and girls has a margin of error not exceeding two units? Use the estimate of the variability in BMI from Problem 7.

develop early onset cardiovascular disease (defined as cardiovascular disease before age 50 years). A study con- ducted 10 years ago found that 15% and 8% of men and women, respectively, developed early onset cardiovas- cular disease. How many men and women are needed to generate a 95% confidence interval estimate for the differ- ence in proportions with a margin of error not exceeding 4%?

7. The mean body mass index (BMI) for boys aged 12 years is 23.6. An investigator wants to test whether the BMI is higher in boys aged 12 years who live in New York City. How many boys are needed to ensure that a two-sided

23944_ch08_5976 116 8/3/11 11:45:20 AM

mated slope. Excel has statistical functions that can be used to estimate the Y-intercept and slope.

Example 9.1. Suppose we wish to estimate the equation of the line that best describes the relationship between systolic blood pressure (SBP) and age. The data from Table 9–1 are entered into an Excel worksheet, as shown in Figure 9–1. Note that the Excel worksheet contains n = 40 observations; only the first 20 are shown in Figure 9–1.

Excel has several functions that are useful for linear re- gression analysis. We use the INTERCEPT and SLOPE func- tions to estimate the y-intercept and slope, respectively, for a simple linear regression analysis. The functions are used as follows:

INTERCEPT(data range for y variable, data range for x variable)

SLOPE(data range for y variable, data range for x variable)

In our example, the dependent variable (y) is SBP, which is contained in cell D2 through cell D41. Age is the independent variable (x), which is contained in cell A2 through cell A41. In Figure 9–2 we estimate the y-intercept (see formula in top menu bar). Next we estimate the slope using the SLOPE func- tion, as shown in Figure 9–3. The estimate of the y-intercept is b0 = 89.40, and the estimate of the slope is b1 = 0.56. The regression equation relating age to SBP is as follows:

ŷ = 89.40 + 0.56 Age

In Chapter 9 of the textbook, we introduced regression analy- sis. We noted that regression analysis is a very general and widely applied technique. In the textbook we focused more on the use of regression analysis to assess confounding and effect modification. We limit our focus here to estimating and interpreting simple linear and multiple linear regression models using Excel Mac 2008 functions.

We use data collected from 40 randomly selected partici- pants of the sixth examination of the Framingham Offspring Study (n = 40) to illustrate regression analysis using Excel. The data are shown in Table 9–1 and include each partici- pant’s age (in years), sex (which is coded 1 for males and 0 for females), body mass index (BMI), systolic and diastolic blood pressures, total cholesterol, HDL cholesterol, diabetes (coded 1 for participants diagnosed with diabetes and 0 otherwise), and current smoking status (coded 1 for current smokers and 0 otherwise).

9.1 SimplE linEar rEgrESSion analySiS In Chapter 9 of the textbook, we introduced simple linear re- gression analysis as a technique for estimating the equation that best describes the linear association between a continuous dependent or outcome variable, Y, and a single independent or predictor variable, X. The independent variable can be continuous or dichotomous (sometimes called an indictor variable). The regression equation is as follows:

ŷ = b0 + b1x

where ŷ is the estimated value of the dependent or outcome variable, b0 is the estimated Y-intercept, and b1 is the esti-

Chapter 9 Regression Analysis

23944_ch09_5976 117 8/3/11 11:46:18 AM

118 Regression Analysis

TablE 9–1 Data from 40 Randomly Selected Participants (n = 40) of the Sixth Examination of the Framingham Offspring Study

Age Male Sex BMI SBP DBP Total Cholesterol HDL Diabetes Smoke

48.2683 1 27.92 140 88 184 35 1 1 47.3347 1 32.61 118 77 178 48 0 1 47.1129 1 34.83 112 69 177 33 0 0 49.0541 1 28.76 128 84 246 54 0 0 45.9548 1 26.76 121 85 193 43 0 0 54.5243 1 27.01 126 77 182 40 0 0 56.2409 1 28.76 124 77 246 50 0 1 52.1068 1 24 131 80 167 40 1 0 56.011 1 30.37 129 81 176 39 1 0 58.2012 1 27.88 121 85 210 45 0 1 51.1129 1 19.67 93 59 174 63 0 0 53.1444 1 25.45 111 79 180 58 0 0 68.8241 1 23.1 151 75 192 31 1 0 66.8611 1 27.44 132 76 180 50 0 1 66.8446 1 29.03 137 56 129 39 0 0 62.152 1 27.25 144 82 216 57 0 0 69.2293 1 24.68 109 75 184 64 0 0 64.3723 1 34.44 133 77 271 50 1 0 61.2567 1 22.86 104 68 198 51 0 0 66.9624 1 27.84 122 60 180 33 1 0 71.7454 1 27.7 137 81 198 44 1 0 71.0089 1 31.04 136 75 213 62 0 0 77.4456 1 34.06 110 57 181 45 0 0 34.6557 0 21.8 99 60 178 33 0 0 59.0773 0 23.59 124 76 212 47 0 0 45.7659 0 22.39 118 77 258 56 0 0 55.9808 0 26.18 110 66 263 50 0 0 47.5729 0 24.86 103 66 183 47 0 0 59.4798 0 32.89 123 85 203 40 0 0 58.3381 0 24.47 118 61 230 81 0 0 50.0589 0 21.98 110 68 168 72 0 1 52.6845 0 25.12 105 67 201 61 0 0 51.7016 0 39.93 131 80 197 43 0 0 58.8255 0 38.14 107 69 224 29 0 0 64.5859 0 25.86 138 68 205 53 0 0 67.0418 0 30.95 135 72 210 36 0 0 62.642 0 31.99 123 65 209 70 0 0 71.8248 0 19.03 103 50 206 63 0 0 76.6899 0 21.8 137 85 176 74 0 0 73.9932 0 33.07 135 80 254 57 0 0

23944_ch09_5976 118 8/3/11 11:46:18 AM

Multiple Linear Regression Analysis 119

in Figure 9–1. We again use the INTER- CEPT and SLOPE functions to estimate the parameters of the simple linear regres- sion model. The dependent or outcome variable (y) is SBP, which is contained in cell D2 through cell D41. The independent variable (x) in this example is smoking, and the smoking data is contained in cell I2 through cell I41. In Figure 9–4 we esti- mate the Y-intercept.

Next we estimate the slope using the SLOPE function, as shown in Figure 9–5. The estimate of the y-intercept is b0 = 121.85, and the estimate of the slope is b1 = 2.31. The regression equation relating current smoking status to SBP is:

ŷ = 121.85 + 2.31 Current Smoking

where ŷ is the estimated SBP. The regres- sion equation indicates that smokers have higher SBPs by approximately 2.31 units, as compared to nonsmokers.

9.2 mulTiplE linEar rEgrESSion analySiS In Chapter 9 of the textbook, we introduced multiple linear regression analysis as a technique for estimating the equa- tion that best describes the association between a continuous outcome variable Y and a set of independent variables, X1, X2, º , Xp. The independent variables can be continuous or dichotomous.

FigurE 9–1 Data for Regression Analysis

FigurE 9–2 Estimating the Y-Intercept

where ŷ is the estimated SBP. The regression equation indi- cates that each additional year of age is associated with a 0.56 unit increase in SBP.

Example 9.2. Suppose we wish to assess whether there is an association between SBP and current smoking status using the data in Table 9–1, which were entered into the Excel worksheet

23944_ch09_5976 119 8/3/11 11:46:19 AM

120 Regression Analysis

FigurE 9–3 Estimating the Slope

FigurE 9–4 Estimating the Y-Intercept

FigurE 9–5 Estimating the Slope

23944_ch09_5976 120 8/3/11 11:46:19 AM

Multiple Linear Regression Analysis 121

The regression equation is as follows:

ŷ = b0 + b1x1 + b2x2 + º bpxp where ŷ is the predicted or expected value of the dependent variable, x1 through xp are p distinct independent or predictor variables, b0 is the value of y when all of the independent vari- ables (x1 through xp) are equal to zero, and b1 through bp are the estimated regression coefficients. Excel has a function that can be used to estimate the coefficients of a multiple regres- sion equation.

Example 9.3. Suppose we again consider SBP as our dependent or outcome variable. We now wish to assess the association be- tween age and sex, considered simultaneously, on SBP using the data in Table 9–1. We again use Excel Mac 2008 functions to estimate the parameters of the multiple linear regression equation. Specifically, we use the LINEST function, which is used as follows:

LINEST(data range for y variable, data range for x1, x2, º , xp)

It is important to note that for multiple regression analy- sis, the independent or predictor variables (x1, x2, º , xp) must be in adjacent columns in the Excel worksheet. When we specify the location of the cells containing the indepen- dent variables (i.e., data range for x1, x2, º , xp), we specify the locations of the first and last cells in the adjacent columns containing the data.

In Example 9.3, the outcome is SBP, which is contained in cell D2 through cell D41. The independent variables (x1 and x2) are age and sex. The age data is con- tained in cell A2 through cell A41, and the sex data (in this example, the variable is an indicator of male sex: 1 = male, 0 = female) is contained in cell B2 though cell B41. The range A2:B41 in-

FigurE 9–6 Using the LINEST Function for Multiple Linear Regression Analysis

FigurE 9–7 Estimates of Regression Coefficients in Multiple Linear Regression Model

cludes both independent variables. In cell K2 in Figure 9–6 we specify the data ranges for the dependent and independent variables in the LINEST function.

Notice that the LINEST function returns one number (5.383 in this example) in cell K2. LINEST is actually an array function that produces results that occupy multiple cells in the worksheet. To view the estimates of the regression coefficients for the multiple regression equation, we need to first highlight cell K2 through cell M2 in the worksheet. In this example, the LINEST function has estimated three regression coefficients, b0, b1, and b2, and thus we highlight three cells. We then press CTRL + U (at the same time) and then Command + Return (at the same time). When we enter these two sequences of key- strokes, the estimates of the regression coefficients are shown in cell K2 through cell M2, as shown in Figure 9–7.

23944_ch09_5976 121 8/3/11 11:46:20 AM

122 Regression Analysis

Example 9.4. We now consider HDL as our dependent or outcome variable and want to assess the association between BMI and sex, considered simultaneously, on HDL using the data in Table 9–1.

We again use the LINEST function to estimate the re- gression coefficients of the multiple regression model. In this example, the outcome is HDL, which is contained in cell G2 through cell G41. The independent variables (x1 and x2) in this example are sex and BMI. The sex data is contained in cell B2 through cell B41, and the BMI data is contained in cell C2 through cell C41. The range B2:C41 includes both inde- pendent variables. In cell K6 in Figure 9–8 we specify the data ranges for the variables in the LINEST function.

Again, the LINEST function returns one number (–0.943 in this example) in cell K6 in the worksheet. To view the esti- mates of the regression coefficients for the multiple regression equation, we first highlight cell K6 through cell M6 in the

worksheet. In this exam- ple, the LINEST func- tion has estimated three regression coefficients, b0, b1, and b2, and thus we highlight three cells. We then press CTRL + U (at the same time) and then Command + Re- turn (at the same time). After we enter these two sequences of keystrokes, the estimates of the re- gression coefficients are shown in cell K6 through cell M6, as shown in Fig- ure 9–9.

The value in cell M6 is the estimate of b0, the value in cell L6 is the estimate of b1, and the value in cell K6 is the estimate of b2. Thus, the estimate of the coef- ficients of the multiple regression equation are as follows: b0 = 79.38, b1 = –6.31, and b2 = –0.94. The regression equation relating sex and BMI to HDL is as follows:

FigurE 9–8 Using the LINEST Function for Multiple Linear Regression Analysis

FigurE 9–9 Estimates of Regression Coefficients in Multiple Linear Regression Model

The value in cell M2 is the estimate of b0, the value in cell L2 is the estimate of b1, and the value in cell K2 is the estimate of b2. Thus, the estimate of the coefficients of the multiple regression equation are as follows: b0 = 87.19, b1 = 0.54, and b2 = 5.38. The regression equation relating age and sex to SBP is as follows:

ŷ = 87.19 + 0.54 Age + 5.38 Male Sex

where ŷ is the estimated SBP. The multiple regression equa- tion indicates that each additional year of age is associated with a 0.54 unit increase in SBP, holding sex constant, and that men have higher SBPs than women by about 5.38 units, holding age constant.

There are a number of additional statistics that are avail- able with the LINEST function. Here we restrict our attention to the use of the LINEST function to estimate the regression coefficients.

23944_ch09_5976 122 8/3/11 11:46:20 AM

Multiple Linear Regression Analysis 123

FigurE 9–10 Preparing Data for Multiple Regression Analysis

FigurE 9–11 Using the LINEST Function for Multiple Linear Regression Analysis

ŷ = 79.38 – 6.31 Male Sex – 0.94 BMI

where ŷ is the estimated HDL. The multiple regression equa- tion indicates that men have lower HDL than women by about 6.31 units, holding BMI constant, and that each additional unit of BMI is associated with a 0.94 unit reduction in HDL. Recall that HDL is the good cholesterol and that higher values are better. Thus, higher BMI is associated with lower (more unhealthy) HDL.

Suppose in Example 9.3 we wished to consider sex and diabetes as the independent variables. In order to use the LINEST function, we need to reorganize the data in the Excel

worksheet so that sex and diabetes are in adjacent columns (to correctly specify the data range for these independent variables). This can be done in several different ways. An easy way is to copy the data from columns B and H into columns K and L, as shown in Figure 9–10. We now use the LINEST function to estimate the regression coefficients. In cell N2 in Figure 9–11 we specify the data ranges for the variables in the LINEST function.

Again, the LINEST function returns one number (–11.268 in this example) in cell N2. To view the estimates of the regres- sion coefficients for the multiple regression equation, we need to first highlight cell N2 through cell P2 in the worksheet. In

23944_ch09_5976 123 8/3/11 11:46:21 AM

124 Regression Analysis

FigurE 9–12 Estimates of Regression Coefficients in Multiple Linear Regression Model

this example, the LINEST function has estimated three regres- sion coefficients, b0, b1, and b2, and thus we highlight three cells. We then press CTRL + U (at the same time) and then Command + Return (at the same time). After we enter these two sequences of keystrokes, the estimates of the regression coefficients are shown in cell N2 through cell P2, as shown in Figure 9–12.

The value in cell P2 is the estimate of b0, the value in cell O2 is the estimate of b1, and the value in cell N2 is the estimate of b2. Thus, the estimate of the coefficients of the multiple regression equation are as follows: b0 = 53.65, b1 = –3.52, and b2 = –11.27. The regression equation relating sex and diabetes to HDL is as follows:

ŷ = 53.65 – 3.52 Male Sex – 11.27 Diabetes

where ŷ is the estimated HDL. The multiple regression equa- tion indicates that men have lower HDL than women by about 3.52 units, holding diabetes constant, and that patients with diabetes have lower HDL than patients who do not by about 11.27 units, holding sex constant.

9.3 pracTicE problEmS

1. Consider the data in Table 9–2 measured in a sample of n = 25 undergraduates in an on-campus survey of health behaviors. Enter the data into an Excel worksheet for analysis.

2. Using the data in Table 9–2, estimate the simple linear regression equation relating the number of cups of coffee per week to grade point average (GPA). (Consider GPA the dependent or outcome variable.)

3. Using the data in Table 9–2, estimate the simple linear regression equation relating female sex to GPA. (Consider GPA the dependent or outcome variable.)

4. Using the data in Table 9–2, estimate the multiple linear regression equation relating number of cups of coffee per week, female sex, and number of hours of exercise per week to GPA. (Consider GPA the dependent or outcome variable.)

23944_ch09_5976 124 8/3/11 11:46:21 AM

Practice Problems 125

TablE 9–2 Data for Practice Problems

ID Age Female Sex Year in School GPA

Current Smoker

# Hours Exercise per Week

Average # Drinks per Week

# Cups Coffee per Week

1 18 1 Fr 3.85 1 7 3 3 2 21 0 Jr 3.27 1 3 2 4 3 19 1 So 2.90 0 0 4 7 4 22 0 Sr 3.65 1 0 2 4 5 21 1 Sr 3.41 1 0 1 3 6 20 0 Jr 3.20 0 2 5 8 7 19 1 Jr 2.89 1 1 4 10 8 17 0 Fr 3.75 0 6 0 0 9 18 0 So 4.00 0 6 2 6

10 17 1 So 3.18 0 3 5 7 11 21 0 Jr 2.58 1 3 12 12 12 22 1 Sr 2.98 0 2 3 4 13 19 0 Fr 3.16 1 2 0 6 14 21 1 Jr 3.36 1 3 1 2 15 22 1 So 3.72 0 6 3 0 16 19 0 So 3.30 1 4 0 6 17 16 0 Fr 3.28 0 4 0 5 18 22 0 Sr 2.98 0 0 8 5 19 17 1 Fr 3.90 0 7 0 2 20 20 1 Sr 3.78 1 4 6 2 21 21 1 So 3.26 1 2 3 4 22 23 0 Jr 3.01 0 1 9 7 23 23 0 Sr 3.83 1 5 4 4 24 17 1 Fr 3.76 0 5 2 1 25 22 1 Sr 3.05 0 1 5 5

23944_ch09_5976 125 8/3/11 11:46:21 AM

23944_ch09_5976 126 8/3/11 11:46:21 AM

The test statistic (Step 2) varies depending on the specific test. When we conducted nonparametric tests of hypothesis by hand in Chapter 10 of the textbook, we ultimately drew a conclusion by comparing the test statistic to the critical value, which was derived from an appropriate probability distribu- tion table. Critical values can be found in Table 5 through Table 8 in the Appendix. We now use Excel Mac 2008 to compute the test statistics for each test. Before illustrating the computation of test statistics with Excel, we first illustrate how to use Excel to rank data, which is a key component of all nonparametric tests.

10.1 Ranking Data The nonparametric procedures that we describe here follow the same general procedure. The outcome variable (ordinal, interval, or continuous) is ranked from lowest to highest, and the analysis focuses on ranks as opposed to the measured or raw values. For example, suppose we measure self-reported pain using a visual analog scale with anchors at 0 (no pain) and 10 (agonizing pain). We record the following in a sample of six participants (n = 6):

7 5 9 3 0 2

The data are entered into Excel, as shown in Figure 10–1. The ranks, which are used to perform a nonparametric test, are assigned to the data, which are ordered from smallest to largest. Notice that the data do not have to be ordered when they are entered into Excel. The smallest value is assigned a rank of 1, the next smallest is assigned a rank of 2, and so on.

In Chapter 10 of the textbook we presented hypothesis test- ing procedures for situations with small sample sizes and outcomes that are ordinal, ranked, or continuous and cannot be assumed to be normally distributed. Nonparametric tests are based on ranks that are assigned to the ordered data. The tests involve the same five steps as parametric tests: specifying the null and alternative or research hypothesis, selecting and computing an appropriate test statistic, setting up a decision rule, and drawing a conclusion.

Four tests were presented in Chapter 10: the Mann– Whitney U test for comparing a continuous outcome in two independent samples, the Sign test and Signed Rank test for comparing a continuous outcome in two matched or paired samples, and the Kruskal–Wallis test for comparing a continu- ous outcome in more than two independent samples.

For each test we used the same general five-step approach:

Step 1. Set up hypotheses (H0 and H1) and select a level of significance, a.

Step 2. Choose the appropriate test statistic (e.g., U, the smaller of the number of positive or negative signs, W, or H).

Step 3. Determine the critical value(s) and set up the de- cision rule (which depend on a , the test statistic, and whether the test is upper, lower, or two tailed).

Step 4. Compute the test statistic based on observed sam- ple data.

Step 5. Draw a conclusion by comparing the test statistic to the critical value.

Chapter 10 Nonparametric Procedures

23944_ch10_5976 127 8/3/11 11:47:03 AM

128 Nonparametric Procedures

when we assigned ranks by hand. The RANK function ranks the data in ascending or descending order. However, if there are ties, the same ranks are assigned to the tied values. For example, suppose the following data are recorded in a sample of six participants (n = 6):

7 7 9 3 0 2

The data are entered into Excel as shown in Fig- ure 10–3, and the rank function is again specified as RANK(value,A$2:A$7,1). The ranks are shown in Figure 10–3. Notice that the 4th and 5th ordered values are both assigned ranks of 4. In nonparametric testing, we wish to assign the mean rank to values that are tied. Specifically, we assign ranks of 4.5 to the two values of 7. To assign mean ranks to the tied values, we enter the following correction in each cell (see cell C2 in Figure 10–4):

“=RANK(A2,A$2:A$7,1)+(COUNT(A$2:A$7)+1- RANK(A2,A$2:A$7,0)-RANK(A2,A$2:A$7,1))/2”

This formula is then copied from cell C2 to cell C3 through cell C7, as shown in Figure 10–4 (notice the formula in cell C2 showing in the menu bar). The corrected formula assigns the mean rank to tied values. As we work through applications, only the formula shown in column C is needed to assign ranks because it makes the appropriate adjustment to assign mean ranks when there are ties.

The largest value is assigned a rank of n (in this example, n = 6). Excel has a built-in RANK function that assigns ranks to data. The function is used as follows:

=RANK(value, data range, order)

The value is the address of the cell that we wish to rank. The data range contains the addresses of the cells contain- ing the first and last observations in the dataset, separated by a colon. For example, the data in Figure 10–1 occupy the range A2:A7. Order is specified as either 0 or 1. Order = 0 indicates that ranks are assigned in descending order, and order = 1 indicates that ranks are assigned in ascending order. Because we want to assign ranks from smallest to largest, we specify order = 1. To rank the data shown in Figure 10–1 in ascending order, we use the RANK function as follows: RANK(value,A$2:A$7,1), where the value changes depend- ing on which data point we wish to rank (i.e., cell A2 through cell A7). Notice that we use absolute cell references to indi- cate the data range (A$2:A$7) to ensure that the same data range is considered as we copy the RANK function from cell to cell. The RANK function is specified in cell B2 as “=RANK(A2,A$2:A$7,1)” and is copied from cell B2 to cell B3 through cell B7, as shown in Figure 10–2 (notice the formula in cell B7 showing in the menu bar).

Using Excel, we rank the data in one step as opposed to first ordering the data and then assigning ranks, as we did

FiguRe 10–1 Observed Data

FiguRe 10–2 Ranked Data FiguRe 10–3 Ranks in Data with Ties

23944_ch10_5976 128 8/3/11 11:47:03 AM

Tests with Two Independent Samples 129

FiguRe 10–4 Assigning Ranks Accounting for Ties

FiguRe 10–5 Ranking Data with Ties

Suppose the following are recorded in a sample of six participants (n = 6):

7 7 7 3 0 2

The data are entered into Excel as shown in Figure 10–5, and the rank function with the correction factor is specified in cell B2 as follows:

“=RANK(A2,A$2:A$7,1)+(COUNT(A$2:A$7)+1- RANK(A2,A$2:A$7,0)-RANK(A2,A$2:A$7,1))/2”

This formula is then copied from cell B2 to cell B3 through cell B7, as shown in Figure 10–5. Notice that there are three values of 7. We assign a rank of 5 (the mean of 4, 5, and 6) to

the 4th, 5th, and 6th ordered values. Using this approach of assigning the mean rank when there are ties ensures that the sum of the ranks is the same in each sample and is always equal to n(n + 1) / 2. When conducting nonparametric tests, it is useful to check the sum of the ranks before proceeding with the analysis.

10.2 tests with two inDepenDent samples The Mann–Whitney U test is used to compare a continuous outcome in two independent samples with small sample sizes and outcomes that are ordinal, ranked, or continuous and cannot be assumed to be normally distributed. The hypoth- eses, with a two-sided alternative (as is generally the case), are as follows:

23944_ch10_5976 129 8/3/11 11:47:04 AM

130 Nonparametric Procedures

the placebo and new-drug groups, combined. Recall that we must maintain the group assignments.

To perform the ranking, we create two new columns: one to contain the ranks of the data in the placebo group and the other to contain the ranks of the data in the new-drug group (Figure 10–7). The first observation in the placebo group is in cell A2. To rank this value, in cell C2 we specify the following:

“=RANK(A2,$A$2:$B$6,1)+(COUNT($A$2:$B$6)+1- RANK(A2,$A$2:$B$6,0)-RANK(A2,$A$2:$B$6,1))/2”

This formula is then copied from cell C2 to cell C3 through cell D6 as shown in Figure 10–7. Notice that we specify the range of the data as $A$2:$B$6. Specifically, we use absolute cell references for both the row and column addresses of the cells containing the data so that when the formula is copied we continue to rank the observed data that is located in cell A2 through cell B6. See the formula for cell D6 showing in the menu bar.

Before proceeding we check the assignment of the ranks by summing the ranks in each group. These are denoted R1 and R2 for the placebo and new-drug groups, respec- tively. Recall that the sum of the ranks will always equal n(n + 1) / 2 = 10(11) / 2 = 55. This is shown in Figure 10–8. Thus, R1 = 37 and R2 = 18. The test statistic for the Mann– Whitney U test is denoted U and is the smaller of U1 and U2, defined as follows:

U n n n n

1 1 2 1 1

2 1 2 2 2

1 2

= + +

−

= + +

−

( )

H0: The two populations are equal H1: The two populations are not equal

In Chapter 10 of the textbook, we presented the follow- ing formula for the test statistic, U, in the Mann–Whitney U

test: U is the smaller of U1 = n1n2 + n n1 1 1

2 ( )+

– R1 and U2 =

n1n2 + n n2 2 1

2 ( )+

– R2, where R1 and R2 are the sums of the

ranks in groups 1 and 2, respectively. When performing the test of hypothesis by hand, we

computed the test statistic U and found the appropriate critical value in Table 5 in the Appendix to set up the decision rule: Reject H0 if U ≤ Critical Value from Table 5. Excel does not have a specific function for the Mann–Whitney U test. How- ever, Excel can be used to assign ranks and to compute the test statistic. The conclusion of the test is based on a comparison of the test statistic to the appropriate critical value from Table 5 in the Appendix.

example 10.1. In Example 10.1 in the textbook we analyzed data from a Phase II clinical trial designed to investigate the effectiveness of a new drug to reduce symptoms of asthma in children. A total of n = 10 participants were randomized to receive either the new drug or a placebo. Participants recorded the number of episodes of shortness of breath over a 1-week period following receipt of the assigned treatment. The data are as follows:

Placebo 7 5 6 4 12 New drug 3 6 4 2 1

The question of interest is whether there is a difference in the number of episodes of shortness of breath over a 1-week period in participants receiving the new drug as compared to those receiving the placebo. The hypotheses to be tested are as follows, and we run the test at the 5% level of significance (i.e., a = 0.05).

H0: The two populations are equal H1: The two populations are not equal

The data are entered into Excel as shown in Figure 10–6.

The first step is to assign ranks from 1 to 10, which is done on the combined or total sample (i.e., pooling the data from the two treatment groups (n = 10)). Using Excel, we enter the data for each comparison group and then rank the pooled sample. This is done by using the procedure outlined in Section 10.1. Specifically, we use the RANK function with the correction factor to assign ranks to the observed data in

FiguRe 10–6 Data for Mann Whitney U Test

23944_ch10_5976 130 8/3/11 11:47:05 AM

Tests with Two Independent Samples 131

FiguRe 10–7 Ranking Data in the Pooled Sample

FiguRe 10–8 Summing the Ranks in Each Group

FiguRe 10–9 Determining Sample Sizes

To compute the test statistic in Excel, we first compute the sample sizes, n1 and n2, using the COUNT function. We specify the cells we wish to count as follows: COUNT(A2:A6). This totals the number of cells in the range of A2 through A6 that contain numeric data (numbers). The computation of sample sizes is shown in Figure 10–9.

In this example, the sample size of group 1 (n1) is in cell C13 and is determined by COUNT(A2:A6). The sam- ple size of group 2 (n2) is in cell D13 and is determined by COUNT(B2:B6). We now use R1, R2, n1, and n2 to compute U1 and U2 using Excel. The results are shown in Figure 10–10.

The formula used to compute U2 in cell D16 is shown in the top menu bar.

The final step is to produce the test statistic, U, which is the smaller of U1 and U2. This is shown in Figure 10–11 and is computed using the MIN (minimum) function. Thus, U = 3. To draw a final conclusion in the test, we must determine whether the observed test statistic, U, supports the null or research hypothesis. This is done by determining a critical value of U such that if the observed value of U is less than or equal to the critical value, we reject H0 in favor of H1; and if the observed value of U exceeds the critical value, we do not

23944_ch10_5976 131 8/3/11 11:47:05 AM

132 Nonparametric Procedures

that the two populations of numbers of episodes of shortness of breath are not equal.

10.3 tests with matcheD samples The Sign test and the Wilcoxon Signed Rank test are used to compare a continuous outcome in two matched or paired sam- ples with small sample sizes and outcomes that are ordinal, ranked, or continuous and cannot be assumed to be normally distributed. Recall that when data are matched or paired, we compute difference scores for each individual and analyze dif- ference scores. The hypotheses, with a two-sided alternative (as is generally the case) for both tests are as follows:

H0: The median difference is zero H1: The median difference is not zero

In Chapter 10 of the textbook, we presented the Sign test, and the test statistic is the smaller of the number of positive or negative signs of the difference scores. When performing the test of hypothesis by hand, we found the appropriate critical value in Table 6 in the Appendix to set up the following deci- sion rule: Reject H0 if the smaller of the number of positive or negative signs ≤ Critical Value from Table 6. Excel does not have a specific function for the Sign test. However, Excel can be used to compute difference scores and count the numbers of positive and negative signs and determine the smaller of the two, which is the test statistic. The conclusion of the test is based on a comparison of the test statistic to the appropriate critical value from Table 6 in the Appendix.

In Chapter 10 of the textbook, we also presented the Wilcoxon Signed Rank test and the test statistic W, which is defined as the smaller of W+ and W–, which are the sums of the positive and negative ranks of the difference scores, re- spectively. When performing the test of hypothesis by hand, we found the appropriate critical value in Table 7 in the Ap- pendix of the textbook to set up the decision rule: Reject H0 if W ≤ Critical Value from Table 7. Excel does not have a specific function for the Wilcoxon Signed Rank test. However, Excel can be used to compute difference scores, rank the differences, attach the signs, determine W+ and W–, and compute the test statistic W. The conclusion of the test is based on a comparison of the test statistic to the appropriate critical value from Table 7 in the Appendix of the textbook.

example 10.2. In Example 10.5 in the textbook, we ana- lyzed data from a study to assess quality of life (QOL) in patients with breast cancer following a new chemotherapy treatment. QOL was measured on an ordinal scale, and for analysis purposes numbers were assigned to each response

reject H0. The critical value of U is found in Table 5 in the Ap- pendix. For n1 = n2 = 5 and a two-sided level of significance (a = 0.05), the critical value is 2, and the decision rule is to reject H0 if U ≤ 2. We do not reject H0 because 3 > 2. We do not have statistically significant evidence at a = 0.05 to show

FiguRe 10–10 Computing U1 and U2

FiguRe 10–11 Computing the Test Statistic U

23944_ch10_5976 132 8/3/11 11:47:06 AM

Tests with Matched Samples 133

tion determines whether the value in cell D2 is negative (i.e., < 0). If so, it places a negative sign “–” in cell E2; if not, cell E2 is left blank “ ”. We actually wish to assign a positive sign “+” to a cell with a positive difference and a negative sign “–” to a cell with a negative difference. To do so, we specify the follow- ing in cell E2: “=IF(D2<0,“-”,IF(D2>0,“+”,“ ”))”. This condition first determines whether the value in cell D2 is negative, and if so, a negative sign “–” is placed in cell E2. If not, the condi- tion then determines whether the value in cell D2 is positive.

category as follows: 1 = Poor, 2 = Fair, 3 = Good, 4 = Very Good, 5 = Excellent. The data are shown in Table 10–1.

The question of interest is whether there is a difference in QOL after chemotherapy treatment as compared to before treatment. The test is run at a 5% level of significance, and the hypotheses are as follows:

H0: The median difference is zero H1: The median difference is not zero

The data are entered into Excel, as shown in Figure 10–12. We analyze the data using the Sign test. The test statistic for the Sign test is the smaller of the number of positive or nega- tive difference scores. We first compute difference scores by subtracting the QOL measured before treatment from that measured after (i.e., the measurement in column C minus the measurement in column B for each participant). The differ- ence scores are shown in Figure 10–13.

With the Sign test, we concern ourselves only with the signs of the difference scores. Thus, we take each difference score in column D and retain only the sign of the difference score. This is done in Excel using the IF function. Specifi- cally, we evaluate each difference score in column D (in cell D2 through cell D13) and retain only the sign of the differ- ence. The IF function is used as follows: IF(condition, result if condition is true, result if condition is false). For example, we specify the following in cell E2: “=IF(D2<0,“-”,“ ”)”. The condi-

table 10–1 Quality of Life Before and After Chemotherapy Treatment

Patient

QOL Before Chemotherapy

Treatment

QOL After Chemotherapy

Treatment

1 3 2 2 2 3 3 3 4 4 2 4 5 1 1 6 3 4 7 2 4 8 3 3 9 2 1 10 1 3 11 3 4 12 2 3

FiguRe 10–12 Data on Matched Pairs for Sign Test

FiguRe 10–13 Difference Scores

23944_ch10_5976 133 8/3/11 11:47:06 AM

134 Nonparametric Procedures

Next, we count the number of positive and negative signs using the COUNTIF function. The COUNTIF function counts the number of cells in a specified range that meet a certain criterion. In this example the number of positive signs is computed as COUNTIF(E2:E13,“+”), and the number of negative signs is computed as COUNTIF(E2:E13,“-”), as shown in Figure 10–16. The test statistic is the smaller of the number of positive or negative signs of the dif- ference scores, which is equal to 3. The appro- priate critical value for the Sign test is found in Table 6 in the Appendix, based on the sample size (or number of matched pairs, n = 12) and our two-sided level of significance (a = 0.05). The critical value for this two-sided test with n = 12 and a = 0.05 is 2, and the decision rule is as follows: Reject H0 if the smaller of the number of positive or negative signs ≤ 2. We do not reject H0 because 3 > 2. We do not have statistically significant evidence at a = 0.05 to show that there is a difference in QOL after chemotherapy treatment as compared to before treatment.

With the Sign test it is possible to com- pute a p-value for the test using the binomial distribution. In Chapter 7 of this workbook, we computed p-values for the parametric tests analyzing means, proportions, differences in means and proportions, and mean differences using the Z and t probability distributions. We use the same approach here. Specifically, we use Excel to compute the test statistic (with the Sign test, the smaller of the number of posi- tive or negative signs) and a p-value, and the investigator then compares the p-value to the predetermined level of significance to draw a conclusion about the hypotheses using the fol- lowing: Reject H0 if p ≤ a.

The test statistic for the Sign test is the smaller of the number of positive or negative

signs of the difference scores, and it follows a binomial dis- tribution with n = the number of subjects in the study and p = 0.5. In this example, n = 12 and p = 0.5. The two-sided p-value for the test is p-value = 2 × P(x ≤ 3). In Chapter 5 of this workbook, we used the BINOMDIST function to compute probabilities from the binomial distribution. The BINOM- DIST function is specified as follows: =BINOMDIST(x, n, p, cumulative). The inputs for the function are the same as those we use in computing probabilities by hand with the binomial distribution model (i.e., x, n, and p).

FiguRe 10–14 Signs of the Difference Scores

FiguRe 10–15 Assigning Signs to Differences of Zero

If so, it places a positive sign “+” in cell E2; if not, cell E2 is left blank. This condition is entered into cell E2 and is copied into cell E3 through cell E13 as shown in Figure 10–14. Notice that cells E6 and E9 are blank because the differences are nei- ther positive nor negative; they are equal to zero. Recall that if there is an even number of zeros in a dataset, we randomly assign positive and negative signs. In this example, we assign one negative sign (i.e., “–” to patient 5) and one positive sign (i.e., “+” to patient 8). The signs are entered directly into cells E6 and E9, as shown in Figure 10–15.

23944_ch10_5976 134 8/3/11 11:47:06 AM

Tests with Matched Samples 135

do not use the cumulative distribution function by specifying “false”. The cumulative distribution function returns the probability of observing x or fewer successes. For example, if we specify “true” and indicate x = 3 in the function, then Excel computes P(X ≤ 3). In contrast, if we specify “false” and indicate x = 3 in the function, then Excel computes the individual or point probabil- ity, P(X = 3). We wish to compute P(X ≤ 3) with n = 12 and p = 0.5. The computation is shown in Figure 10–17. Notice that we indicate the number of successes (x) as the smaller of E15 and E16, and n (the sample size for analysis) is determined using the COUNTIF function (the number of positive and negative signs; see the formula in the top menu bar). Because the p-value = 0.1460 exceeds the level of significance (a = 0.05), we do not have statistically significant evidence at a = 0.05 to show that there is a difference in QOL after chemotherapy treatment as compared to before treatment.

Another popular nonparametric test for matched or paired data is called the Wilcoxon

Signed Rank test. Like the Sign test, it is based on difference scores, but in addition to analyzing the signs of the differ- ences, it also takes into account the magnitude of the observed differences.

FiguRe 10–16 Summing the Numbers of Positive and Negative Signs

FiguRe 10–17 Computing the P-Value for the Sign Test

Excel requires one additional input, labeled cumulative. The last argument in the BINOMDIST function is a logical value (i.e., one whose response is true or false). We use the cumulative distribution function by specifying “true”, or we

23944_ch10_5976 135 8/3/11 11:47:07 AM

136 Nonparametric Procedures

table 10–2 Blood Pressure Before and After Exercise Program

Patient

Systolic Blood Pressure Before

Exercise Program

Systolic Blood Pressure After

Exercise Program

1 125 118 2 132 134 3 138 130 4 120 124 5 125 105 6 127 130 7 136 130 8 139 132 9 131 123 10 132 128 11 135 126 12 136 140 13 128 135 14 127 126 15 130 132

example 10.3. In Example 10.7 in the textbook, we ana- lyzed data from a study to evaluate the effectiveness of an exercise program in reducing systolic blood pressure (SBP) in patients with prehypertension (defined as an SBP between 120 and 139 mmHg or a diastolic blood pressure between 80 and 89 mmHg). A total of 15 patients (n = 15) with prehypertension enrolled in the study, and their SBPs were measured. Each pa- tient then participated in an exercise training program where they learned proper techniques and execution of a series of exercises. Patients were instructed to do the exercise program three times per week for 6 weeks. After 6 weeks, their SBPs were again measured and are shown in Table 10–2.

The question of interest is whether there is a difference in SBPs after participating in the exercise program as compared to before the exercise program. The test is run at a 5% level of significance, and the hypotheses are as follows:

H0: The median difference is zero H1: The median difference is not zero

The data are entered into Excel, as shown in Figure 10–18. We analyze the data using the Wilcoxon Signed Rank test. The test statistic is W, the smaller of W+ and W–, which are the sums of the positive and negative ranks, respectively. We first compute difference scores by subtracting the

SBP measured after the exercise program from that measured before the exercise program. The difference scores are shown in Figure 10–19. The next step is to rank the ordered absolute values of the difference scores. First, we generate a column of the absolute values of the difference scores using the ABS

FiguRe 10–18 Data on Matched Pairs for Wilcoxon Signed Rank Test

FiguRe 10–19 Difference Scores

23944_ch10_5976 136 8/3/11 11:47:07 AM

Tests with Matched Samples 137

outlined in Section 10.1. The ranks of the absolute val- ues of the difference scores are shown in Figure 10–21. In the next step, we attach the signs (“+” or “–”) of the observed differences to each rank, as shown in Figure 10–22, using the IF function. Specifically, if the differ- ence (value in column D) is less than zero, the signed rank is equal to –1 ¥ the rank (in column F). If not, the signed rank is equal to the rank in column F. Notice that for patient 2 (row 3) the difference is –2, and thus the signed rank is –2.5.

We now compute the sums of the positive and nega- tive ranks, W+ and W–, respectively, using the SUMIF function. The SUMIF function sums the values in a specified range of cells (e.g., cell G2 through cell G16) that meet a specified criterion. W+ is the sum of the posi- tive ranks, which is computed as SUMIF(G2:G16,“>0”). W– is the sum of the negative ranks, which is computed as ABS(SUMIF(G2:G16,“<0”)). Notice that when we sum the negative ranks, we want to sum the absolute values of the negative ranks, which is done using the ABS (ab- solute value) function. This is shown in Figure 10–23. The test statistic is W, the smaller of W+ and W–. W =

31, as shown in Figure 10–24. The critical value of W is found in Table 7 in the Appen-

dix, based on the sample size (n = 15) and our two-sided level of significance (a = 0.05). The critical value for this two-sided test with n = 15 and a= 0.05 is 25, and the decision rule is as follows: Reject H0 if W ≤ 25. We do not reject H0 because

FiguRe 10–20 Absolute Values of the Difference Scores

FiguRe 10–21 Ranking the Absolute Values of the Difference Scores

(absolute value) function. The absolute values of the difference scores are shown in column E in Figure 10–20.

Next we assign ranks from 1 through n to the smallest through the largest absolute values of the difference scores, respectively, and assign the mean rank when there are ties in the absolute values of the difference scores using the approach

23944_ch10_5976 137 8/3/11 11:47:07 AM

138 Nonparametric Procedures

FiguRe 10–22 Signed Ranks

FiguRe 10–23 Computing W+ and W–

23944_ch10_5976 138 8/3/11 11:47:08 AM

Tests with More Than Two Independent Samples 139

31 > 25. We do not have sta- tistically significant evidence at a = 0.05 to show that the median difference in SBPs is not zero (i.e., that there is a significant difference in SBPs after the exercise pro- gram as compared to before the exercise program).

10.4 tests with moRe than two inDepenDent samples The Kruskal–Wallis test is used to compare medians of a continuous outcome in more than two independent samples with small sample sizes when the outcome is ordinal, ranked, or continu- ous and cannot be assumed to be normally distributed. The Kruskal–Wallis test is used to compare medians among k comparison groups (k > 2) and is sometimes described as an ANOVA with the data replaced by their ranks. The null and research hypotheses for the Kruskal–Wallis nonparametric test are as follows:

H0: The k population medians are equal H1: The k population medians are not equal

The procedure for the test involves pooling the observa- tions from the k samples into one combined sample, keeping track of which sample each observation comes from, and then ranking lowest to highest from 1 to N, where N = n1 + n2 + º + nk.

In Chapter 10 of the textbook, we presented the following formula for the test statistic, H, in the Kruskal–Wallis test: H

= 12 1

1N N R n

( )+ 

 



 

= ∑ – 3(N + 1), where k = the number of

comparison groups, N = the total sample size, nj is the sample size in the jth group, and Rj is the sum of the ranks in the jth group. When performing the test of hypothesis by hand, we computed the test statistic H and found the appropriate critical value in Table 8 in the Appendix to set up the decision rule: Reject H0 if H ≥ Critical Value from Table 8. Excel does not have a specific function for the Kruskal–Wallis test. However, Excel can be used to assign ranks and to compute the test sta-

FiguRe 10–24 Computing the Test Statistic W

table 10–3 Albumin Levels in Three Different Diets

5% Protein 10% Protein 15% Protein

3.1 3.8 4.0 2.6 4.1 5.5 2.9 2.9 5.0

3.4 4.8 4.2

tistic. The conclusion of the test is based on a comparison of the test statistic to the appropriate critical value from Table 8 in the Appendix.

example 10.4. In Example 10.8 of the textbook, we ana- lyzed a clinical study designed to assess differences in albumin levels in adults following different low-protein diets. Three diets were compared, ranging from 5% to 15% protein; the 15% protein diet represents a typical American diet. The albumin levels of participants following each diet are shown in Table 10–3.

23944_ch10_5976 139 8/3/11 11:47:08 AM

140 Nonparametric Procedures

FiguRe 10–25 Data for the Kruskal-Wallis Test

FiguRe 10–26 Ranking the Data in the Pooled Sample

FiguRe 10–27 Summing the Ranks in Each Group

The question of interest is whether there is a difference in albumin levels among the three different diets. The test is run at a 5% level of significance, and the hypotheses are as follows:

H0: The three population medians are equal H1: The three population medians are not equal

The data are entered into Excel, as shown in Figure 10–25.

To conduct the test we assign ranks using the procedures outlined in Section 10.1. This is done on the combined or total sample (i.e., pooling the data from the three compari- son groups (n = 12)), and ranks are assigned from 1 to 12. We also need to keep track of the group assignments in the

total sample. The ranks are shown in Figure 10–26. Notice that the range of the data specified in the RANK function is $A$2 through $C$6 (see the formula in the top menu bar). To compute the test statistic H, we need the sum of the ranks in each group, Rj. These are denoted R1, R2, and R3 and are shown in Figure 10–27.

Recall that the sum of the ranks will always equal n(n + 1) / 2 = 12(13) / 2 = 78. This is shown in Figure 10–28. The test statistic for the Kruskal–Wallis test is denoted H and is defined as follows:

H N N

R n

Nj jj

= +



 



  − +

= ∑12 1 3 1

1( ) ( )

23944_ch10_5976 140 8/3/11 11:47:09 AM

Tests with More Than Two Independent Samples 141

ports the null or research hypothesis. This is done by deter- mining a critical value of H such that if the observed value of H is greater than or equal to the critical value, we reject H0 in favor of H1; and if the observed value of H is less than the critical value, we do not reject H0. The critical value of H can be found in Table 8 in the Appendix. For n1 = 3, n2 = 5, and n3 = 4 and a level of significance of a = 0.05, the critical value is 5.656, thus we reject H0 because 7.52 > 5.656. We have statis- tically significant evidence at a = 0.05 to show that there is a

difference in median albumin levels among the three differ- ent diets.

As we described in Chap- ter 10 of the textbook, if there are three or more comparison groups and five or more obser- vations in each of the compari- son groups, it can be shown that the test statistic H approx- imates a c2 distribution with df = k – 1. Thus, in a Kruskal– Wallis test with three or more comparison groups and five or more observations in each group, it is possible to compute a p-value for the test using the c2 distribution. Specifically, we use Excel to compute the test statistic H and a p-value, and the investigator then compares

FiguRe 10–28 Checking the Sum of the Ranks FiguRe 10–29 Determining Sample Sizes, nj, and Total Sample Size N

To compute the test statistic in Excel, we first compute the sample sizes in each group, nj, using the COUNT function. We also sum the sample sizes to compute the total sample size N. This is shown in Figure 10–29. We now use Rj, nj, and N to compute the test statistic H using Excel. The formula to compute the test statistic H in cell E15 is shown in the top menu bar in Figure 10–30.

Thus, H = 7.52. To draw a final conclusion of the test, we must determine whether the observed test statistic, H, sup-

FiguRe 10–30 Computing the Test Statistic H

23944_ch10_5976 141 8/3/11 11:47:10 AM

142 Nonparametric Procedures

the p-value to the predetermined level of significance to draw a conclusion about the hypotheses using: Reject H0 if p ≤ a. The following example illustrates this situation.

example 10.5. In Example 10.9 of the textbook, we com- pared anaerobic thresholds of elite athletes. The data in Table 10–4 are anaerobic thresholds for distance runners, distance cyclists, distance swimmers, and cross-country skiers. The question of interest is whether there is a difference in anaero- bic thresholds among the different groups of elite athletes. The test is run at a 5% level of significance, and the hypotheses are as follows:

H0: The four population medians are equal H1: The four population medians are not equal

table 10–4 Anaerobic Thresholds

Distance Runners

Distance Cyclists

Distance Swimmers

Cross-Country Skiers

185 190 166 201 179 209 159 195 192 182 170 180 165 178 183 187 174 181 160 215

FiguRe 10–31 Data for the Kruskal– Wallis Test

FiguRe 10–32 Ranking the Data in the Pooled Sample

The data are entered into Excel, as shown in Figure 10–31. To conduct the test we assign ranks using the procedures out- lined in Section 10.1. This is done on the combined or total sample (i.e., pooling the data from the four comparison groups (n = 20)), and ranks are assigned from 1 to 20. We also need to keep track of the group assignments in the total sample. The ranks are shown in Figure 10–32. Notice that the range of the data specified in the RANK function is $A$2 through $D$6 (see the formula in the top menu bar). To compute the test statistic H, we need the sum of the ranks in each group,

23944_ch10_5976 142 8/3/11 11:47:10 AM

Tests with More Than Two Independent Samples 143

We now use Rj, nj, and N to compute the test statistic H using Excel. The formula to compute the test statistic H in cell G15 is shown in the top menu bar in Figure 10–36. Thus, H = 9.11. We can now compute a p-value for the test using the c2 distribution and the CHIDIST function in

FiguRe 10–33 Summing the Ranks in Each Group

FiguRe 10–34 Checking the Sum of the Ranks

Rj. These are denoted R1, R2, R3, and R4 and are shown in Figure 10–33.

Recall that the sum of the ranks will always equal n(n + 1) / 2 = 20(21) / 2 = 210. This is shown in Figure 10–34. The test statistic for the Kruskal–Wallis test is denoted H and is defined as follows:

H N N

R n

Nj jj

= +



 



  − +

= ∑12 1 3 1

1( ) ( )

23944_ch10_5976 143 8/3/11 11:47:11 AM

144 Nonparametric Procedures

FiguRe 10–35 Determining the Sample Sizes, nj, and Total Sample Size N

FiguRe 10–36 Computing the Test Statistic H

Excel. The CHIDIST function requires specification of the test statistic (H in this case) and the degrees of free- dom. The degrees of freedom are defined as k – 1, where k is the number of comparison groups. Computation of the p-value for the test is shown in Figure 10–37. Because

the p-value = 0.0278 is less than the level of significance (a = 0.05), we reject H0. We have statistically significant evidence at a = 0.05 to show that there is a difference in median anaerobic thresholds among the four different groups of elite athletes.

23944_ch10_5976 144 8/3/11 11:47:11 AM

Practice Problems 145

10.5 pRactice pRoblems

1. A company is evaluating the impact of a wellness pro- gram offered on-site as a means of reducing employee sick days. A total of eight employees agree to participate in the evaluation, which lasts 12 weeks. Their sick days in the 12 months prior to the start of the wellness program and again over the 12 months after the completion of the program are recorded and are shown in Table 10–5. Is there a significant reduction in the number of sick days taken after completing the wellness program? Use the Sign test at a 5% level of significance.

2. Using the data in Problem 1, assess whether there is there a significant reduction in the number of sick days taken after completing the wellness program using the Wil- coxon Signed Rank test at a 5% level of significance.

3. A small study (n = 10) is designed to assess whether there is an association between smoking during pregnancy and low birth weight. Low birth weight babies are those born at less than 5.5 pounds. The following data represent the birth weights, in pounds, of babies born to mothers who reported smoking during pregnancy and to those who did not.

FiguRe 10–37 Computing the P-Value Using the Chi-Square Distribution

table 10–5 Data for Practice Problems 1 and 2

Employee

Sick Days Taken in 12 Months

Prior to Program

Sick Days Taken in 12 Months

Following Program

1 8 7 2 6 6 3 4 5 4 12 11 5 10 7 6 8 4 7 6 3 8 2 1

Mother smoked during pregnancy

5.0 4.2 4.8 3.3 3.9

Mother did not smoke during pregnancy

5.1 4.9 5.3 5.4 4.6

23944_ch10_5976 145 8/3/11 11:47:11 AM

146 Nonparametric Procedures

Is there a significant difference in birth weights between mothers who smoked during pregnancy and those who did not? Run the appropriate test at a 5% level of significance.

4. The following data represent the number of playground injuries occurring among children aged 5 to 9 years over a 3-month period in 12 playgrounds in and around the neighborhoods of Boston. Playground injuries include fractures, internal injuries, lacerations, and dislocations. The question of interest is whether there are differences in the numbers of injuries at playgrounds in various lo- cations. The data that follow represent the numbers of injuries recorded at four randomly selected playgrounds located on school properties, at day care centers, and in residential neighborhoods.

School properties 39 51 42 29 Day care centers 28 25 30 15 Residential neighborhoods 18 16 25 22

Run the appropriate test at a 5% level of significance.

5. The recommended daily allowance of vitamin A for chil- dren between ages 1 and 3 years is 400 micrograms (mcg). Vitamin A deficiency is linked to a number of adverse health outcomes, including poor eyesight, susceptibility to infection, and dry skin. The following are vitamin A concentrations in children with and without poor eye- sight, a history of infection, and dry skin.

With poor eyesight, a history of infection, and dry skin

270 420 180 345 390 430

Free of poor eyesight, a history of infection, and dry skin

450 500 395 380 430

Is there a significant difference in vitamin A concentra- tions between children with and without poor eyesight, a history of infection, and dry skin? Run the appropriate test at a 5% level of significance.

6. A study is conducted to assess the potential benefits of an Ayurvedic treatment to reduce high cholesterol. Seven patients (n = 7) agree to participate in the study. Each participant’s cholesterol is measured at the start of the study and then again after 4 weeks of taking a popular herb called Arjuna (Table 10–6). Is there a significant difference in total cholesterol after taking the herb? Use the Sign test at a 5% level of significance.

7. Using the data in Problem 6, assess whether there is there a difference in total cholesterol after taking the herb using the Wilcoxon Signed Rank test at a 5% level of significance.

8. An investigator wants to test if there is a difference in en- dotoxin levels in children who are exposed to endotoxin as a function of their proximity to operating farms. The following data are endotoxin levels in units per milligram of dust sampled from children’s mattresses, organized by the children’s proximity to farms.

Within 5 miles 54 62 78 90 70 5–24.9 miles 28 42 39 81 65 25–49.9 miles 37 29 30 50 53 50 miles or more 36 19 22 28 27

Run the appropriate test at a 5% level of significance.

table 10–6 Data for Practice Problems 6 and 7

Participant Total Cholesterol Before Treatment

Total Cholesterol After Treatment

1 250 241 2 265 260 3 240 253 4 233 230 5 255 224 6 275 227 7 241 232

23944_ch10_5976 146 8/3/11 11:47:12 AM

In Chapter 11 of the textbook we also discussed Cox proportional hazards regression analysis, which is a popular multivariable technique to estimate the effect of several risk factors, considered simultaneously, on survival. Excel does not have the capability to estimate the parameters of the Cox proportional hazards model. Interested readers should see Al- lison1 for details regarding the estimation of parameters in a Cox proportional hazards regression model using SAS.

11.1 Estimating thE survival Function There are several different ways to estimate a survival func- tion or a survival curve. A number of popular parametric methods are used to model survival data, and they differ in terms of the assumptions that are made about the distribu- tion of survival times in the population. In Chapter 11 of the textbook, we focused on two nonparametric methods, the ac- tuarial or life table approach and the Kaplan–Meier approach, which made no assumptions about how the probability that a person develops the event changes over time. The approaches are summarized here, and their implementation is illustrated using Excel Mac 2008.

With the actuarial or life table approach, we first organize the observed follow-up times into equally spaced intervals. We might, for example, consider 1-, 2-, or 5-year intervals depending on the duration of the follow-up. We then sum the number of participants who are at risk at the beginning of each interval, the number who suffer the event of interest, and the number who are censored or lost to follow-up in each interval. We compute the proportions who suffer the event of interest and those who do not in each interval, and then we

In Chapter 11 of the textbook we presented techniques to analyze time to event data, or survival data. Because of the unique features of survival data, most specifically the pres- ence of censoring, special statistical procedures are neces- sary to analyze these data. In survival analysis applications, it is often of interest to estimate the survival function, or survival probabilities, over time. We presented two popular nonparametric techniques called the life table or actuarial table approach and the Kaplan–Meier approach to construct- ing cohort life tables or follow-up life tables. Both approaches generate estimates of the survival function, which can be used to estimate the probability that a participant survives to a specific time (e.g., 5 or 10 years) or the median survival time. Here we use Excel Mac 2008 to program the formulas to estimate survival probabilities and to generate graphical displays of survival functions.

It is also often of interest to assess whether there are sta- tistically significant differences in survival between groups, that is, between competing treatment groups in a clinical trial, between men and women, or between patients with and without a specific risk factor in an observational study. There are many statistical tests available; in Chapter 11 of the textbook we presented the log-rank test, which is a popular nonparametric test to compare survival between two inde- pendent groups. It makes no assumptions about the survival distributions and can be conducted relatively easily using life tables based on the Kaplan–Meier approach. We use Excel to program the computation of the test statistic and to determine a p-value that can be used to assess the statistical significance of the difference in survival between groups.

Chapter 11 Survival Analysis

23944_ch11_5976 147 8/3/11 11:48:25 AM

148 Survival Analysis

the specified criterion (in this case, whether A2 > 0). If the criterion is met, then the value of cell A2 is placed in cell D2; otherwise the value of cell B2 is placed in cell D2. The formula is then copied from cell D2 into cell D3 through cell D21 (see the formula in the top menu bar for the entry in cell D21 in Figure 11–2).

We now create an indicator variable (coded 0 or 1) to in- dicate whether the participant suffered the event of interest (death, in this example) or not. We again use the IF function. If a Year of Death is recorded in column A, we assign a 1; other- wise we assign 0 to indicate that the observed time is censored. The Event variable is created in column E, as shown in Figure 11–3. Using Excel, we now construct the life table using the Time variable and the Event indicator. (It is not necessary to sort the data to construct the table, although the data sorted by Time does facilitate interpretation.)

compute the survival probability. The notation we presented in Chapter 11 of the textbook is summarized as follows:

Nt = number of participants who are event free and con- sidered at risk during interval t

Dt = number of participants who suffer the event of inter- est during interval t

Ct = number of participants who are censored during interval t

Nt* = average number of participants at risk during in- terval t, Nt* = Nt – Ct / 2

qt = proportion suffering the event of interest during interval t, qt = Dt / Nt*

pt = proportion remaining event free during interval t, pt = 1 – qt

St = proportion remaining event free past interval t, St+1 = pt+1 ¥ St, where S0 = 1

With the Kaplan–Meier approach, we do not consider equally spaced intervals; instead we reestimate the survival probability at each observed event time. At each observed time (event time or censored time), we compute the number of participants at risk at that time (Nt), the number of deaths at that time (Dt), the number censored (Ct), and the survival probability (St). The survival probabilities are computed using St+1 = St ¥ ((Nt+1 – Dt+1) / Nt+1). It is important to note that the calculations using the Kaplan–Meier approach are simi- lar to those using the actuarial life table approach. The main difference is the time intervals. With the actuarial life table approach we consider equally spaced intervals, while with the Kaplan–Meier approach we use observed event times and censoring times.

Example 11.1 In Example 11.2 in the textbook, we analyzed a small prospective cohort study with death as the primary outcome. The study involved participants who were 65 years of age and older who were followed for up to 24 years. The study involved 20 participants (n = 20) who were enrolled for a period of 5 years and were followed until death, until the study ended, or until they dropped out of the study (lost to follow-up). The data are shown in Table 11–1.

We use Excel to construct a life table using the actuarial approach. The data are entered into Excel, as shown in Figure 11–1. To construct the life table, we create two new variables. The first is the observed time (either Year of Death or Year of Last Contact). We create the Time variable in column D, and we use the IF function to copy either the Year of Death or the Year of Last Contact from column A or B, respectively, depending on which was measured. Specifically, in cell D2 we enter “=IF(A2>0,A2,B2)”. Recall that the IF function checks

tablE 11–1 Year of Death or Year of Last Contact

Participant Identification Number

Year of Death

Year of Last Contact

1 24

2 3 3 11

4 19

5 24

6 13

7 14

8 2 9 18 10 17 11 24 12 21 13 12 14 1 15 10 16 23 17 6 18 5 19 9 20 17

23944_ch11_5976 148 8/3/11 11:48:25 AM

Estimating the Survival Function 149

FigurE 11–1 Year of Death or Year of Last Contact

FigurE 11–2 Creating the Time Variable

FigurE 11–3 Creating the Event Indicator

To construct the life table, we first organize the follow-up times into equally spaced intervals. In this example we have a maximum follow-up of 24 years, and we consider 5-year intervals (0–4, 5–9, 10–14, 15–19, and 20–24 years). In Excel we now create two new variables that indicate the start and end of each interval. The start and end of the desired intervals are entered into columns G and H, respectively, as shown in Figure 11–4.

Next, we sum the number of participants who are alive at the beginning of each interval (Nt), the number who die (Dt), and the number who are censored (Ct) in each interval. To com- pute these sums, we use the COUNTIF function. For example, to compute the number of participants alive at the beginning of each interval, we specify COUNTIF(D2:D21,“>=”start of interval), where D2:D21 refers to the range of the observed times, and start of interval refers to the value in cell G2 through cell G6. We illustrate the computation of Nt for each interval in Figure 11–5.

Note that in cell I6 we specify “=COUNTIF (D$2:D$21,“ > = ”&G6)”. We use absolute cell references to indicate the data range (i.e., D$2:D$21) so that when the formula is copied from cell I2 to cell I3 through cell I6 we continue to count the same data. The second argument to the COUNTIF func- tion is the criterion we wish Excel to consider. Specifically, for each interval we want to count all Times that are greater

23944_ch11_5976 149 8/3/11 11:48:25 AM

150 Survival Analysis

than or equal to the start of the interval. This is indicated by “>=”&G6 in row 6 of Figure 11–5. To consider a cell reference in the criterion of the COUNTIF function, we include “&” before the cell address.

We now sum the number of participants who die (Dt) in each interval. We again use the COUNTIF function. However, we sum the num- ber of participants with Year of Death in each interval as follows. For interval 1 (0–4 years), we sum the number of deaths that occur between 0 and 4 years of follow-up (i.e, if 0 ≤ Year of Death ≤ 4). To implement two criteria (Year of Death greater than or equal to 0 years, and Year of Death less than or equal to 4 years), we first sum the number of participants with Year of Death greater than or equal to 0 years and then subtract the number of participants with Year of Death greater than 4 years. Specifically, in cell J2 we enter “=COUNTIF(A$2:A$21,“>=”&G2)- COUNTIF(A$2:A$21,“>”&H2)”. We then copy this formula from cell J2 into cell J3 through J6, as shown in Figure 11–6.

We use the same approach to sum the number of participants who are censored (Ct)

in each interval. We again use the COUNTIF function. However, we sum the number of participants with Year of Last Contact in each inter- val as follows. For interval 1 (0–4 years), we sum the number of par- ticipants with Year of Last Contact between 0 and 4 years of follow-up (i.e, if 0 ≤ Year of Last Contact ≤ 4). Specifically, in cell K2 we enter “=COUNTIF(B$2:B$21,“>=”&G2)- COUNTIF(B$2:B$21,“>”&H2)”. We then copy this formula from cell K2 into cell K3 through cell K6, as shown in Figure 11–7.

We now compute Nt*, the aver- age number of participants at risk in each interval, using Nt* = Nt – Ct / 2. This is shown in Figure 11–8. Next we compute the proportion who suf- fer the event of interest in each inter- val (in this example, the proportion who die), qt = Dt / Nt*. This is shown in Figure 11–9. Next we compute the

FigurE 11–4 Entering the Start and End of Intervals for the Life Table

FigurE 11–5 Computing the Number at Risk, Nt

23944_ch11_5976 150 8/3/11 11:48:26 AM

Estimating the Survival Function 151

FigurE 11–6 Computing Number of Events (Deaths), Dt

FigurE 11–7 Computing the Number Censored, Ct

23944_ch11_5976 151 8/3/11 11:48:26 AM

152 Survival Analysis

FigurE 11–8 Computing the Average Number at Risk, Nt*

FigurE 11–9 Computing the Proportion who Suffer Event (Death), qt

23944_ch11_5976 152 8/3/11 11:48:27 AM

Estimating the Survival Function 153

FigurE 11–10 Computing Proportion who Remain Event Free, pt

FigurE 11–11 Computing the Survival Probabilities, St

proportion who remain event free in each interval, pt = 1 – qt. This is shown in Figure 11–10.

The final step is to compute the survival probabilities, St, using St+1 = pt+1 ¥ St. Recall that S0 = 1, and thus we compute

the survival probability for the first interval by specifying “=N2*1” in cell O2. The survival probabilities for the subse- quent intervals use the formula St+1 = pt+1 ¥ St, as shown in Figure 11–11. Although it is tedious to construct the life table

23944_ch11_5976 153 8/3/11 11:48:27 AM

154 Survival Analysis

study (lost to follow-up). The data are shown in Table 11–2, and follow-up times are measured in years.

We use Excel to construct a life table using the actuarial approach and the template we developed for Example 11.1. We first copy the worksheet with all of the formulas we developed for Example 11.1 and enter the data from Table 11–2 into the worksheet, as shown in Figure 11–12. Notice that we copied the formulas from cells D21 and E21 into cells D22 and E22 through cells D31 and E31 (Figure 11–3) to accommodate the larger sample size. Because the follow-up times extend to 34 years, we need to add two additional time intervals to our life table. Specifically, we add 25–29 and 30–34 years to cells G7 to H7 and G8 to H8, respectively. In addition, we must update the data range from 21 rows to 31 rows for the computations of Nt, Dt, and Ct in columns I, J, and K, respectively (Figure 11–5 through Figure 11–7). Finally, we copy the formulas to compute Nt*, qt, pt, and St in cell L6 through cell O6 to cell L7

the first time, after the structure is entered into Excel, the construction of life tables for new datasets is relatively easy. Only the data range in the formulas must be modified.

Example 11.2 Consider a prospective cohort study where 30 participants (n = 30) are enrolled and followed until time of death, until the study ends, or until they drop out of the

tablE 11–2 Year of Death or Year of Last Contact

Participant Identification Number

Year of Death

Year of Last Contact

1 19 2 21 3 6 4 34 5 7 6 23 7 12 8 18 9 11 10 23 11 12 12 28 13 29 14 32 15 17 16 19 17 21 18 29 19 19 20 30 21 30 22 16 23 21 24 21 25 28 26 18 27 3 28 24 29 9 30 27

FigurE 11–12 Data for Life Table Using Actuarial Approach

23944_ch11_5976 154 8/3/11 11:48:27 AM

Estimating the Survival Function 155

When we select the Sort option, Excel presents the dialog box shown in Figure 11–15. We specify that we would like to sort the data by Time in ascending order (i.e., from smallest to largest). We also wish to sort the data by the Event vari- able, and we specify that after sorting the data by Time (in ascending order) that we then wish to sort the data by the variable Event in descending order (i.e., from largest to small- est). Notice that we also indicate that there is a header row in the dataset (option at bottom left corner of the dialog box). When we indicate that there is a header row, Excel unselects the first row. When we click OK, Excel produces the data shown in Figure 11–16.

Using the sorted data, in particular the Time and Event variables, we now construct the life table using the Kaplan– Meier approach. Specifically, we compute the number at risk, Nt, the number of deaths, Dt, the number censored, Ct, and the survival probability, St, for each time. First, we compute the number at risk, Nt, using the COUNTIF func- tion. Specifically, for each Time, we count the number of participants whose observed time is greater than or equal to

through cell O8. The life table for the data in Example 11.2 is shown in Figure 11–13. We now illustrate the Kaplan–Meier approach to estimate survival probabilities using Excel.

Example 11.3 Consider again Example 11.1 (Example 11.2 in the textbook), where we analyzed a small prospective cohort study with death as the primary outcome. The study involved 20 participants (n = 20) who were 65 years of age and older and were followed for up to 24 years until they died, until the study ended, or until they dropped out of the study (lost to follow-up). The data are shown in Table 11–1.

We now use Excel to construct a life table using the Ka- plan–Meier approach. The data were entered into Excel, as shown in Figure 11–1, and the two new variables, Time and an indicator of event status (Event), were created, as shown in Figure 11–2 and Figure 11–3, respectively. The first step is to sort the data by the Time variable (column D). This is done by highlighting the data (column A through column E) and choosing the Sort option under Data on the top menu bar, as shown in Figure 11–14.

FigurE 11–13 The Life Table Using Actuarial Approach

23944_ch11_5976 155 8/3/11 11:48:28 AM

156 Survival Analysis

the current time. For example, at time 1 (1 year), there are 20 participants at risk (i.e., 20 participants who survive 1 year or longer). To compute the number at risk at the first observed time, we enter the following into cell G2: “=COUNTIF(D$2:D$21,“>=”&D2)”. We then copy this formula from cell G2 into cell G3 through cell G21, as shown in Figure 11–17.

Next we compute the number of events (deaths) at each time, Dt. This is done using the IF function. Specifically, at each Time, if the event (column E) occurs (Event = 1), then we count that event. If the event does not occur (Event = 0), we do not count it. In cell H2 we enter “=IF(E2=1,1,0)”. Recall that the IF function checks the specified criterion (in this case, whether E2 = 1). If the criterion is met, then “1” is placed in cell H2; otherwise “0” is placed in cell H2. This is shown in Fig- ure 11–18, and the formula is copied from cell H2 into cell H3 through cell H21.

Next we compute the number of participants who are censored at each time, Ct. This is again done using the IF function. Specifically, at each Time, if the Event (column E) is coded 0 (cen- sored), then we count that event as cen- sored. For example, in cell I2 we enter “=IF(E2=0,1,0)”. This is shown in Figure 11–19, and the formula is copied from cell I2 into cell I3 through cell I21. The final step is to compute the survival probabilities using St+1 = St ¥ ((Nt+1 – Dt+1) / Nt+1).

Before computing the survival probabilities at each time, we first in- sert a row to represent the start of the study (i.e., baseline or Time = 0) at which Time the Survival Probability is 1. To insert a row before row 2, we place the cursor anywhere in row 2 and right click. This brings up the options shown in Fig- ure 11–20. We select the Insert option, which brings up the dialog box shown in Figure 11–21. We select Entire row and click OK. In cell D2 we enter “0” (i.e., Time = 0), and in cell J2 we enter “1” (i.e., Survival Probability = 1, S0 = 1).

FigurE 11–14 Sorting the Data by Time and Event

FigurE 11–15 Sorting the Data by Time in Ascending Order and by Event in Descending Order

23944_ch11_5976 156 8/3/11 11:48:28 AM

Estimating the Survival Function 157

FigurE 11–16 Sorted Data FigurE 11–17 Computing the Number at Risk, Nt

FigurE 11–18 Computing the Number of Events (Deaths), Dt

23944_ch11_5976 157 8/3/11 11:48:29 AM

158 Survival Analysis

FigurE 11–19 Computing the Number Censored, Ct

FigurE 11–20 Inserting a Row

23944_ch11_5976 158 8/3/11 11:48:29 AM

Estimating the Survival Function 159

FigurE 11–21 Inserting a Row

The Survival Probabilities for each subsequent time are then computed using the formula St+1 = St ¥ ((Nt+1 – Dt+1) / Nt+1), as shown in Figure 11–22 (see the formula in the top menu bar). Notice that the survival probabilities change only when there are observed events (in this example, deaths). Censored times do not affect the estimates of the survival probabilities.

Again, although it is tedious to construct the life table using the Kaplan–Meier approach the first time, after the structure is entered into Excel, the construction of life tables for new datasets is relatively easy. The new data need to be entered and sorted, and only the data range in the formulas need to be modified.

In Chapter 11 of the textbook, we also computed stan- dard errors of the survival estimates. There are several for- mulas to produce standard errors; we discussed Greenwood’s

formula, SE(St) = S D

N N Dt t

t t t

∑ −( )

, where the quantity

D N N D

t t t( )− is summed for numbers at risk (Nt) and

numbers of deaths (Dt) occurring through the time of interest (i.e., cumulative, across all times before the time of interest).

In Example 11.4 we use Excel to compute standard errors for the survival estimates for the data in Example 11.3. We also produce 95% confidence intervals for the survival probabili- ties using St ± 1.96 ¥ SE(St).

Example 11.4. Consider again Example 11.3, where we ana- lyzed a small prospective cohort study with death as the pri- mary outcome. The study involved 20 participants who were 65 years of age and older and were followed for up to 24 years until they died, until the study ended, or until they dropped out of the study (i.e., were lost to follow-up). Using Excel we estimated the survival function as shown in Figure 11–22. We now use Excel to compute standard errors and 95% confidence limits for the estimates of the survival probabilities.

Before we begin to add the computations, we first hide column A through column C of the spreadsheet to allow for better visualization of the additional columns needed for the computations of the standard errors and confidence limits. This is done by highlighting the columns we wish to hide (col- umn A though column C). We then right click, which brings up the options shown in Figure 11–23. From the list of options, we select Hide. We now create the quantity Dt / Nt(Nt – Dt) at

23944_ch11_5976 159 8/3/11 11:48:30 AM

160 Survival Analysis

FigurE 11–23 Hiding Columns

FigurE 11–22 Computing the Survival Probabilities, St

23944_ch11_5976 160 8/3/11 11:48:30 AM

Estimating the Survival Function 161

each time, as shown in Figure 11–24 (see the formula in cell K22 that appears in the top menu bar).

Next we sum the quantities D

N N D t

t t t( )− in

column K through each time of interest (i.e., cumula- tive, across all times before the time of interest). This is done using the SUM function. For example, in cell L3 we enter “=K3”. In cell L4 we enter “=K4+L3”. We then copy the formula in cell L4 into cell L5 through cell L22, as shown in Figure 11–25.

The next step is to compute the standard error

for each time SE(St) = S D

N N Dt t

t t t

∑ −( )

. Speci-

fically, we multiply the Survival Probability (column J) by the square root of the sum of the quantities

D N N D

t t t( )− (column L) to produce the standard

errors at each time. This is shown in Figure 11–26. The next step is to compute the margins of error

for the 95% confidence intervals using 1.96 ¥ SE(St). This is shown in Figure 11–27. The last step is to produce the 95% confidence limits using the formula point estimate ± margin of error. In this case, we use

FigurE 11–24 Computing the Standard Errors

FigurE 11–25 Computing the Standard Errors

23944_ch11_5976 161 8/3/11 11:48:32 AM

162 Survival Analysis

FigurE 11–26 Computing the Standard Errors

FigurE 11–27 Computing the Margins of Error

23944_ch11_5976 162 8/3/11 11:48:32 AM

Plotting a Survival Function 163

Excel we estimated the survival function, as shown in Figure 11–22. We now use Excel to create a graphical display of the survival function.

In Chapter 11 of the textbook, we presented graphical displays of survival functions, which we produced in Excel and showed time along the X-axis and the survival probabilities along the Y-axis. In order to produce the graphical displays, which take the form of step functions, some manipulation of the data is required. Here we detail the steps needed to pro- duce the displays.

The data for the graphical display are shown in Figure 11–29. The key data elements are Time (column D) and Sur- vival Probability (column J). Notice that the observed data (entered in column A and column B), as well as a blank col- umn C, are hidden.

Excel has a number of built-in graphical displays that are available on the Charts tab (see Chapter 3 of this workbook for examples). We use these built-in displays to produce the display of the survival function. However, we first need to do some formatting of the data. To illustrate why the formatting is needed, consider the following. To produce a display of the Survival Probabilities by Time, we first highlight the columns

St ± 1.96 ¥ SE(St). In Figure 11–28 we create two new variables representing the lower and upper limits of the 95% confidence interval, respectively. The lower limit is computed by sub- tracting the margin of error (column N) from the Survival Probability (column J), and the upper limit is computed by adding the margin of error (column N) to the survival prob- ability (column J). The 95% confidence limits are computed in Figure 11–28.

11.2 Plotting a survival Function A graphical display of the survival function is a very useful means of reporting or presenting survival information. A graphical display of the Kaplan–Meier survival curve can be produced from the life table we created using Excel.

Example 11.5 Consider again Example 11.3 where we ana- lyzed a small prospective cohort study with death as the pri- mary outcome. The study involved 20 participants (n = 20) who were 65 years of age or older and were followed for up to 24 years until they died, until the study ended, or until they dropped out of the study (i.e., were lost to follow-up). Using

FigurE 11–28 95% Confidence Limits for the Survival Probabilities

23944_ch11_5976 163 8/3/11 11:48:32 AM

164 Survival Analysis

containing the data for the display. Specifically, we highlight column D (Time) and column J (Survival Probability), and we select XY (Scatter) from the Charts tab. This is shown in Figure 11–30.

When we select XY (Scatter), Excel offers vari- ous options for scatter plots. The survival function is displayed with a specific plot type, specifically the fourth option, with straight lines and markers (Fig- ure 11–31). When we click this specific scatter plot type, Excel then produces a preliminary version of the display (Figure 11–31).

We make a number of formatting changes in the display prior to presenting it. First, notice that the display is not a step function. The survival curve connects observed changes in survival over time using diagonal lines (i.e., using interpolation). We do not wish to interpolate; instead we want to show steps, that is, vertical lines from an observed survival probability connecting to a horizontal line at the next observed survival probability, taking an L shape at each transition point.

In order to produce a step function, we must format the data before selecting the XY (Scatter)

FigurE 11–29 Data for the Graphical Display

FigurE 11–30 Creating A Scatter Plot

23944_ch11_5976 164 8/3/11 11:48:33 AM

Plotting a Survival Function 165

option as follows. Using the data shown in Figure 11–29, we first replace the formulas that we used to compute the survival probabilities with their calculated values. We do this so that in the process of formatting the data to gener- ate the plot we do not inadvertently change the estimates of the Survival Probabilities. Specifically, we want to replace the formulas currently in cell G2 through J22 with their computed values. This is done by highlighting these cells and clicking the Copy icon on the top menu bar. This gen- erates a f lashing dashed line around the highlighted cells. We then click the Paste icon, and then on the arrow to the right of the Paste icon we select the Values Only option, as shown in Figure 11–32.

The next step is to format the data so that at each transi- tion point (i.e., change in Survival Probability), the graphical display shows a step. This is done by inserting a new row be- tween each transition point (which is equivalent to inserting

FigurE 11–31 Preliminary Display

a row before each observed event, death, in the dataset). This is done by placing the cursor anywhere in a row and right clicking, which brings up the options shown in Figure 11–33. We select the Insert option, which brings up the dialog box shown in Figure 11–34. We select Insert and then Entire row and click OK. The resulting worksheet after inserting rows at each transition point is shown in Figure 11–35.

The next step involves inserting data (Times and Survival Probabilities) into the newly inserted rows. Specifically, in each newly inserted (blank) row, we copy the Time from the cell above and the Survival Probability from the cell below. For example, in cell D3 we enter “=D2” (the Time from cell D2), and in cell J3 we enter “=J4” (the Survival Probability from cell J4). The same procedure is followed for rows 6, 8, 16, 18, and 24. For example, in cell D24 we enter “=D23”, and in cell J24 we enter “=J25”. The updated worksheet is shown in Figure 11–36.

23944_ch11_5976 165 8/3/11 11:48:33 AM

166 Survival Analysis

FigurE 11–32 Replacing Formulas with Their Calculated Values

FigurE 11–33 Inserting a Row

23944_ch11_5976 166 8/3/11 11:48:34 AM

Plotting a Survival Function 167

FigurE 11–34 Inserting a Row

FigurE 11–35 Inserting Rows at Each Transition Point FigurE 11–36 Data to Create a Step Function

23944_ch11_5976 167 8/3/11 11:48:35 AM

168 Survival Analysis

FigurE 11–37 The Display

Using the updated worksheet, we follow the same steps illustrated in Figure 11–30 and Figure 11–31, that is, we high- light the data for the display in column D (Time) and column J (Survival probability) and select the XY (Scatter) option in the Charts tab on the top menu bar. Following the previously outlined steps, we produce the display shown in Figure 11–37. For presentation purposes, we need to format the plot. For example, Excel automatically scales the X- and Y-axes from 0 to a value larger than the maximum value in the dataset. First, we rescale the Y-axis to accommodate the range of the data. Specifically, we rescale the Y-axis to a maximum of 1.0. This is done by right clicking the Y-axis, which brings up the options shown in Figure 11–38.

Selecting Format Axis brings up the dialog box shown in Figure 11–39. We select the Scale option and input the desired maximum (1.0) as shown in Figure 11–39. We click OK to pro- duce the display shown in Figure 11–40. We can also insert a more appropriate title by clicking the default title (“Survival probability St”) at the top and retyping the desired title. Next, we remove the legend by clicking the legend box and pressing the Delete key. We do the same to the horizontal lines in the display. If we click any of the lines and press the Delete key, all of the horizontal lines are removed. The formatted display plot is shown in Figure 11–41.

We next want to insert a title for the X-axis. This is done by clicking the Toolbox icon on the top menu bar, which

23944_ch11_5976 168 8/3/11 11:48:35 AM

Plotting a Survival Function 169

FigurE 11–38 Formatting the Y-Axis

23944_ch11_5976 169 8/3/11 11:48:35 AM

170 Survival Analysis

FigurE 11–39 Rescaling the Y-Axis

FigurE 11–40 The Display

23944_ch11_5976 170 8/3/11 11:48:36 AM

Plotting a Survival Function 171

FigurE 11–41 The Display

FigurE 11–42 Inserting Horizontal Axis Title

23944_ch11_5976 171 8/3/11 11:48:37 AM

172 Survival Analysis

opens the Formatting Palette (Figure 11–42). Under Chart Options, we select Horizontal (Category) Axis and enter the desired title.

We then select Vertical (Value) Axis and enter the de- sired title for the Y-axis, as shown in Figure 11–43. The for- matted scatter diagram can now be copied from Excel into a manuscript, report, or presentation. The final display is shown in Figure 11–44. In the survival curve shown in Figure 11–44, the symbols represent each event time, either a death or a censored time.

Sometimes it is of interest to plot the estimates of the survival probabilities (as shown in Figure 11–44) along with 95% confidence limits. This is done using exactly the same

FigurE 11–43 Inserting Vertical Axis Title

approach as that used to plot the survival function. Specifi- cally, we first generate the estimates of the survival probabili- ties and the 95% confidence limits, as shown in Example 11.4 and Figure 11–28. We then format the data to produce the step functions (in this case for the estimates as well as the upper and lower limits of the confidence interval). We illustrate the approach in Example 11.6.

Example 11.6 Consider again the data in Example 11.4 where we generated estimates of survival probabilities and 95% confidence limits. The results of the computations are shown in Figure 11–28. To facilitate interpretation, we hide the columns that are not directly related to the graphical

23944_ch11_5976 172 8/3/11 11:48:37 AM

Plotting a Survival Function 173

FigurE 11–44 Formatted Display

display. We then replace the formulas in the columns that still appear with their calculated values. This is done by first highlighting the cells (columns D, J, O, and P in Figure 11–45), clicking the Copy icon on the top menu bar, clicking the Paste icon, and then clicking the arrow to the right of the Paste icon and selecting the Values Only option (Figure 11–32).

Next we insert rows at each transition in Survival Prob- ability (to produce a step function), as shown in Figure 11–45. Because survival probabilities are between 0 and 1, inclusive, we also recode any estimates to that range. Specifically, we recode the estimates of the upper limits from above 1.0 to 1.0 as needed. Next, in each newly inserted (blank) row, we copy the Time from the cell above and the Survival Probability, Lower, and Upper limits of the confidence interval from the cells below. For example, in cell D3 we enter “=D2” (the Time from cell D2); in cells J3, O3, and P3 we enter “=J4”, “=O4”, and “=P4”, respectively. The same procedure is followed for

rows 6, 8, 16, 18, and 24. For example, in cell D24 we enter “=D23”; in cells J24, O24, and P24 we enter “=J25”, “=O25”, and “=P25”, respectively. The updated worksheet is shown in Figure 11–46.

We again use Excel’s built-in graphical displays under the Charts tab to create the display. The easiest way to produce the display is to highlight the data shown in Figure 11–46 (spe- cifically, the data for the X-axis in column D and the data for the Y-axis in column J, column O, and column P). With the data highlighted, we select XY (Scatter) on the Charts tab, as shown in Figure 11–47. We then select the fourth plot type option, specifically the plot with straight lines and markers.

When we select the specific scatter plot type, Excel pro- duces a preliminary version of the display (Figure 11–48). For presentation purposes, we need to format the plot. For example, Excel automatically scales the X- and Y-axes from 0 to a value larger than the maximum value in the dataset. First, we rescale the Y-axis to a maximum of 1.0. This is done by

23944_ch11_5976 173 8/3/11 11:48:37 AM

174 Survival Analysis

FigurE 11–46 Data to Display Survival Probabilities and 95% Confidence Limits

FigurE 11–45 Preparing Data for Display

23944_ch11_5976 174 8/3/11 11:48:38 AM

Plotting a Survival Function 175

FigurE 11–47 Selecting the Chart Type

23944_ch11_5976 175 8/3/11 11:48:38 AM

176 Survival Analysis

FigurE 11–48 Preliminary Display

right clicking the Y-axis, which brings up the options shown in Figure 11–49. Selecting Format Axis brings up the dialog box shown in Figure 11–50, where we select the Scale option and input the desired maximum (1.0). Clicking OK produces the display shown in Figure 11–51.

We remove the legend by clicking the legend and pressing the Delete key. We do the same to the horizontal lines in the display. If we click any of the lines and press the Delete key, all of the horizontal lines are removed. We also want to add a title to the display. This is done by opening the Formatting Palette by clicking the display and then clicking the Toolbox icon on the top menu bar. Under Chart Options, we select Chart Title and enter the desired title, as shown in Figure 11–52. We can then select Horizontal (Category) Axis under

Chart Options and enter a title for the X-axis, as shown in Figure 11–53.

We then select Vertical (Value) Axis under Chart Op- tions and enter a title for the Y-axis, as shown in Figure 11–54. An additional formatting step is needed to change the colors of the lines (the estimated survival probability, the lower and upper limits of the 95% confidence interval) to black. This is done by clicking any part of the line (one at a time). For example, suppose we click the estimated survival probability line (the middle line shown in Figure 11–54). Under the Colors, Weights, and Fills option of the Formatting Palette, we select the desired color for the line from the list of Theme Colors (black in this case), as shown in Figure 11–55.

23944_ch11_5976 176 8/3/11 11:48:38 AM

Plotting a Survival Function 177

FigurE 11–49 Formatting the Y-Axis

23944_ch11_5976 177 8/3/11 11:48:39 AM

178 Survival Analysis

FigurE 11–50 Rescaling the Y-Axis

23944_ch11_5976 178 8/3/11 11:48:39 AM

Plotting a Survival Function 179

FigurE 11–51 The Display

23944_ch11_5976 179 8/3/11 11:48:40 AM

180 Survival Analysis

FigurE 11–52 Inserting a Title

23944_ch11_5976 180 8/3/11 11:48:40 AM

Plotting a Survival Function 181

FigurE 11–53 Inserting Horizontal Axis Title

23944_ch11_5976 181 8/3/11 11:48:40 AM

182 Survival Analysis

FigurE 11–54 Inserting Vertical Axis Title

23944_ch11_5976 182 8/3/11 11:48:41 AM

Plotting a Survival Function 183

FigurE 11–55 Changing the Line Color

23944_ch11_5976 183 8/3/11 11:48:41 AM

184 Survival Analysis

FigurE 11–56 Changing the Marker Color

We then change the color of the markers to black by se- lecting the desired marker fill color from the list of Theme Colors (black in this case), as shown in Figure 11–56. We fol- low the same procedure to change the upper and lower limits of the 95% confidence interval to black, but in addition we change the lines from solid to dashed by choosing one of the Line options under Dashed, as shown in Figure 11–57.

We also select triangles as markers for both the upper and lower limits of the 95% confidence interval. This involves changing the default marker (squares) selected for the lower 95% confidence limit. The marker style can be changed by

highlighting any one of the markers along the lower 95% con- fidence limit line and right clicking. This brings up the options shown in Figure 11–58. Selecting Format Data Series and then Marker Style brings up the dialog box shown in Figure 11–58. From the list of marker styles, we select the triangle so that the upper and lower 95% confidence limits are displayed with the same markers. The final formatted display is shown in Figure 11–59. The display can now be easily copied into papers, reports, or other presentations. In the survival curve shown in Figure 11–59, the markers or symbols represent each event time, either a death or a censored time.

23944_ch11_5976 184 8/3/11 11:48:42 AM

Plotting a Survival Function 185

FigurE 11–57 Changing the Line Style

23944_ch11_5976 185 8/3/11 11:48:42 AM

186 Survival Analysis

FigurE 11–58 Changing the Marker Style

23944_ch11_5976 186 8/3/11 11:48:43 AM

Plotting a Survival Function 187

FigurE 11–59 Formatted Display

23944_ch11_5976 187 8/3/11 11:48:43 AM

188 Survival Analysis

Example 11.7 In Example 11.3 in the textbook we analyzed a small clinical trial comparing two combination treatments in patients with advanced gastric cancer. Twenty participants (n = 20) with stage IV gastric cancer who consented to par- ticipate in the trial were randomly assigned to receive chemo- therapy before surgery or after surgery. The primary outcome was death, and participants were followed for up to 48 months (4 years) following enrollment in the trial. The experiences of participants in each arm of the trial are shown in Table 11–3. The question of interest is whether there is a difference in survival between the two treatments. The test is run at a 5% level of significance, and the hypotheses are as follows:

H0: The two survival curves are identical (or S1t = S2t) H1: The two survival curves are not identical (or S1t ≠ S2t,

at any time t)

The data are entered into Excel as shown in Figure 11–60.

To prepare the data for the computations, we now create two new variables, Event Times and Group. The Event Times are the times of the observed events (deaths, in this example). Group indicates whether the events occurred in group 1 or group 2. The two new variables are shown in Figure 11–61. Notice that the Event Times are not sorted; they are simply copied from column A and column D.

The next step is to sort the data. Specifically, we sort the data by the two new variables, first by Event Times in ascending order and then by Group in ascending order. This is done by highlighting the data (column A through column H) and selecting the Sort option under Data on the top menu bar. Selecting this option brings up the dialog box shown in Figure 11–62. In the dialog box, we specify that we want the

11.3 comParing survival curvEs In many survival analysis applications, we are interested in assessing whether there are differences in survival among dif- ferent groups of participants. For example, in a clinical trial with a survival outcome, we are often interested in compar- ing survival between participants receiving the new drug as compared to the placebo (or other appropriate comparator). In an observational study, we might be interested in comparing survival between men and women or between participants with and without a particular risk factor (e.g., hypertension or diabetes).

Several tests are available to compare survival among in- dependent groups. In Chapter 11 of the textbook we presented the log-rank test, which is a popular test to compare survival between two or more independent groups. The test compares the entire survival experience between groups and can be thought of as a test of whether the survival curves are iden- tical (overlapping) or not. Survival curves are estimated for each group, considered separately using the Kaplan–Meier ap- proach, and are compared statistically using the log-rank test. The log-rank test is computed using the five-step approach for hypothesis testing. The test statistic for the log-rank test is

as follows: c2 = ( )

( ) Σ Σ

Σ O E

E jt jt

− ∑

, where ΣOjt represents the

sum of the observed number of events in the jth group (e.g., j = 1, 2) and ΣEjt represents the sum of the expected number of events in the jth group over time and is approximately distributed as a chi-square test statistic. Using Excel we can compute the value of the test statistic and compute a p-value using the CHIDIST function. The log-rank test statistic has degrees of freedom equal to k – 1, where k represents the number of comparison groups.

tablE 11–3 Month of Death or Month of Last Contact in Each Treatment Group

Chemotherapy Before Surgery Chemotherapy After Surgery

Month of Death Month of Last Contact Month of Death Month of Last Contact

8 8 33 48 12 32 28 48 26 20 41 25 14 40 37 21 48 27 25

23944_ch11_5976 188 8/3/11 11:48:43 AM

Comparing Survival Curves 189

FigurE 11–60 Data for the Log Rank Test

FigurE 11–61 Creating New Variables

FigurE 11–62 Sorting the Data

23944_ch11_5976 189 8/3/11 11:48:44 AM

190 Survival Analysis

participants in group 1 who are at risk at each event time, N1t, we count the numbers of participants in group 1 with observed times (either Months of Death or Months of Last Contact) that are greater than or equal to each observed Event Time in column G. Specifically, in cell J2 we enter “=COUNTIF(A$2:B$7,“>=”&G2)”. Notice that we specify the range of the data for group 1 as cell A$2 through cell B$7 using absolute cell references so that we continue to refer to the same data range when we copy the formula from cell J2 to cell J3 through cell J10. The numbers of participants in group 1 at risk at each Event Time are shown in Figure 11–64.

We use the same approach to compute the numbers of participants in group 2 who are at risk at each event time, N2t. We count the numbers of participants in group 2 with observed times (either Months of Death or Months of Last Contact) that are greater than or equal to each observed Event Time in column G. Specifically, in cell K2 we enter “=COUNTIF(D$2:E$8,“>=”&G2)”. Again, we specify the range of the data for group 2 as cell D$2 through cell E$8 using absolute cell references so that we continue to refer to the same data range when we copy the formula from cell K2 to cell K3 through cell K10. The numbers of participants in group 2 at risk at each Event Time are shown in Figure 11–65.

In the next step, we compute the numbers of observed events in each group, O1t and O2t, respectively. These are computed in columns L and M using the IF function. Spe- cifically, in column L we compute the numbers of observed events in group 1 by specifying “=IF(H2=1,1,0)” in cell L2 and

copying this formula into cell L3 through cell L10. Similarly, in column M we compute the numbers of observed events in group 2 by specifying “=IF(H2=2,1,0)” in cell M2 and copying this formula into cell M3 through cell M10. The numbers of observed events in each group are shown in Figure 11–66.

Next, we compute the total number of participants at risk at each event time, Nt, by summing the numbers of participants at risk across groups (i.e., summing N1t and N2t) at each event time. This is shown in Figure 11–67. Next, we compute the total number of observed events at each event time, Ot, by summing the numbers of observed events across groups (i.e., summing O1t and O2t) at each event time. This is shown in Figure 11–68.

Next we compute the expected number of events in each group, E1t and E2t, at each

data sorted by Event Times from smallest to largest (i.e., in ascending order). We also wish to sort the data by Group from smallest to largest (in ascending order). Notice that we also indicate that there is a header row in the dataset (option at top left corner of the dialog box). When we click OK, Excel produces the data shown in Figure 11–63.

Next, we want to compute the numbers of participants at risk in each group at each observed event time. To do this, we use the COUNTIF function. To compute the numbers of

FigurE 11–63 Sorted Data

FigurE 11–64 Computing the Number of Participants in Group 1 at Risk at Each Event Time

23944_ch11_5976 190 8/3/11 11:48:44 AM

Comparing Survival Curves 191

FigurE 11–65 Computing the Number of Participants in Group 2 at Risk at Each Event Time

FigurE 11–66 Computing the Number of Observed Events in Each Group at Each Event Time

event time using E1t = N1t ¥ (Ot / Nt) for group 1 and E2t = N2t ¥ (Ot / Nt) for group 2. Specifically, in column P we com- pute the expected number of events in group 1 by entering “=J2*(O2/N2)” in cell P2 and copying this formula into cell P3 through cell P10. This is shown in Figure 11–69. We do the same for group 2 by entering “=K2*(02/N2)” in cell Q2 and copying this formula into cell Q3 through cell Q10. The

final step is to compute the test statistic. However, to do so we need the total number of observed events in each group (i.e., the sums of O1t and O2t, ΣO1t and ΣO2t, respectively) and the total number of expected events in each group (i.e., the sums of E1t and E2t, ΣE1t and ΣE2t, respectively). These are computed using the SUM function, as shown in Figure 11–70.

23944_ch11_5976 191 8/3/11 11:48:45 AM

192 Survival Analysis

FigurE 11–67 Computing the Total Number of Participants at Risk at Each Event Time

FigurE 11–68 Computing the Total Number of Observed Events at Each Event Time

23944_ch11_5976 192 8/3/11 11:48:45 AM

Comparing Survival Curves 193

FigurE 11–69 Computing the Number of Expected Events in Group 1 at Each Event Time

FigurE 11–70 Computing the Total Numbers of Observed and Expected Events in Each Group

The next step is to compute the log-rank test statistic: c2

= ( )

( ) Σ Σ

Σ O E

E jt jt

− ∑

, where ΣOjt represents the sum of the

observed number of events in the jth group (e.g., j = 1, 2), and ΣEjt represents the sum of the expected number of events in the jth group over time. We compute the test statistic in two steps. First, we take the ratio of the square of the difference between the observed and expected numbers of events to the

expected number of events in each group (Figure 11–71) and then sum to produce the test statistic (Figure 11–72).

Thus, c2 = 6.148. We now compute a p-value for the test using the chi-square distribution and the CHIDIST function in Excel. The CHIDIST function requires specification of the test statistic (c2) and the degrees of freedom. The log-rank statistic has degrees of freedom equal to k – 1, where k rep- resents the number of comparison groups. In this example,

23944_ch11_5976 193 8/3/11 11:48:46 AM

194 Survival Analysis

FigurE 11–71 Computing the Log Rank Test Statistic

FigurE 11–72 The Log-Rank Test Statistic

23944_ch11_5976 194 8/3/11 11:48:47 AM

Comparing Survival Curves 195

FigurE 11–73 Computing the P-Value for the Log Rank Test

tablE 11–4 Number of Weeks to First Drink or Number of Weeks Alcohol Free by Treatment Group

Standard Prenatal Care Brief Intervention

Relapse No Relapse Relapse No Relapse

19 20 16 21 6 19 21 15 5 17 7 18 4 14 18

k = 2, so the test statistic has 1 degree of freedom. The p-value for the test is computed as shown in Figure 11–73. Because the p-value = 0.013 is less than the level of significance (a = 0.05), we reject H0. We have significant evidence, a = 0.05, to show that the two survival curves are different.

Again, although it is tedious to construct the template for the computation of the log-rank test the first time, after the structure is entered into Excel, implementation of the test for new datasets is relatively easy. Only the data range in the formulas must be modified.

Example 11.8 In Example 11.4 in the textbook, we evalu- ated the efficacy of a brief intervention to prevent alcohol consumption in pregnancy. Pregnant women with a history of heavy alcohol consumption were recruited into the study and randomized to receive either the brief intervention fo- cused on abstinence from alcohol or standard prenatal care. The outcome of interest was relapse to drinking. Women were recruited into the study at approximately 18 weeks gestation and followed through the course of pregnancy to delivery (ap- proximately 39 weeks gestation). The data are shown in Table 11–4 and indicate whether women relapsed to drinking, and if so, the time of their first drink measured in the number of weeks from randomization. For women who did not relapse,

we recorded the number of weeks from randomization that they were alcohol free.

The question of interest is whether there is a difference in time to relapse between women assigned to standard prenatal care as compared to those assigned to the brief intervention. The test is run at a 5% level of significance, and the hypoth- eses are as follows:

H0: Relapse-free time is identical between groups H1: Relapse-free time is not identical between groups

23944_ch11_5976 195 8/3/11 11:48:47 AM

196 Survival Analysis

FigurE 11–74 Data for the Log Rank Test

FigurE 11–75 Sorted Data for Analysis

FigurE 11–76 The Log Rank Test Statistic and P-Value

To conduct the test, we first copy the worksheet with all of the formulas we developed to conduct the log-rank test in Example 11.7 and enter the data from Table 11–4 into the worksheet, as shown in Figure 11–74.

Next we copy the observed event times in column A and column D into column G and indicate the group (1 or 2) in which each event occurred. We then sort the data by the Event

Times and Group variables, as shown in Figure 11–62. The sorted data are shown in Figure 11–75.

Next, we update the formulas used to compute the num- bers of participants at risk in each group, N1t and N2t, to reflect the data ranges for these data (i.e., A$2:B$5 for group 1 and D$2:E$6 for group 2). When the data ranges are updated in cells J2 and K2, the formulas are copied to cells J3 through K8 (see the formula in the top menu bar for cell K8), as shown in

23944_ch11_5976 196 8/3/11 11:48:47 AM

Comparing Survival Curves 197

FigurE 11–77 Computing Survival Probabilities for Group 1

Figure 11–76. Excel then computes the observed numbers of events, the total numbers of participants and events, and the expected numbers of events in each group at each event time. The test statistic is then computed along with a p-value.

For this example, c2 = 0.727 (degrees of freedom = k – 1 = 2 – 1 = 1) and the p-value = 0.394. Because the p-value exceeds the level of significance (a = 0.05), we do not reject H0. We do not have statistically significant evidence at a = 0.05 to show that the time to relapse is different between groups.

11.4 comParing two survival curvEs graPhically In Section 11.2, we used Excel to generate graphical displays of a survival curve in one sample. We outlined the steps to produce graphical displays of the survival curve as well as displays that incorporated 95% confidence limits around the estimated survival probabilities. Here we present techniques to produce graphical displays of Kaplan–Meier survival func- tions in two independent groups using Excel.

Example 11.9 Consider again Example 11.7 where we com- pared survival between two competing combination treat- ments in patients with advanced gastric cancer. In Example 11.7 we conducted a log-rank test and found a statistically sig- nificant difference in survival between competing treatments (p = 0.013). Here we use Excel to produce a graphical display of the survival functions.

The first step is to produce the Kaplan–Meier estimates of the survival probabilities for each group using the template

we developed in Excel to compute survival probabilities using the Kaplan–Meier approach shown in Figure 11–22. We copy the worksheet (Figure 11–22) and enter the data for group 1. We need to adjust only the data ranges for the Time and Event variables (column D and column E) to produce the survival estimates shown in Figure 11–77.

Next we copy the data for group 2 into the worksheet and again copy the formulas from row 2 through row 11 to produce the survival estimates for the data in group 2 (see specifically row 14 through row 23), as shown in Figure 11–78. We again need to update the data ranges for the computations. For example, to compute the number of participants at risk in group 2, we specify the data range as D$14:D$23 in cell G23 (see top menu bar).

Next, we must prepare the data for the display. First, we hide the columns that are not directly involved in the display. We retain Time (column D) and Survival Probability (column J). We highlight the columns we wish to hide, right click, and select the Hide option. Next we convert the formulas we used to compute the Time and Survival Probability variables to their calculated values by highlighting those columns, click the Copy icon on the top menu bar, click the Paste icon, and then click the arrow to the right of the Paste icon and select the Values only option. These steps produce the data shown in Figure 11–79.

In order to produce a display with two survival curves, we need some additional formatting. First, we move the Survival Probability for group 2 into column K, as shown in Figure 11–80, and we remove the header rows for the group 2 data

23944_ch11_5976 197 8/3/11 11:48:48 AM

198 Survival Analysis

FigurE 11–78 Computing Survival Probabilities for Group 2 FigurE 11–79 Data for the Display

FigurE 11–81 Data for the Display

(i.e., remove rows 12 and 13 in Figure 11–79) by right click- ing each row, one at a time. From the available options we select Delete and then delete the entire row. We also rename the columns to S1t and S2t to represent survival in groups 1 and 2, respectively.

Again, we must format the data to produce the step functions for the survival probabilities. We first insert a row and enter “0” for the Time and “1” for the Survival Probability in each group (i.e., enter “0” for Time and “1” for S1t in cells D2 and J2 for group 1; and enter “0” for Time and “1” for S2t in cells D13 and K13 for group 2). This is shown in Figure 11–81.

Next we insert rows at each transition point (i.e., changes in Survival Probability), and we then copy the Time from the row above and the Survival Probability from the row below. The updated worksheet is shown in Figure 11–82. We now highlight the data for the display (column D, column J, and column K) and select the XY (Scatter) option under the Charts tab. We then select the fourth scatter plot option, with straight lines and markers, and Excel produces the display shown in Figure 11–83.

The final step is to format the display for presentation. This involves removing the horizontal lines, rescaling the

FigurE 11–80 Formatting the Data for the Display

23944_ch11_5976 198 8/3/11 11:48:48 AM

Comparing Survival Curves 199

FigurE 11–82 Data to Create Step Functions

FigurE 11–83 The Display

23944_ch11_5976 199 8/3/11 11:48:48 AM

200 Survival Analysis

FigurE 11–84 Survival in Each Treatment Group

Y-axis to a maximum of 1.0, and inserting a title and labels for the X- and Y-axes, as outlined in Figure 11–38 through Figure 11–43. We also change the line style for group 2 to distinguish it from group 1. The final display is shown in Figure 11–84.

11.5 PracticE ProblEms

1. A study is conducted to estimate survival in patients fol- lowing kidney transplant. Key factors that adversely affect success of the transplant include advanced age and diabe- tes. This study involves 25 participants (n = 25) who are 65 years of age and older, and all participants have diabetes. Following transplant, each participant is followed for up to 10 years. The following are times to death, in years, or the times to last contact (at which time the participant was known to be alive):

Deaths: 1.2, 2.5, 4.3, 5.6, 6.7, 7.3, 8.1 Alive: 3.4, 4.1, 4.2, 5.7, 5.9, 6.3, 6.4, 6.5, 7.3, 8.2, 8.6, 8.9,

9.4, 9.5, 10, 10, 10, 10

a. Use the life table approach to estimate the survival function.

b. Use the Kaplan–Meier approach to estimate the sur- vival function.

c. Graph the survival function based on the estimates in (b) using Excel.

2. A clinical trial is run to assess the effectiveness of a new antiarrhythmic drug designed to prevent atrial fibrilla- tion (AF). Thirty participants enroll in the trial and are randomized to receive the new drug or a placebo. The primary outcome is AF, and participants are followed for

23944_ch11_5976 200 8/3/11 11:48:49 AM

Practice Problems 201

tients undergoing joint replacement surgery using the log-rank test and a 5% level of significance.

5. Sketch the survival functions for each group (obese and nonobese) using the results from Problem 4.

6. A study of patients with stage I breast cancer is run to assess time to progression to stage II over an observa- tion period of 15 years. Of interest is whether there is a difference in time to progression between women on two different chemotherapy regimens. Times to progression are measured in years from the time at which the che- motherapy regimen was initiated (Table 11–7).

up to 12 months following randomization. The experi- ences of participants in each arm of the trial are shown in Table 11–5.

a. Estimate the survival functions for each treatment group using the Kaplan–Meier approach.

b. Test whether there is a significant difference in survival between treatment groups using the log-rank test and a 5% level of significance.

3. Sketch the survival functions for the new antiarrhyth- mic drug and the placebo groups using the results from Problem 2.

4. An observational cohort study is conducted to compare time to early failure in patients undergoing joint replace- ment surgery. Of specific interest is whether there is a difference in time to early failure between patients who are considered obese versus those who are not. The study is run for 40 weeks. Times to early joint failure, measured in weeks, are shown in Table 11–6 for participants classi- fied as obese or not obese at the time of surgery.

a. Estimate the survival functions (time to early joint failure) for each group using the Kaplan–Meier approach.

b. Test whether there is a significant difference in time to early joint failure between obese and nonobese pa-

tablE 11–5 Data for Practice Problems 2 and 3

Placebo New Drug

Month of AF Month of

Last Contact Month of AF Month of

Last Contact

4 5 7 6 6 5 9 6 7 6 12 7 8 7 8 9 8 9 11 9 9

11 10 12 10 12 11

11 12 12

tablE 11–6 Data for Practice Problems 4 and 5

Obese Not Obese

Failure No Failure Failure No Failure

28 39 27 37 25 41 31 36 31 37 34 39 32 35 40

38 36 36 32 29 39

tablE 11–7 Data for Practice Problem 6

Regimen 1 Regimen 2

Progression No

Progression Progression No

Progression

2 12 9 11 6 14 4 14 7 13 7 13 3 11 9 4 15 14

10 13 8 6 6 9 9 7 12

23944_ch11_5976 201 8/3/11 11:48:49 AM

202 Survival Analysis

b. Test whether there is a significant difference in time to progression between treatment regimens using the log-rank test and a 5% level of significance.

7. A clinical trial is conducted to evaluate the efficacy of a new drug for prevention of hypertension in patients with prehypertension (defined as systolic blood pres- sure between 120–139 mmHg or diastolic blood pressure between 80–89 mmHg). A total of 20 patients are ran- domized to receive the new drug or a currently available drug for treatment of high blood pressure. Participants are followed for up to 12 months, and time to progression to hypertension is measured. The experiences of partici- pants in each arm of the trial are shown in Table 11–8.

a. Estimate the survival functions (time to progression to hypertension) for each treatment group using the Kaplan–Meier approach.

b. Test whether there is a significant difference in time to progression between treatment groups using the log-rank test and a 5% level of significance.

8. The data in Table 11–9 reflect the time to first surgery in children born with congenital heart disease. Time is measured in years from birth up until the age of 10 years. Construct a life table using the Kaplan–Meier approach. Also include standard errors and 95% confidence limits for the estimates of survival probability.

rEFErEncEs 1. Allison P. Survival Analysis Using the SAS System. Cary, NC: SAS

Institute; 1995.

a. Estimate the survival functions (time to progression) for each chemotherapy regimen using the Kaplan– Meier approach.

tablE 11–8 Data for Practice Problem 7

New Drug Currently Available Drug

Hypertension Free of Hypertension Hypertension Free of Hypertension

7 8 6 8

8 8 7 9 10 8 9 11

9 10 11 11 11 12 12 12

tablE 11–9 Data for Practice Problem 8

Participant Identification Number

Year of First Surgery

Year of Last Contact

1 8 2 10 3 4 4 4 5 7 6 6 7 9 8 5 9 3 10 8 11 9 12 10 13 3 14 2 15 6 16 7 17 8 18 9

23944_ch11_5976 202 8/3/11 11:48:49 AM

23944_FMxx_5976.pdf
23944_CH01_5976
23944_CH02_5976
23944_CH03_5976
23944_CH04_5976
23944_CH05_5976
23944_CH06_5976
23944_CH07_5976
23944_CH08_5976
23944_CH09_5976
23944_CH10_5976
23944_CH11_5976