Mean
The term "mean" refers to the arithmetic average of a group of scores When my daughter was in fifth grade, she expressed her confusion about calculating averages after learning from her textbook.
Jennifer asked for help with her math problem, and I explained that she needed to add all the scores together and divide by the total number of scores However, she responded with a serious expression, insisting, "Dad, this is serious!" as she thought I was joking.
“See these numbers in your book; add them up What is the answer?” (She did that.)
“Now, how many numbers do you have?” (She answered that question.)
“Then, take the number you got when you added up the numbers, and divide that number by the number of numbers that you have.”
By applying the same reasoning, you'll discover the correct answer effortlessly, as Excel will automate all the necessary steps for you.
We will call this average of the scores the “mean” which we will symbolize as:
X, and we will pronounce it as: “Xbar.”
The formula for finding the mean with your calculator looks like this:
XẳΣX n ð1:1ị © Springer International Publishing Switzerland 2016
T.J Quirk, S.M Cummings, Excel 2016 for Health Services Management Statistics,
The Greek letter sigma (Σ) represents the concept of "sum," indicating that you should add all the values denoted by the letter X and then divide the total by n, which represents the count of those values.
Length of Stay (LOS) refers to the total number of days a patient spends in a healthcare facility, starting from the day of admission until discharge For instance, consider a random sample of mothers who gave birth and were discharged within the last two weeks, which provides valuable LOS data for analysis.
To find the mean of these scores, you add them up, and then divide by the number of scores So, the mean is: 25/6ẳ4.17
Standard Deviation
Standard deviation measures the proximity of scores to the mean, indicating how closely they cluster together A small standard deviation signifies that scores are closely grouped around the mean, while a large standard deviation indicates a wider dispersion of scores The formula for calculating standard deviation, represented by the symbol S, is essential for understanding data distribution.
The formula look complicated, but what it asks you to do is this:
1 Subtract the mean from each score (XX).
2 Then, square the resulting numbers to make each a positive number.
3 Then, add up these squared numbers to get a total score.
4 Then, take this total score and divide it by n1 (where n stands for the number of numbers that you have).
5 The final step is to take the square root of the number you found in step 4.
This article focuses on calculating the standard deviation using Excel rather than manual computation While calculators are not required for this process, examples can be found in basic statistics texts like Bowers (2008) Using Excel, we can determine the standard deviation of a specific set of scores, which in this case yields a standard deviation (S) of 1.47 for the provided six numbers.
Standard Error of the Mean
The formula for the standard error of the mean(s.e., which we will use S X to symbolize) is: s:e:ẳS X ẳ S
To calculate the standard error (SE), divide the standard deviation (STDEV) by the square root of n, where n represents the total number of values in your data set For instance, in the example provided, the standard error is 0.60, which can be verified using a calculator.
If you want to learn more about the standard deviation and the standard error of the mean, see Polit (2010) and Bowers (2008).
In this article, we will explore how to utilize Excel to calculate key statistical measures, including sample size, mean, standard deviation, and standard error of the mean, based on the Length of Stay (LOS) data for eight adult men discharged from a healthcare facility in the past week, as illustrated in Fig 1.1.
Fig 1.1 Worksheet Data for Length of Stay (Practical
Sample Size, Mean, Standard Deviation, and Standard
Using the Fill/Series/Columns Commands
Objective: To add the sample numbers 2–8 in the patient column
Home (top left of screen)
Important note: The “Paste” command should be on the top of your screen on the far left of the screen.
Important note: Notice the Excel commands at the top of your computer screen:
File Home Insert Page Layout Formulas etc.
If these commands ever “disappear” when you are using Excel,you need to click on “Home” at the top left of your screen to make them reappear!
Fill (top right of screen: click on the Series down arrow; see Fig.1.2)
The patient numbers should be identified as 1–8, with 8 in cell B11.
Enter the Length of Stay (LOS) data into cells C4 through C11, ensuring that you verify the accuracy of your figures to obtain the correct results.
To enhance the professional appearance of your computer screen's information, it's essential to learn how to widen the column width and center the content within a group of cells.
Fig 1.2 Home/Fill/Series commands
Fig 1.3 Example of Dialogue Box for Fill/Series/Columns/Step Value/Stop Value commands
Changing the Width of a Column
Objective: To make a column width wider so that all of the information fits inside that column
To ensure that all information is visible, you need to widen Column C on your computer screen.
Click on the letter, C, at the top of your computer screen
Place your mouse pointer on your computer at the far right corner of C until you create a “cross sign” on that corner
Left-click on your mouse, hold it down, and move this corner to the right until it is
“wide enough to fit all of the data”
Take your finger off your mouse to set the new column width (see Fig.1.4)
Then, click on any empty cell (i.e., any blank cell) to “deselect” column C so that it is no longer a darker color on your screen.
When you widen a column, you will make all of the cells in all of the rows of this column that same width.
Now, let’s go through the steps to center the information in both Column B and Column C.
Fig 1.4 Example of How to Widen the Column Width
Centering Information in a Range of Cells
Objective: To center the information in a group of cells
In order to make the information in the cells look “more professional,” you can center the information using the following steps:
Left-click your mouse pointer on B3 and drag it to the right and down to highlight cells B3:C11 so that these cells appear in a darker color
At the top of your computer screen, you will notice a series of lines that are uniformly centered in width under the "Alignment" section, which is represented by the second icon located at the bottom left of the Alignment box (refer to Fig 1.5).
Click on this icon to center the information in the selected cells (see Fig.1.6)
Fig 1.5 Example of How to Center Information
To simplify referencing the Length of Stay in your formulas, it's beneficial to assign a name to the data range instead of recalling specific cell references like C4:C11 For instance, you can label this group of cells as "LOS," but feel free to choose any name that suits your preference.
Naming a Range of Cells
Objective: To name the range of data for Length of Stay with the name: LOS
Highlight cells C4:C11 by left-clicking your mouse pointer on C4 and dragging it down to C11
Formulas (top left of your screen)
Define Name (top center of your screen)
LOS (type this name in the top box; see Fig.1.7)
Centering Information in the Cells
Then, click on any cell of your spreadsheet that does not have any information in it (i.e., it is an “empty cell”) to deselect cells C4:C11
Now, add the following terms to your spreadsheet:
When using formulas in Excel, it is essential to begin the formula with an equal sign (ẳ) to indicate that you are entering a function This ensures that Excel recognizes your input as a formula and processes it correctly.
Fig 1.7 Dialogue box for “naming a range of cells” with the name: LOS
Finding the Sample Size Using the ẳ COUNT
Objective: To find the sample size (n) for these data using the ẳCOUNT function
This command should insert the number 8 into cell F6 since there are eight patients in your sample.
Finding the Mean Score Using the ẳ AVERAGE
Objective: To find the mean LOS figure using theẳAVERAGE function
This command should insert the number 7.125 into cell F9.
Finding the Standard Deviation Using the ẳ STDEV
Objective: To find the standard deviation (STDEV) using theẳSTDEV function
This command should insert the number 3.482097 into cell F12.
Finding the Standard Error of the Mean
Objective: To find the standard error of the mean using a formula for these eight data points
This command should insert the number 1.231107 into cell F15 (see Fig.1.9).
It is crucial to verify all figures in your spreadsheet to ensure they are placed in the correct cells, as any discrepancies may lead to incorrect formula results.
1.4.8.1 Formatting Numbers in Number Format (Two Decimal Places)
Objective: To convert the mean, STDEV, and s.e to two decimal places
Home (top left of screen)
To decrease the number of decimal places displayed, locate the "Number" section at the top center of your screen Then, move your mouse pointer to the bottom right corner of the 00 0 area until the option "Decrease Decimal" appears.
Fig 1.9 Example of Using Excel Formulas for Sample Size, Mean, STDEV, and s.e.
Click on this icononce and notice that the cells F9:F15 are now all in just two decimal places (see Fig.1.11)
Now, click on any “empty cell” on your spreadsheet to deselect cells F9:F15. Fig 1.11 Example of Converting Numbers to Two Decimal Places
Fig 1.10 Using the “Decrease Decimal Icon” to convert Numbers to Fewer Decimal Places
Saving a Spreadsheet
Objective: To save this spreadsheet with the name: LOS3
To ensure you can access your spreadsheet later, the first step is to determine the appropriate location for saving it You have multiple options, including saving it directly to your computer's hard drive; if you're unsure how to do this, consider seeking assistance from someone knowledgeable about your device.
Or, you can save it onto a “CD” or onto a “flash drive.” To save the spreadsheet, you need to complete these steps:
To save a file, navigate through the left sidebar by scrolling up or down, and click on your desired location, such as "This PC" or "My Documents."
File name: LOS3 (enter this name to the right of File name; see Fig.1.12)
Important note: Be very careful to save your Excel file spreadsheet every few minutes so that you do not lose your information!
Fig 1.12 Dialogue Box of Saving an Excel Workbook File as “LOS3” in My Documents location
Printing a Spreadsheet
Objective: To print the spreadsheet
Use the following procedure when printing any spreadsheet.
Print Active Sheets (see Fig.1.13)
Print (top of your screen)
The final spreadsheet is given in Fig1.14
Fig 1.13 Example of How to Print an Excel Worksheet Using the File/Print/Print Active SheetsCommands
Before concluding this chapter, we will practice modifying the format of figures in a spreadsheet through two examples: first, by formatting dollar amounts to display two decimal places, and second, by adjusting numerical figures to show three decimal places.
To save your final spreadsheet, navigate to File and select Save, then close the spreadsheet by choosing File and Close To create a new document, open a blank Excel spreadsheet by clicking on File, then New, and selecting Blank Workbook from the top left corner of your screen.
Formatting Numbers in Currency Format (Two Decimal Places)
Objective: To change the format of figures to dollar format with two decimal places
Highlight cells A4:A6 by left-clicking your mouse on A4 and dragging it down so that these three cells are highlighted in a darker color
Number (top center of screen: click on the down arrow on the right; see Fig.1.15)Fig 1.14 Final Result of Printing an Excel Spreadsheet
Decimal places: 2 (then see Fig.1.16)
Fig 1.15 Dialogue Box for Number Format Choices
The three cells should have a dollar sign in them and be in two decimal places.Next, let’s practice formatting figures in number format, three decimal places.
Formatting Numbers in Number Format (Three Decimal Places)
Objective: To format figures in number format, three decimal places
Highlight cells A4:A6 on your computer screen
Number (click on the down arrow on the right)
At the right of the box, change two decimal places to three decimal places by clicking on the “up arrow” once
Fig 1.16 Dialogue Box for Currency (two decimal places) Format for Numbers
The three figures should be formatted to display three decimal places Next, click on any empty cell to deselect the range A4:A6 Finally, close the file by navigating to File > Close and select "Don’t Save" as there is no need to retain changes made during this practice exercise.
You can use these same commands to format a range of cells in percentage format (and many other formats) to whatever number of decimal places you want to specify.
End-of-Chapter Practice Problems
The birthweight of infants born in a healthcare facility is crucial data for monitoring health outcomes By analyzing a random sample of 14 infants born last month, we can gain insights into their birth weights recorded in grams, as illustrated in the hypothetical data presented in Fig.1.17.
To the right of the table, utilize Excel to calculate the sample size, mean, standard deviation, and standard error of the mean for the given data Ensure to label your results clearly and round the mean, standard deviation, and standard error of the mean to two decimal places, applying number formatting to these three values.
(b) Print the result on a separate page.
(c) Save the file as: BIRTH4
Monthly laboratory supply expenses significantly impact the profitability of healthcare facilities By analyzing the total supply expenses recorded over the past year, you can assess their variability The hypothetical data, presented in thousands of dollars, indicates that an expense of 18.7 corresponds to $18,700 for January.
Fig 1.17 Worksheet Data for Chap 1: Practice
To analyze the data effectively, utilize Excel to construct a table, then calculate the sample size, mean, standard deviation, and standard error of the mean Ensure to label each result clearly and format the mean, standard deviation, and standard error to two decimal places in currency format for clarity.
(b) Print the result on a separate page.
(c) Save the file as: SUPPLY3
Mental health respite care offers temporary support for individuals with severe mental illnesses living at home, allowing caregivers a much-needed break while ensuring their loved ones are safe If you are interested in the age distribution of patients in such facilities over the past month, refer to the hypothetical data presented in Fig 1.19.
Fig 1.18 Worksheet Data for Chap 1: Practice
To analyze the data effectively, utilize Excel to construct a table that organizes the information clearly Next, calculate the sample size, mean, standard deviation, and standard error of the mean, placing these results adjacent to the table Ensure that each answer is properly labeled and round the mean, standard deviation, and standard error of the mean to three decimal places using the number format in Excel.
(b) Print the result on a separate page.
(c) Save the file as: RESPITE3
Bowers D Medical statistics from scratch: an introduction for health professionals Hoboken: John Wiley & Sons; 2008.
Polit DF Statistics and data analysis for nursing research Upper Saddle River: Pearson Education Inc.; 2010.
Fig 1.19 Worksheet Data for Chap 1: Practice
A health administrator aims to assess the average waiting time for patients who visited the Emergency Department (ED) on Wednesday evening from 7:00 p.m to 11:00 p.m To achieve this, she has requested a random sample of 5 patients from a total of 32 who arrived during that time Utilizing Excel, the first step involves defining a "sampling frame" to facilitate the selection of this random sample effectively.
A sampling frame is a crucial tool for selecting a random sample, consisting of a list of objects, events, or individuals In this context, the sampling frame includes 32 incoming patients from last Wednesday evening To establish this frame, each patient is assigned a unique identifier, starting with the first patient receiving an ID of 1, followed by the second patient with an ID of 2, and continuing sequentially until the last patient is assigned an ID of 32.
Since the group had 32 incoming patients, your sampling frame would go from
1 to 32 with each patient having a unique ID number.
We will first create the frame numbers as follows in a new Excel worksheet:
Creating Frame Numbers for Generating Random
Objective: To create the frame numbers for generating random numbers
To generate frame numbers in column A, utilize the Home/Fill commands detailed in Section 1.4.1 of this book.
T.J Quirk, S.M Cummings, Excel 2016 for Health Services Management Statistics,
21 numbers go from 1 to 32, with the number 32 in cell A35 If you need to be reminded about how to do that, here are the steps:
Click on cell A4 to select this cell
Fill (then click on the “down arrow” next to this command and select)
Fig 2.1 Dialogue Box for Fill/Series Commands
Fig 2.2 Dialogue Box for Fill/Series/Columns/Step value/Stop value Commands
Then, save this file as: Random29 You should obtain the result in Fig.2.3.
Now, create a column next to these frame numbers in this manner:
To format your spreadsheet, use the Home/Fill command to populate frame numbers from cell B4 to B35 Ensure that columns A and B are widened to accommodate all content, and center the information in both columns for a polished appearance, as illustrated in Fig 2.4.
Fig 2.3 Frame Numbers from 1 to 32
Save this file as: Random30
To ensure that you have precisely 32 frame numbers for your random sequence, you may find duplicate information in both Column A and Column B of your spreadsheet This duplication serves as a safeguard before sorting the frame numbers.
Now, let’s add a random number to each of the duplicate frame numbers as follows:
Creating Random Numbers in an Excel Worksheet
C3: RANDOM NO (then widen columns A, B, C so that their labels fit inside the columns; then center the information in A3:C35)
Next, hit the Enter key to add a random number to cell C4.
To generate a random number in a cell, you must use the RAND() function, which requires both an open and a closed parenthesis The RAND command references the cells to the left of the one containing the RAND() function to assign a random value.
To add a random number to all 32 ID frame numbers, position your mouse pointer over cell C4 and move it to the bottom right corner until a “plus sign” appears Then, click and drag the pointer down to cell C35.
Then, click on any empty cell to deselect C4:C35 to remove the dark color highlighting these cells.
Save this file as: Random31
Now, let’s sort these duplicate frame numbers into a random sequence:
Random Numbers Assigned to the Duplicate Frame
Sorting Frame Numbers into a Random Sequence
Objective: To sort the duplicate frame numbers into a random sequence
Highlight cells B3:C35 (include the labels at the top of columns B and C)
Sort (click on this word at the top center of your screen; see Fig.2.6)
Sort by: RANDOM NO (click on the down arrow)
Smallest to Largest (see Fig.2.7)
Fig 2.6 Dialogue Box for Data/Sort Commands
Click on any empty cell to deselect B3:C35.
Save this file as: Random32
These steps will produce Fig.2.8with the DUPLICATE FRAME NUMBERS sorted into a random order:
Important note: Because Excel randomly assigns these random numbers, your
Excel commands will produce a different sequence of random numbers from everyone else who reads this book!
Fig 2.7 Dialogue Box for Data/Sort/RANDOM NO./Smallest to Largest Commands
Your objective at the beginning of this chapter was to randomly select 5 of the
32 incoming patients who arrived in the Emergency Department last Wednesday evening You can now do that by selecting thefirst five sorted ID numbersin the DUPLICATE FRAME NO column.
While your initial selection of five random numbers will differ from those chosen in our random sort within this chapter, we will proceed to select these five patient IDs (refer to Fig 2.9).
When using the RAND() function in Excel, it's important to note that the five ID numbers generated will differ from those shown in Fig 2.9, as Excel produces a new random number each time the command is executed.
Before concluding this chapter, it's essential to understand how to print a file so that all its information fits neatly on a single page, avoiding any overflow onto additional pages.
Printing an Excel File So That All of the Information
Objective: To print a file so that all of the information fits onto one page
This chapter includes three practice problems that involve sorting random numbers from files containing 63 nurses, 114 Medicare claims, and 75 long-term care facilities in Missouri Due to their size, these files will not fit on a single printed page unless properly formatted.
Let’s create a situation where the file does not fit onto one printed page unless you format it first to do that.
Go back to the file you just created, Random 33, and enter the name:Jennifer into cell: A50.
Printing this file will cause the name "Jennifer" to appear on a second page due to it extending beyond the page margins in its current format.
To ensure that all information, including the name Jennifer, fits onto a single page when printing, you will need to adjust the page format by following specific steps.
Click on any empty cell to change the pointer from cell A50
Page Layout (top left of the computer screen)
(Notice the “Scale to Fit” section in the center of your screen; see Fig.2.10)
Hit the down arrow to the right of 100 %onceto reduce the size of the page to 95 %Fig 2.10 Dialogue Box for Page Layout/Scale to Fit Commands
The name "Jennifer" appears on the second page of your screen, positioned below the horizontal dotted line in Fig 2.11, which indicates the outline dimensions of the file as it would appear if printed.
To adjust the worksheet size to 90% of its normal dimensions, simply press the down arrow on the right again As shown in Fig 2.12, the dotted lines now appear below Jennifer's name, indicating that all content, including her name, is formatted to fit on a single page for printing.
Fig 2.11 Example of Scale Reduced to 95 % with “Jennifer” to be Printed on a Second Page
Fig 2.12 Example of Scale Reduced to 90 % with “Jennifer” to be printed on the first page (note the dotted line below Jennifer on your screen)
Save the file as: Random34
Print the file Does it all fit onto one page? It should (see Fig.2.13).
Spreadsheet of 90 % Scale to Fit
End-of-Chapter Practice Problems
As the Director of a nurse training program, you aim to enhance the curriculum by gathering insights from a random sample of the 63 graduating nurses By conducting interviews, you will obtain valuable suggestions that can lead to significant improvements in the program.
15 of these 63 nurses for a personal interview.
(a) Set up a spreadsheet of frame numbers for these nurses with the heading: FRAME NUMBERS using the Home/Fill commands.
To organize your data, first create a column labeled "Duplicate Frame Numbers" next to the existing frame numbers Then, add another column titled "RANDOM NO." where you will use the =RAND() function to generate random numbers corresponding to each frame number in the duplicate column Ensure to format this column to display three decimal places for each random number.
(d) Sort the duplicate frame numbers and random numbers into a random order. (e) Print the result so that the spreadsheet fits onto one page.
(f) Circle on your printout the I.D number of the first 15 nurses that you would use in your interviews.
(g) Save the file as: RAND9
It's important to understand that each time you use the RAND() function in Excel, it generates a unique random order of ID numbers Consequently, the sequence of random numbers you produce will differ from the example provided in this Excel Guide This variability is normal and expected.
In reviewing the Medicare claims submitted by your home health care agency last month, you will audit a random sample of 10 out of 114 claims This audit aims to identify any potential billing errors prior to conducting a comprehensive review of all submitted claims.
(a) Set up a spreadsheet of frame numbers for these claims with the heading: FRAME NO.
(b) Then, create a separate column to the right of these frame numbers which duplicates these frame numbers with the title: Duplicate frame no.
To enhance your spreadsheet, add a new column labeled "Random Number" next to the duplicate frame numbers Utilize the =RAND() function to generate random numbers for each entry in this column Finally, adjust the formatting of the random numbers to display three decimal places for a polished appearance.
(d) Sort the duplicate frame numbers and random numbers into a random order. (e) Print the result so that the spreadsheet fits onto one page.
(f) Circle on your printout the I.D number of the first 10 claims that would be used in this audit.
(g) Save the file as: RANDOM6
3 Suppose, for the sake of argument, that the city of St Louis, Missouri USA has a total of 75 long-term-care facilities Assume that you want to randomly sample
A survey of 20 out of 75 long-term care facilities was conducted through phone interviews with their Directors to gather insights on enhancing the quality of care provided to residents.
(a) Set up a spreadsheet of frame numbers for these facilities with the heading: FRAME NUMBERS.
To organize your data effectively, first create a column labeled "Duplicate frame numbers" next to the original frame numbers Then, add another column titled "Random number" to the right of the duplicate frame numbers In this new column, utilize the =RAND() function to generate random numbers corresponding to each frame number in the duplicate column Finally, adjust the formatting of this column to display each random number with three decimal places.
(d) Sort the duplicate frame numbers and random numbers into a random order. (e) Print the result so that the spreadsheet fits onto one page.
(f) Circle on your printout the I.D number of the first 20 facilities that you would select for your phone interviews.
(g) Save the file as: RAND5
Confidence Interval About the Mean Using the TINV Function and Hypothesis Testing
This chapter focuses on two ideas: (1) finding the 95 % confidence interval about the mean, and (2) hypothesis testing.
Let’s talk about the confidence interval first and then hypothesis testing.
Confidence Interval About the Mean
How to Estimate the Population Mean
Objective: To estimate the population mean,μ
The population mean represents the average of a specific demographic within a target population For instance, if we want to assess the preferences of adults aged 25–44 regarding a new Ben & Jerry's ice cream flavor, surveying the entire U.S population in that age group would be impractical due to time and cost constraints.
To efficiently estimate the population mean without testing everyone, we utilize a sample of individuals from the population This approach, known as inferential statistics, allows us to draw conclusions about the entire population based on the sample mean, ultimately saving both time and resources.
T.J Quirk, S.M Cummings, Excel 2016 for Health Services Management Statistics,
When analyzing a sample from a population, we determine the sample size (n), sample mean (X), and sample standard deviation (STDEV) These metrics are essential for estimating the precision of our estimated population mean through a method known as the "confidence interval about the mean."
Estimating the Lower Limit and the Upper Limit
of the 95 % Confidence Interval About the Mean
This book does not delve into the theoretical background of the test; however, for a comprehensive understanding, readers are encouraged to consult a reputable statistics textbook, such as Veney et al (2009) The fundamental concepts of the test are outlined here.
We assume that the population mean is somewhere in an interval which has a
In this book, we establish a "lower limit" and an "upper limit" to define a confidence interval, aiming for a 95% confidence level that the population mean lies within this range.
“We are 95 % confident that the population mean of the number of outpatient visits to our clinic during the past 12 months was between 3.25 visits and 3.67 visits.”
In our clinic, we can assert that the average number of outpatient visits over the past year is 3.50, as this figure falls within the 95% confidence interval of our research study While we do not know the exact population mean, we estimate it to be between 3.25 and 3.67 visits, with our sample mean of 3.50 residing within this range However, it is important to note that we are only 95% confident in this estimate, meaning there is a 5% chance that the actual population mean differs from our assumption of 3.50 visits.
In scientific research, a 95% confidence level is commonly used to affirm the accuracy of assumptions, though this figure is arbitrary Researchers have the flexibility to choose different confidence levels, such as 80%, 90%, or even 99% For consistency throughout this book, we will adhere to a 95% confidence level, eliminating any uncertainty regarding the degree of confidence in the presented problems.
So how do we find the 95 % confidence interval about the mean for our data?
In words, we will find this interval this way:
To calculate the confidence interval, start with the sample mean (X) and add 1.96 times the standard error of the mean (s.e.) to determine the upper limit For the lower limit, subtract 1.96 times the standard error of the mean from the sample mean.
The standard error of the mean (s.e.) is calculated by dividing the standard deviation of a sample (STDEV) by the square root of the sample size (n).
In mathematical terms, the formula for the 95 % confidence interval about the mean is:
To calculate the confidence interval, first add and subtract 1.96 times the standard error (s.e.) from the mean This process determines the upper limit by adding 1.96 s.e to the mean and the lower limit by subtracting 1.96 s.e from the mean, where 1.96 s.e represents the product of 1.96 and the standard error of the mean.
Note: We will explain shortly where the number 1.96 came from.
Let’s try a simple example to illustrate this formula.
Estimating the Confidence Interval for the
of Outpatient Visits to a Clinic
A study of outpatient clinic visits was conducted using a random sample of 36 patients who received care in the past year The analysis revealed that these patients averaged 3.42 visits, with a standard deviation of 1.15 visits The standard error (s.e.) was calculated by dividing the standard deviation by the square root of the sample size, resulting in an s.e of 0.19 visits.
The 95 % confidence interval for these data would be:
Theupper limit of this confidence intervaluses the plus sign of thesign in the formula Therefore, the upper limit would be:
Similarly,the lower limit of this confidence intervaluses the minus sign of the sign in the formula Therefore, the lower limit would be:
The result of our part of the ongoing research study would, therefore, be the following:
“We are 95 % confident that the population mean for the number of outpatient visits to these clinics during the past 12 months was between 3.05 visits and 3.79 visits.”
Our analysis indicates that the average number of outpatient visits to these clinics over the past 12 months is 3.50, as this value falls within the 95% confidence interval for the population mean This finding is supported by our sample mean of 3.50 visits You may wonder about the significance of the 1.96 in the confidence interval formula.
Where Did the Number “1.96” Come From?
A detailed mathematical answer to that question is beyond the scope of this book, but here is the basic idea.
We assume that the population data follows a "normal distribution," which means that if we could test every individual or property in the population, the data would form a "normal curve." This curve resembles the outline of the Liberty Bell in Philadelphia and is characterized by its symmetry; if divided down the middle, each half would align perfectly when folded over.
In this article, we explore the concept of confidence intervals in statistics, specifically focusing on the normal curve where 95% of the area lies between the lower and upper limits For research studies involving more than 40 participants, these limits can be calculated as plus or minus 1.96 times the standard error of the mean (s.e.) This calculation helps establish the confidence interval, providing crucial insights into population data For further understanding of these statistical principles, readers are encouraged to refer to reputable statistics literature, such as Polit (2010).
The number 1.96 would change if we wanted to be confident of our results at a different level from 95 % as long as we have more than 40 people in our research study.
1 If we wanted to be 80 % confident of our results, this number would be 1.282.
2 If we wanted to be 90 % confident of our results, this number would be 1.645.
3 If we wanted to be 99 % confident of our results, this number would be 2.576.
In this book, we aim for 95% confidence in our results, which is why we will consistently use a value of 1.96 for studies involving more than 40 participants.
Finding the Value for t in the Confidence
Objective: To find the value for t in the confidence interval formula
The correct formula for the confidence interval about the mean for different sample sizes is the following:
To calculate the 95% confidence interval, start by determining the sample mean (X) The upper limit is found by adding the product of the t-value and the standard error (s.e.) to the sample mean, while the lower limit is obtained by subtracting the same product from the sample mean To find the appropriate t-value, refer to the table provided in Appendix E of this book.
Objective: To find the value of t in the t-table in AppendixE
Before we get into an explanation of what is meant by “the value of t,” let’s give you practice in finding the value of t by using the t-table in AppendixE.
Keep your finger on Appendix Eas we explain how you need to “read” that table.
In this chapter, referred to as the "confidence interval about the mean test," you will utilize the first column on the left in Appendix E to determine the critical value of t relevant to your research study, which is labeled as "sample size n."
To determine the value of t for your research study, locate the sample size in the first column of the table, then move to the right to find the corresponding t value in the "critical t column," which is used for a 95% confidence interval about the mean For instance, if your study includes 14 participants, the t value is 2.160.
If you have 26 people in your research study, the value of t is 2.060.
If you have more than 40 people in your research study, the value of t is always 1.96.
Note that the “critical t column” in AppendixErepresents the value of t that you need to use to be 95 % confident that your results are “significant”.
This book operates under the assumption that you aim for 95% confidence in your statistical test results Consequently, the t-value from the t-table in Appendix E will guide you in determining the appropriate t-value to use when calculating the 95% confidence interval for the mean.
To calculate the confidence interval for the mean using Excel, first determine the value of t from the appropriate statistical table Next, input your data into Excel and utilize functions such as AVERAGE and STDEV to find the mean and standard deviation Finally, apply the formula for the confidence interval, incorporating the t value, to obtain your results efficiently within the spreadsheet.
Using Excel ’ s TINV Function to Find the
Objective: To use the TINV function in Excel to find the confidence interval about the mean
When you use Excel, the formulas for finding the confidence interval are:
Lower limit:ẳXTINVð10:95,n1ị*s:e:ðno spaces between these symbolsị ð3:3ị
Upper limit:ẳXỵTINVð10:95,n1ị*s:e:ðno spaces between these symbolsị ð3:4ị
In Excel formulas, the “*” symbol indicates multiplication, representing the term "times" as used in mathematical expressions Additionally, remember that "n" denotes the sample size, while "n-1" signifies the sample size minus one.
In Chapter 1, we established that the standard error of the mean (s.e.) is calculated by dividing the standard deviation (STDEV) by the square root of the sample size (n) To illustrate this concept, we will now use Excel to determine the 95% confidence interval for the mean in a practical example.
Let’s suppose that a clinic wanted to claim that the average number of outpatient clinic visits during the past 12 months was 3.50 visits Let’s call 3.50 visits the
“reference value” for this clinic.
To verify the validity of the claim, you undertake a research initiative to gather relevant data This process involves meticulous data collection and analysis to assess the claim's credibility based on empirical evidence.
95 % confidence interval about the mean to test your results:
Using Excel to Find the 95 % Confidence
Objective: To analyze the data using a 95 % confidence interval about the mean
In a research study involving a random sample of 25 outpatients, the number of clinic visits recorded over a span of 12 months yielded the hypothetical results illustrated in Fig 3.1.
To analyze the data effectively, create a spreadsheet in Excel and utilize the appropriate formulas to calculate the sample size (n), mean, standard deviation (STDEV), and standard error of the mean (s.e.) Ensure that you reference the correct cells for accurate results.
A5: Visits during the past 12 months
Enter the other Outpatient Visits data in cells A7:A30
To format the numbers in cells A6:A30, select the range and apply the number format with zero decimal places, ensuring the values are centered in Column A Additionally, increase the widths of both Columns A and B to three times their original size for better visibility.
A Then, widen column C so that it is three times as wide as the original width of column A so that your table looks more professional.
Fig 3.1 Worksheet Data for Outpatient Visits (Practical Example)
B26: Draw a picture below this confidence interval
B29: lower (right-align this word)
B30: limit (right-align this word)
C28: ‘ - 3.40 -– 3.50 -– (note that you need to begin cell C28 with a single quotation mark (‘) to tell Excel that this is a label, and not a number)
D28: ‘ - (note the single quotation mark)
C30: value (move to the right under Ref using the space bar)
Fig 3.2 Example of Outpatient Visits Format for the Confidence Interval About the Mean Labels
Now, align the labels underneath the picture of the confidence interval so that they look like Fig.3.3.
Next, name the range of data from A6:A30 as: Outpatient4
D7: Use Excel to find the sample size
D10: Use Excel to find the mean
D13: Use Excel to find the STDEV
D16: Use Excel to find the s.e.
Now, you need to find the lower limit and the upper limit of the 95 % confidence interval for this study.
We will use Excel’s TINV function to do this We will assume that you want to be 95 % confident of your results.
Fig 3.3 Example of Drawing a Picture of a Confidence Interval About the Mean Result
F21:ẳD10TINV 1ð :95, 24ị*D16 ðno spaces between symbolsị
Note that this TINV formula uses 24 since 24 is one less than the sample size of
The confidence interval's lower limit is calculated using the formula 2.78, where D10 represents the mean and D16 indicates the standard error of the mean The formula for this calculation is F23: =D10 + TINV(1:95, 24) * D16, ensuring there are no spaces between the symbols.
The calculated upper limit of the confidence interval is 4.02 To enhance readability, format your Excel spreadsheet to display the mean, standard deviation, standard error of the mean, and both the lower and upper limits of the confidence interval to two decimal places Currently, if you were to print this spreadsheet, the lower limit of 2.78 and the upper limit of 4.02 would extend onto a second page due to the excessive size of the information displayed.
To resize your spreadsheet to 80% of its original dimensions, utilize Excel's "Scale to Fit" feature found under the Page Layout tab, as mentioned in Chapter 2, Section 2.4 After applying this adjustment, observe that the dotted line next to the measurements 2.78 and 4.02 now indicates that these dimensions will fit onto a single printed page, as illustrated in Figure 3.4.
Fig 3.4 Result of Using the TINV Function to Find the Confidence Interval About the Mean
Note that you have drawn a picture of the 95 % confidence interval beneath cell B26, including the lower limit, the upper limit, the mean, and the reference value of 3.50.
Now, let’s write the conclusion to your research study on your spreadsheet:
C33: Since the reference value of 3.50 is inside the
C34: confidence interval, we accept that the average
C35: number of outpatient visits to the clinic during
C36: the past 12 months was 3.50 visits.
When formatting the conclusion in a spreadsheet, it's crucial to avoid placing it on a single long line Doing so can lead to two significant issues: first, if you reduce the page layout size for printing, the font may become too small to read; second, printing without adjusting the layout may result in part of the conclusion spilling over onto a separate page, compromising the professional appearance of your spreadsheet.
The research study confirmed that the average number of outpatient visits to the clinic over the past year was 3.50 Please save the resulting spreadsheet as OUTPATIENT3.
Hypothesis Testing
Hypotheses Always Refer to the Population
The first step is to understand that our hypotheses always refer to thepopulationof people or events in a study.
To evaluate our nursing program effectively, we can conduct in-depth interviews with a random sample of students two months prior to their graduation The insights gained from this sample will enable us to generalize our findings to the entire population of students set to graduate in the same timeframe.
The focus of our study is on the students set to graduate from the program in two months, representing the larger population we aim to analyze The specific students we interview constitute our sample, which is a smaller subset of this population Our interest lies in how the findings from this sample can be generalized to reflect the broader population of graduating students.
That is why our hypotheses always refer to the population, and never to the sample of people or events in our study.
You will recall from Chap.1that we used the symbol:Xto refer to the mean of the sample we use in our research study (see Sect.1.1).
We will use the symbol:μ(the Greek letter “mu”) to refer to the population mean.
In testing our hypotheses, we are trying to decide which one of two competing hypothesesabout the population meanwe should accept given our data set.
The Null Hypothesis and the Research (Alternative) Hypothesis
These two competing hypotheses are called thenull hypothesisand the research hypothesis.
Statistics textbooks typically refer to thenull hypothesiswith the notation:H0.
Theresearch hypothesisis typically referred to with the notation:H1, and it is sometimes called thealternative hypothesis.
Let’s explain first what is meant by the null hypothesis and the research hypothesis:
(1) The null hypothesis is what we accept as true unless we have compelling evidence that it is not true.
(2) The research hypothesis is what we accept as true whenever we reject the null hypothesis as true.
In the American legal system, individuals are presumed innocent until proven guilty by a jury This principle aligns with the concept of a null hypothesis, where the assumption is that the defendant is innocent, while the research hypothesis posits that the defendant is guilty.
In Missouri, the state slogan "Show me" reflects the residents' skepticism, emphasizing that they require proof before accepting claims as true This belief underscores the idea that actions are far more significant than words, highlighting the importance of demonstrating integrity through behavior rather than mere statements.
In hypothesis testing, the goal is to determine which of the two competing statements—the null hypothesis or the research hypothesis—is true, as both cannot coexist simultaneously This process involves applying statistical formulas to evaluate the evidence and make an informed decision on which hypothesis to accept.
In health services management research, surveys often utilize rating scales to assess individuals' attitudes toward an organization's activities Commonly employed scales include 5-point, 7-point, and 10-point formats, although other variations may also be utilized.
3.2.2.1 Determining the Null Hypothesis and the Research Hypothesis
When Rating Scales Are Used
This article provides examples of testing both the null hypothesis and research hypothesis using rating scales, demonstrating practical applications for professionals encountering these scales in their work.
The American Health Information Management Association (AHIMA) has more than 67,000 members who work in a variety of health information settings that connect clinical, operational, and administrative functions.
A 7-point scale, utilized by the American Health Information Management Association (AHIMA), effectively gathers participant feedback on the value of its annual week-long international conference.
So, how do we decide what to use as the null hypothesis and the research hypothesis whenever rating scales are used?
Objective: To decide on the null hypothesis and the research hypothesis when- ever rating scales are used.
In order to make this determination, we will use a simple rule:
Rule: Whenever rating scales are used, we will use the “middle” of the scale as the null hypothesis and the research hypothesis.
In the given example, the number 4 serves as the median of the scale, with three numbers positioned below it and three above Consequently, our hypotheses are formulated as follows: the null hypothesis states that the mean (μ) equals 4.
Based on the statistical test results for the attitude scale item, if the population mean is approximately 4, we conclude that we accept the null hypothesis, indicating that the AHIMA conference participants were neutral regarding their satisfaction with the overall quality of the conference.
If our statistical test shows that the population mean significantly differs from 4, we will reject the null hypothesis and accept the research hypothesis.
Participants of the AHIMA conference expressed high levels of satisfaction regarding the overall quality of the event, as evidenced by a sample mean that significantly exceeds the expected population mean of 4.
Participants of the AHIMA conference expressed notable dissatisfaction with the overall quality, as indicated by a sample mean that was significantly lower than the anticipated population mean of 4.
Fig 3.6 Example of a Rating Scale Item for a Conference (Practical Example)
Both of these conclusions cannot be true We accept one of the hypotheses as
“true” based on the data set in our research study, and the other one as “not true” based on our data set.
In research, the primary responsibility of the researcher is to determine whether to accept the null hypothesis or the research hypothesis as true based on the data collected in the study.
Let’s try some examples of rating scales so that you can practice figuring out what the null hypothesis and the research hypothesis are for each rating scale.
In the spaces in Fig.3.7, write in the null hypothesis and the research hypothesis for the rating scales:
Here are the answers to these three questions:
1 The null hypothesis isμẳ3, and the research hypothesis isμ6ẳ3 on this 5-point scale (i.e the “middle” of the scale is 3).
Fig 3.7 Examples of Rating Scales for Determining the Null Hypothesis and the ResearchHypothesis
2 The null hypothesis isμẳ4, and the research hypothesis isμ6ẳ4 on this 7-point scale (i.e., the “middle” of the scale is 4).
3 The null hypothesis isμẳ5.5, and the research hypothesis isμ6ẳ5.5 on this 10-point scale (i.e., the “middle” of the scale is 5.5 since there are 5 numbers below 5.5 and 5 numbers above 5.5).
Webster University, located in St Louis, Missouri, employs a Course Feedback form to gather student evaluations at the conclusion of its courses, catering to over 17,000 students across 60 cities.
The Course Feedback form, utilized across 11 countries, features 12 rating items that assess the course's planning, organization, and the communication level between instructors and students After course completion, the ratings are summarized and provided to instructors, with each item evaluated on a 4-point scale.
The null hypothesis is defined as μ = 2.5, while the research hypothesis posits μ ≠ 2.5 This is based on the observation that there are two ratings below 2.5 and two ratings above 2.5 on the scale It's important to note that this scale is structured such that lower scores indicate better performance, similar to scoring in golf.
Now, let’s discuss the 7 STEPS of hypothesis testing for using the confidence interval about the mean.
The 7 Steps for Hypothesis-Testing Using
the Confidence Interval About the Mean
Objective: To learn the 7 steps of hypothesis-testing using the confidence interval about the mean
There are seven basic steps of hypothesis-testing for this statistical test.
3.2.3.1 STEP 1: State the Null Hypothesis and the Research Hypothesis
When utilizing numerical scales in surveys, it's essential to recognize that both the null and research hypotheses pertain to the midpoint of the scale For instance, in a 7-point scale where 1 represents "poor" and 7 signifies "excellent," the hypotheses are centered around the middle value of the scale.
3.2.3.2 STEP 2: Select the Appropriate Statistical Test
In this chapter we are studying the confidence interval about the mean, and so we will select that test.
3.2.3.3 STEP 3: Calculate the Formula for the Statistical Test
You will recall (see Sect 3.1.5) that the formula for calculating the confidence interval about the mean is:
We discussed the procedure for computing this formula for the confidence interval about the mean using Excel earlier in this chapter The steps involved in using that formula are:
1 Use Excel’sẳCOUNT function to find the sample size.
2 Use Excel’sẳAVERAGE function to find the sample mean,X.
3 Use Excel’sẳSTDEV function to find the standard deviation, STDEV.
4 Find the standard error of the mean (s.e.) by dividing the standard deviation (STDEV) by the square root of the sample size, n.
5 Use Excel’s TINV function to find the lower limit of the confidence interval.
6 Use Excel’s TINV function to find the upper limit of the confidence interval.
3.2.3.4 STEP 4: Draw a Picture of the Confidence Interval About the Mean, Including the Mean, the Lower Limit of the Interval, the Upper Limit of the Interval, and the Reference Value Given in the Null Hypothesis, H 0 (We Will Explain Step 4 Later in the Chapter)
3.2.3.5 STEP 5: Decide on a Decision Rule
(a)If the reference value is inside the confidence interval, accept the null hypoth- esis, H0
(b) If the reference value is outside the confidence interval, reject the null hypoth- esis, H0, and accept the research hypothesis, H1
3.2.3.6 STEP 6: State the Result of Your Statistical Test
When utilizing the confidence interval for the mean, there are two potential outcomes, but only one can be deemed "true." Therefore, your findings will ultimately reflect one of these two results.
Either: Since the reference value is inside the confidence interval, we accept the null hypothesis, H0
Or: Since the reference value is outside the confidence interval,we reject the null hypothesis, H0, and accept the research hypothesis, H1
3.2.3.7 STEP 7: State the Conclusion of Your Statistical Test in Plain
Summarizing the results of a statistical test, particularly the confidence interval for the mean, can be challenging, especially when aiming for clarity and conciseness for readers without a statistics background This article aims to guide you through the process of articulating these findings effectively, ensuring that even those unfamiliar with statistical concepts can grasp the conclusions drawn from your analysis Throughout this book, you will find ample opportunities to practice and refine this essential skill.
Let’s set some basic rules for stating the conclusion of a hypothesis test.
Rule #1: Whenever you reject H0 and accept H1, you must use the word
“significantly” in the conclusion to alert the reader that this test found an important result.
Rule #2: Create an outline in words of the “key terms” you want to include in your conclusion so that you do not forget to include some of them.
Rule #3: Write the conclusion in plain English so that the reader can understand it even if that reader has never taken a statistics course.
Let’s practice these rules using the outpatient visits Excel spreadsheet that you created earlier in this chapter (OUTPATIENT3), but first we need to state the hypotheses for that clinic.
If the clinic wants to claim that the average number of outpatient visits during the past 12 months was 3.50 visits, the hypotheses would be:
The reference value of 3.50 falls within the 95% confidence interval for the mean of the data, allowing us to accept the null hypothesis (H0) This indicates that the average number of outpatient visits to the clinic over the past 12 months is indeed 3.50.
Objective: To state the result when you accept H 0
Result: Since the reference value of 3.50 is inside the confidence interval, we accept the null hypothesis, H0.
Let’s try our three rules now:
Objective: To write the conclusion when you accept H 0
In this chapter, we adhere to a fundamental guideline: if the reference value falls within the confidence interval, the term "significantly" cannot be used in our conclusions.
Rule #2: The key terms in the conclusion would be:
Rule #3: The average number of outpatient visits to the clinic during the past
Writing a conclusion when accepting the null hypothesis (H0) is straightforward, as it simply reflects the initial statement of the null hypothesis In contrast, articulating a conclusion upon rejecting H0 and accepting the alternative hypothesis (H1) is more complex To enhance your skills in this area, we will practice formulating conclusions through three illustrative case examples.
Objective: To write the result and conclusion when you reject H 0
In a recent time and motion study conducted by your organization, it was established that the average duration to complete a specific laboratory procedure is approximately 25 minutes, rounded to the nearest minute The study's findings serve as the basis for formulating relevant hypotheses regarding the efficiency and effectiveness of the procedure in question.
Suppose that your research yields the following confidence interval:
19 21 23 25 lower Mean upper Ref. limit limit Value
Result: Since the reference value is outside the confidence interval, we reject the null hypothesis and accept the research hypothesis
The three rules for stating the conclusion would be:
Rule #1: We must include the word “significantly” since the reference value of 25 is outside the confidence interval.
Rule #2: The key terms would be:
– average time needed to complete the procedure
– either “more than” or “less than”
Rule #3: The average laboratory time needed to complete this procedure was significantly less than 25 minutes, and it was probably closer to
The conclusion indicates that the procedure was completed in under 25 minutes, with a sample mean of just 21 minutes However, simply stating that the time is "significantly less than 25 minutes" fails to provide clarity on the exact difference between the sample mean and the 25-minute benchmark.
To make the conclusion clear, you need to add: “probably closer to 21 minutes” since the sample mean was only 21 minutes.
In a healthcare center, financial officers can assess the cost of patient services by analyzing a random sample of discharges from the previous year For instance, the average cost for patients with similar disorders who were discharged in the past year is approximately $5,642 This method allows for an estimation of the true cost of patient care based on historical data.
You want to practice your data interpretation skills on the hypothetical data which produces the confidence interval below:
The hypotheses for this test would be:
The null hypothesis posits that if the average cost observed for this sample closely aligns with $5,642, we can reasonably conclude that the actual cost of the procedure is indeed $5,642.
Suppose that your analysis produced the following confidence interval for this test:
$5,555 $5,584 $5,613 $5,642 lower Mean upper Ref. limit limit Value
Result: Since the reference value is outside the confidence interval, we reject the null hypothesis and accept the research hypothesis.
Rule #1: You must include the word “significantly” since the reference value is outside the confidence interval
Rule #2: The key terms would be:
– less or greater (depending on your result)
Rule #3: The average cost of patients discharged during the past year who were treated for a specific disorder was significantly less than $5,642, and was probably closer to $5,584.
Note that you need to use the word “less” since the sample mean of $5,584 was less than the reference value of $5,642.
CASE #3: The American College of Healthcare Executives (ACHE) has more than
The American College of Healthcare Executives (ACHE) boasts an international membership of 40,000 healthcare professionals and is well-regarded for its publication, Healthcare Executive, as well as its initiatives in career development and public policy To gauge the impact of professional relationships formed through ACHE, the organization conducts a survey among its members, asking them to express their agreement on a scale from 1 (Strongly Disagree) to 5 (Strongly Agree) This key question aims to assess the significance of these relationships in advancing their careers.
Suppose that you have been asked to use your Excel skills to determine the opinion of the sampled members.
Suppose that your research produced the following confidence interval for this survey item:
Result: Since the reference value is outside the confidence interval, we reject the null hypothesis and accept the research hypothesis
The three rules for stating the conclusion would be:
Rule #1: You must include the word “significantly” since the reference value is outside the confidence interval
Rule #2: The key terms would be:
Rule #3: The sample of members of ACHE significantly agreed that the profes- sional relationships they developed through ACHE membership have been important to their careers.
It is essential to emphasize that the findings indicate a significant relationship, highlighting the importance of the conclusions drawn without relying on specific numerical references This approach allows for a clearer understanding of the implications while maintaining clarity and coherence in the narrative.
If you want a more detailed explanation of the confidence interval about the mean, see Veney (2003).
This chapter concludes with three practice problems designed to enhance your skills in articulating research conclusions Additionally, the book offers numerous examples to assist you in crafting clear and precise conclusions for your research findings.