Excel Exercise Week 3 The homework is Practice Problems 5.4 Questions 1, 2, 3, 4, 5. Ive attached the excel assignment and the workbook where you can -

Excel Exercise Week 3
The homework is Practice Problems 5.4 Questions 1, 2, 3, 4, 5. Ive attached the excel assignment and the workbook where you can find the problems

EXCEL EXERCISE WEEK 3

Don't use plagiarized sources. Get Your Custom Assignment on

Excel Exercise Week 3 The homework is Practice Problems 5.4 Questions 1, 2, 3, 4, 5. Ive attached the excel assignment and the workbook where you can

From as Little as $13/Page

For this exercise please refer to the downloaded Excel Workbook (See Excel Workbook Instructions) from the JB Learning website with your access code. Please upload your answers as an excel file with properly labeled answers. At the end of the chapter 5 workbook you will find Practice problems. Please answer in excel only under practice problems 5.4 questions: 1, 2, 3, 4, 5. If help is needed, tutorials are found in the workbook with relevant problems.
Note: These pictures will give you an idea where it is in the workbook. You are responsible to use the workbook. 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 12).

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 11 that the
top left cell is outlined with a bold line. The column and
row make up the cells address. The top left cells 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.13 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 11. 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 11). The
identification numbers are not used in statistical analysis; they
keep track of data measured for each participant. The data are
shown in Table 11.

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 11
are entered into Excel by moving the cursor around the work-

sheet. Figure 13 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 13, 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 cells 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 11 Excel Workbook with Blank Worksheet

Worksheet Name

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

Entering and Editing Data 3

Figure 12 Renaming a Worksheet

tAble 11 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 cells contents
are shown (Figure 13). 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 cellsin 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 13 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 14 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 14).
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 15).

Figure 15 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 16).

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 16 Formatting Cells in a Worksheet

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

6 Basics

tAble 12 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 17 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 17). 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 19972004. 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
12. The data were presented in Table 413 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;
19992006.

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;

20022003.
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 22.

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 21.

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

Chapter 2
Formulas and Functions

taBlE 21 Mathematical Operators in Excel

Operation Operator

Multiplication
Division /
Addition +
Subtraction
Exponentiation ^

taBlE 22 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 22 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 22. 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 21.

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 21 Systolic Blood Pressure Measured at
Baseline and 6 Months Later

figurE 22 Creating a New Variable

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

Relative and Absolute Cell References 11

Figure 24 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 25.

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 26.

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 participants 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 23.

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
21. Suppose we now want to create the difference variable.

figurE 23 Using Relative Cell References

figurE 24 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 28.

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 27.

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

figurE 25 Difference Scores figurE 27 Computing Z Scores

figurE 28 Lengths, cm, of
Boys 12 Months of Age with Mean
and Standard DeviationfigurE 26 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 29
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 27 and
29 produce the results shown in Figure 210.

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 15 and 16) 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 29 Using Absolute Cell References

figurE 210 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 211 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 212.

figurE 212 BMI Values

figurE 211 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 213).

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 214 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 Excels 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 215.

Figure 216 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 213 Computing the Sample Mean

23944_ch02_5976 15 8/3/11 11:

Related Questions:

Related questions

Leave a Comment Cancel Reply