Mean
Themeanis the“arithmetic average”of a set of scores When my daughter was in the
fifth grade, she came home from school with a sad face and said that she didn’t get
“averages.”The book she was using described how to find the mean of a set of scores, and so I said to her:
Jennifer, frustrated by my playful response, insisted, "Dad, this is serious!" as I explained how to calculate the average by adding all the scores and dividing by the total number of entries.
“ 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 can easily find the correct answer, 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 forfinding the mean with your calculator looks like this:
XẳΣX n ð1:1ị © Springer International Publishing AG, part of Springer Nature 2018
T J Quirk, Excel 2016 in Applied Statistics for High School Students,
Excel for Statistics, https://doi.org/10.1007/978-3-319-89993-0_1
The Greek letter sigma (Σ) represents the concept of "sum," instructing you to add all the values indicated by the letter X and then divide the total by n, which signifies the count of numbers involved.
Suppose that you had these six test scores on an 7-item true-false quiz:
Tofind 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; a small standard deviation indicates that scores are closely clustered around the mean, while a large standard deviation signifies that scores are more widely dispersed The formula for calculating standard deviation, denoted as STDEV, is represented by the letter S.
The formula look complicated, but what it asks you to do is this:
1 Subtract the mean from each score
2 Then, square the resulting number to make it 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 Thefinal step is to take the square root of the number you found in step 4.
This article focuses on using Excel to calculate the standard deviation of a set of scores, rather than manually computing it with a calculator For example, applying Excel to the six numbers mentioned earlier yields a standard deviation (STDEV) of 1.47 For those interested in traditional methods, basic statistics books provide detailed examples of standard deviation calculations.
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 (s.e.), divide the standard deviation (STDEV) by the square root of n, where n represents the total number of observations in your data set For instance, in the example provided, the standard error is 0.60, which you can verify using a calculator.
If you want to learn more about the standard deviation and the standard error of the mean, see Agresti and Franklin (2013).
In this article, we will explore how to utilize Excel to determine the sample size, mean, standard deviation, and standard error of the mean based on a geometry test taken by a class of eight 9th graders at the end of the first term, with a total possible score of 50 points The hypothetical data used for this analysis is illustrated in Fig 1.1.
Fig 1.1 Worksheet Data for a Geometry Test
Sample Size, Mean, Standard Deviation,
Using the Fill/Series/Columns Commands
Objective: To add the student numbers 2–8 in a column underneath student #1
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 down arrow; see Fig.1.2)
Fig 1.2 Home/Fill/Series commands
The student numbers should be identified as 1–8, with 8 in cell A11.
Now, enter the Geometry Test Scores in cells B4: B11.
To enhance the professionalism of your computer screen's display, it's essential to learn how to widen column widths and center information within a group of cells Here’s a simple guide on how to achieve these formatting adjustments effectively.
Changing the Width of a Column
Objective: To make a column width wider so that all of the informationfits inside that column
If you look at your computer screen, you can see that Column B is not wide enough so that all of the informationfits inside this column To make Column B wider:
Click on the letter, B, at the top of your computer screen
Place your mouse pointer at the far right corner of B until you create a“cross sign” on that corner
Fig 1.3 Example of Dialogue Box for Fill/Series/Columns/Step Value/Stop Value commands
Left-click on your mouse, hold it down, and move this corner to the right until it is
“wide enough tofit all of the data”
Take yourfinger off the 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 B 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 A andColumn B.
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 on A3 and drag it to the right and down to highlight cells A3:B11 so that these cells appear in a darker color
At the top of your computer screen, you'll find a series of lines that are uniformly centered under the "Alignment" section, which is represented by the second icon at the bottom left of the Alignment box (refer to Fig 1.5).
Fig 1.4 Example of How to Widen the Column Width
Click on this icon to center the information in the selected cells (see Fig.1.6)
To simplify referencing Geometry Test Scores in your formulas, it's beneficial to name the data range instead of recalling specific cell locations (B4:B11) By naming this group of cells "Geometry," you can easily incorporate it into your calculations, although you can choose any name that suits your preference.
Fig 1.5 Example of How to Center Information
Centering Information in the Cells
Naming a Range of Cells
Objective: To name the range of data for the test scores with the name: Geometry
Highlight cells B4: B11 by left-clicking your mouse on B4 and dragging it down to B11
Formulas (top left of your screen)
Define Name (top center of your screen)
Geometry (type this name in the top box; see Fig.1.7)
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 B4:B11
Now, add the following terms to your spreadsheet:
Fig 1.7 Dialogue box for “ naming a range of cells ” with the name: Geometry
When using formulas in Excel, it's essential to start each function with an equal sign (ẳ) to indicate that you are entering a formula.
Finding the Sample Size Using the ẳ COUNT
Objective: Tofind 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 students in this class.
Finding the Mean Score Using the ẳ AVERAGE
Objective: Tofind the mean test scorefigure using theẳAVERAGE functionFig 1.8 Example of Entering the Sample Size, Mean, STDEV, and s.e Labels
This command should insert the number 23.125 into cell F9.
Finding the Standard Deviation Using
Objective: Tofind the standard deviation (STDEV) using theẳSTDEV function
This command should insert the number 14.02485 into cell F12.
Finding the Standard Error of the Mean
Objective: Tofind the standard error of the mean using a formula for these eight data points
This command should insert the number 4.958533 into cell F15 (see Fig.1.9).
Fig 1.9 Example of Using Excel Formulas for Sample Size, Mean, STDEV, and s.e.
It is essential to verify all figures in your spreadsheet throughout this book to ensure they are placed in the correct cells; otherwise, the formulas may not function properly.
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 decimal places in your document, 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 display until the option "Decrease Decimal" appears.
Click on this icononce and notice that the cells F9:F15 are now all in just two decimal places (see Fig.1.11)
Fig 1.10 Using the “ Decrease Decimal Icon ” to convert Numbers to Fewer Decimal Places
Now, click on any“empty cell”on your spreadsheet to deselect cells F9:F15.
Saving a Spreadsheet
Objective: To save this spreadsheet with the name: Geometry3
To save your spreadsheet for future access, first determine the storage location You have several options, including saving it to your computer's hard drive, a CD, or a flash drive If you're unsure how to save it on your computer, seek assistance for guidance.
To save a file, scroll through the left sidebar to select your desired location, such as "This PC" or "My Documents," and click on the chosen folder to complete the saving process.
File name: Geometry3 (enter this name to the right of File name; see Fig.1.12)Fig 1.11 Example of Converting Numbers to Two Decimal Places
Important note: Be very careful to save your Excelfile spreadsheet every few minutes so that you do not lose your information!
Printing a Spreadsheet
Objective: To print the spreadsheet
Use the following procedure when printing any spreadsheet.
Print Active Sheets (see Fig.1.13)
Fig 1.12 Dialogue Box of Saving an Excel Workbook File as “ Geometry3 ” in My Documents location
Print (top of your screen)
Thefinal spreadsheet is given in Fig1.14
Fig 1.13 Example of How to Print an Excel Worksheet
Using the File/Print/Print
Before concluding this chapter, let's explore formatting figures in a spreadsheet through two examples: first, displaying dollar amounts with two decimal places, and second, representing other figures with three decimal places.
Close your spreadsheet by: File/Save, then close your spreadsheet by: File/Close/, and open a blank Excel spreadsheet by using:
File/New/Blank Workbook (on the top left 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 formattingfigures in number format, three decimal places.
Formatting Numbers in Number Format (Three Decimal Places)
Objective: To formatfigures 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
Ensure the three figures are formatted in number style with three decimal places To deselect cells A4:A6, click on any blank cell Finally, close the file by navigating to File, then Close, and select "Don’t Save," as saving this practice problem is unnecessary.
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
In an advertising firm focused on evaluating television commercials, a panel of male college students aged 18-24 provided feedback on a potential TV ad for a new product This panel, consisting of participants willing to engage in online research studies, is essential for understanding the effectiveness of advertisements across different market segments The survey included ten items, with Item #10 specifically assessing purchase intent based on the TV ad Hypothetical data for this item reveal insights into the likelihood of this demographic to purchase the product after viewing the commercial, highlighting the importance of targeted advertising strategies.
To analyze the data effectively, utilize Excel to calculate the sample size, mean, standard deviation, and standard error of the mean Ensure to label each result clearly and round the mean, standard deviation, and standard error to two decimal places, applying the appropriate number format for these figures.
(b) Print the result on a separate page.
As a data analyst at Ford Motor Company, your task is to analyze survey data from female college students aged 18-24 regarding their preferences for features in the upcoming Ford Focus Utilizing Excel, you have compiled a table of hypothetical data for Item #12 from the survey, which will provide valuable insights into the importance of various features for this target demographic.
Fig 1.17 Worksheet Data for Chap 1: Practice Problem #1
To analyze the ratings effectively, utilize Excel to construct a table displaying the ratings Next, calculate the sample size, mean, standard deviation, and standard error of the mean for the data, ensuring to label each result clearly Round the mean, standard deviation, and standard error of the mean to three decimal places using number formatting Finally, present the results on a separate page for clarity and organization.
A spokesperson is an individual featured in advertisements across various media platforms, such as TV, radio, and online, aiming to persuade consumers to purchase a specific product or service For a spokesperson to be effective, they must be perceived as trustworthy, informative, sincere, and qualified by the target market This credibility is essential for potential customers to believe in the spokesperson's claims regarding the product or service being promoted.
Advertising research plays a crucial role in evaluating the impact of spokespersons on target market perceptions It focuses on how effectively a spokesperson influences audience views regarding both their credibility and the message conveyed about the product or service.
The semantic differential scale is a popular measurement tool in advertising research, featuring bipolar adjectives at each end of a 7-point rating scale For instance, respondents may evaluate a spokesperson on attributes such as trustworthy/untrustworthy and informative/uninformative In a case study involving female undergraduate college students aged 18–24, data analysis was conducted on their responses to a television ad for a new cosmetic product from a leading manufacturer Prior to analyzing actual data, hypothetical data was utilized to refine Excel skills for this analysis.
#12 of this survey to make sure that you can do this type of analysis correctly The data appear in Fig.1.19.
Fig 1.19 Worksheet Data for Chap 1: Practice Problem #3
To analyze the data effectively, create a table in Excel and calculate the sample size, mean, standard deviation, and standard error of the mean Ensure to label each result clearly and round the mean, standard deviation, and standard error to two decimal places using the number format for clarity.
(b) Print the result on a separate page.
Agresti, A.A and Franklin, C.F Statistics: The Art and Science of Learning from Data (3 rd ed.)Boston, MA: Pearson Education, Inc 2013.
To assess job satisfaction among teachers at a local elementary school, a random sample of five out of the total 32 teachers was selected using Excel This approach ensures a representative overview of the teachers' experiences and perspectives regarding their work environment The insights gathered from these interviews will provide valuable information to the school superintendent for enhancing teacher satisfaction and improving the overall school atmosphere.
To effectively select a random sample, it is essential to establish a "sampling frame," which is a comprehensive list of individuals from whom the sample will be drawn In this case, the sampling frame begins with the identification code (ID) assigned to each teacher, starting with the first teacher labeled as ID 1, followed by the second teacher as ID 2, and continuing sequentially until the last teacher, who is assigned ID 32.
Since this school has 32 teachers, your sampling frame would go from 1 to
32 with each teacher having a unique ID number.
We willfirst create the frame numbers as follows in a new Excel worksheet:
Creating Frame Numbers for Generating Random Numbers
Objective: To create the frame numbers for generating random numbers
To create frame numbers in column A from 1 to 32, use the Home/Fill commands as described in Section 1.4.1 of this book Ensure that the number 32 is placed in cell A35 If you need a refresher on the procedure, follow the outlined steps provided.
T J Quirk, Excel 2016 in Applied Statistics for High School Students,
Excel for Statistics, https://doi.org/10.1007/978-3-319-89993-0_2
Click on cell A4 to select this cell
Fill (then click on the“down arrow”next to this command and select)
Then, save thisfile as: Random29 You should obtain the result in Fig.2.3. Fig 2.1 Dialogue Box for Fill/Series Commands
Fig 2.2 Dialogue Box for Fill/Series/Columns/Step value/Stop value Commands
Now, create a column next to these frame numbers in this manner:
To format your spreadsheet correctly, use the Home/Fill command to populate frame numbers starting from cell B4 and ending at cell B35 Ensure that columns A and B are widened to accommodate all the content, and center the information within both columns Your final layout should resemble the example shown in Fig 2.4.
Fig 2.3 Frame Numbers from 1 to 32
You may be questioning the duplication of information in both Column A and Column B of your spreadsheet This approach ensures that you have precisely 32 frame numbers before you sort them into a random sequence, maintaining accuracy in your data organization.
Now, let’s add a random number to each of the duplicate frame numbers as follows:
Creating Random Numbers in an Excel Worksheet
(then widen columns A, B, C so that their labelsfit inside the columns; then center the information in A3:C35)
Next, hit the Enter key to add a random number to cell C4.
To use the RAND() function effectively, ensure that both an open parenthesis and a closed parenthesis are included after the command The RAND function generates a random number by referencing the cells to the left of the cell containing the RAND() command.
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 Click and drag the pointer down to cell C35 to complete the process.
Random Numbers Assigned to the Duplicate Frame
Then, click on any empty cell to deselect C4:C35 to remove the dark color highlighting these cells.
Now, let’s sort these duplicate frame numbers into a random sequence:
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.
These steps will produce Fig.2.8with the DUPLICATE FRAME NUMBERS sorted into a random order:
Fig 2.7 Dialogue Box for Data/Sort/RANDOM NO./Smallest to Largest Commands
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!
Because your objective at the beginning of this chapter was to select randomly
5 of this school’s 32 teachers for a personal interview, you now can do that by selecting the first five ID numbers in DUPLICATE FRAME NO column after the sort.
Although your first five random numbers will be different from those we have selected in the random sort that we did in this chapter, we would select these
five IDs of teachers to interview using Fig.2.9.
Keep in mind that the five ID numbers you generate through a random sort will differ from those shown in Fig 2.9, as Excel produces a unique random number each time the RAND() function 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.
2.4 Printing an Excel File So That All of the Information
Objective: To print afile so that all of the informationfits onto one page
This chapter includes three practice problems that involve sorting random numbers related to specific datasets: 63 Honda dealers, 114 counties in Missouri, and 76 key accounts To ensure these files fit onto a single printed page, it is essential to format them appropriately, as they may be too large otherwise.
Let’s create a situation where thefile does notfit onto one printed page unless you format itfirst to do that.
Go back to thefile you just created, Random 33, and enter the name: Jennifer into cell: A50.
If you printed thisfile now, the name,Jennifer, would be printed onto a second page because it“dribbles over”outside of the page range for thisfile in its current format.
To ensure that all information, including the name Jennifer, fits onto a single page when printing the file, you will need to adjust the page format by following specific steps.
Page Layout (top left of the computer screen)
(Notice the“Scale to Fit”section in the center of your screen; see Fig.2.10)
To adjust the page size to 95%, click the down arrow next to 100% on your screen Be aware that the name "Jennifer" appears on the second page, as it is located below the horizontal dotted line, which indicates the printable area of the document, as shown in Fig 2.11.
To reduce the size of the worksheet to 90% of its normal size, simply press the down arrow on the right again to repeat the "scale change steps." As shown in Fig 2.12, the "dotted lines" now appear below Jennifer's name, indicating that all the information, including her name, is formatted to fit on a single page for printing.
Print thefile Does it allfit onto one page? It should (see Fig.2.13).
Fig 2.12 Example of Scale Reduced to 90% with “ Jennifer ” to be printed on the fi rst page (note the dotted line below Jennifer on your screen)
Fig 2.11 Example of Scale Reduced to 95% with “ Jennifer ” to be Printed on a Second Page
Spreadsheet of 90% Scale to Fit
End-of-Chapter Practice Problems
1 Suppose that your advertising agency represents 63 Honda dealers in your state and that you have been asked to perform a“dealer satisfaction phone survey”of
15 of 63 car dealers to obtain their ideas about how your agency can advertise Hondas more effectively.
(a) Set up a spreadsheet of frame numbers for these dealers with the heading: FRAME NUMBERS using the Home/Fill commands.
To organize your data effectively, first create a column titled "Duplicate Frame Numbers" adjacent to your original frame numbers Next, in a new column to the right of the duplicate frame numbers, utilize the =RAND() function to generate random numbers corresponding to each frame number Ensure that the format of this column is set 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 spreadsheetfits onto one page
(f) Circle on your printout the I.D number of thefirst 15 dealers that you would call in your phone survey
It's important to note that each time the RAND() function is used in Excel, it generates a unique random order of dealer ID numbers Consequently, the sequence of random numbers provided in this Excel Guide will differ from the one you generate, which is completely normal and expected.
To conduct a random sample of 10 counties from the 114 counties in Missouri for a political pollster's phone survey on voting preferences, you can utilize data from the U.S Census Bureau This survey aims to gather insights from registered voters about their preferences in the upcoming election It's noteworthy that Missouri is one of the 50 states in the U.S., which collectively has a total of 3,140 counties.
(a) Set up a spreadsheet of frame numbers for these counties 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 data organization, create a new column titled "Random Number" next to the duplicate frame numbers Utilize the =RAND() function to generate random numbers for each entry in the duplicate frame numbers 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 spreadsheetfits onto one page
(f) Circle on your printout the I.D number of the first ten counties that the pollster would call in his phone survey
The Sales department aims to conduct a customer satisfaction survey targeting 20 of the company's 76 key accounts A key account is defined by the Sales Vice-President as any customer who has made purchases totaling at least $30,000 within the last 90 days.
(a) Set up a spreadsheet of frame numbers for these customers with the heading: FRAME NUMBERS.
To organize your data, first create a column labeled "Duplicate Frame Numbers" to the right of the original frame numbers Next, add another column titled "Random Number" adjacent to the duplicate frame numbers, and utilize the =RAND() function to generate random numbers for each entry Finally, adjust the formatting of the random numbers column to display three decimal places for each value.
(d) Sort the duplicate frame numbers and random numbers into a random order (e) Print the result so that the spreadsheetfits onto one page
(f) Circle on your printout the I.D number of thefirst 20 customers that the Sales Vice-President would call for his phone survey.
U.S Census Bureau Census 2000 PHC-T-4 Ranking tables for counties 1990 and 2000 Retrieved from http://www.census.gov/population/www/cen2000/briefs/phc-t4/tables/tab01.pdf
Con fi dence 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 intervalfirst.
Con fi dence Interval About the Mean
How to Estimate the Population Mean
Objective: To estimate the population mean,μ
The population mean represents the average among all individuals within a target demographic For instance, if we wanted to assess the preferences of adults aged 25–44 regarding a new flavor of Ben & Jerry’s ice cream, surveying every individual in the U.S within that age range would be impractical due to the excessive time and costs involved in such a study.
To estimate the mean of an entire population efficiently, we utilize a sample of individuals rather than testing everyone This approach, known as inferential statistics, allows us to draw conclusions about the population mean based on the sample mean, ultimately saving both time and money.
T J Quirk, Excel 2016 in Applied Statistics for High School Students,
Excel for Statistics, https://doi.org/10.1007/978-3-319-89993-0_3
In business research, we analyze a sample of individuals, characterized by its size (n), mean (X), and standard deviation (STDEV) These statistics are essential for estimating the population mean through a specific testing method.
“confidence interval about the mean.”
Estimating the Lower Limit and the Upper
of the 95% Con fi dence Interval About the Mean
The theoretical background of this test is beyond the scope of this book, and you can learn more about this test from studying any good statistics textbook (e.g Levine
2011) but the basic ideas are as follows.
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" for our analysis, aiming for a "95% confidence" that the population mean falls within this interval This approach allows us to make informed statistical statements regarding the population's characteristics.
“ We are 95% con fi dent that the population mean in miles per gallon (mpg) for the Chevy Impala automobile is between 26.92 miles per gallon and 29.42 miles per gallon ”
When designing a billboard for this car, we can confidently state that it achieves 28 miles per gallon (mpg) since this figure falls within the 95% confidence interval of our research, which ranges from 26.92 to 29.42 mpg While the exact population mean remains unknown, we are assured that it lies within this specified range.
But we are only 95% confident that the population mean is inside this interval, and 5% of the time we will be wrong in assuming that the population mean is
In business research, we typically aim for a 95% confidence level in our assumptions, which is a standard yet arbitrary benchmark While we could opt for varying levels of confidence—such as 80%, 90%, or even 99%—this book will consistently adhere to the 95% standard This approach eliminates any uncertainty regarding the confidence level in the problems presented throughout the book, ensuring clarity and consistency in our results.
So how do wefind the 95% confidence interval about the mean for our data?
In words, we willfind this interval this way:
To calculate the confidence interval, start with the sample mean (X) For the upper limit, add 1.96 times the standard error of the mean (s.e.) to the sample mean 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 sample's standard deviation (STDEV) by the square root of the sample size (n).
In mathematical terms, the formula for the 95% confidence interval about the mean is:
In calculating the confidence interval, the term "sign" refers to "plus or minus." To determine the upper limit, you add 1.96 times the standard error (s.e.) to the mean, while for the lower limit, you subtract 1.96 times the s.e from the mean Essentially, the expression 1.96 s.e signifies multiplying 1.96 by the standard error of the mean, which is a crucial component of the confidence interval formula.
Note:We will explain shortly where the number 1.96 came from.
Let’s try a simple example to illustrate this formula.
Estimating the Con fi dence Interval for the Chevy
Impala in Miles Per Gallon
If Chevy Impala owners were to monitor their mileage alongside the gallons of gas consumed for two separate tanks, the results would provide valuable insights into the vehicle's fuel efficiency and performance.
A study of 49 vehicle owners revealed an average fuel efficiency of 27.83 miles per gallon (mpg), with a standard deviation of 3.01 mpg The standard error of the mean was calculated to be 0.43 mpg, derived from dividing the standard deviation by the square root of the sample size.
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 research study would, therefore, be the following:
“ We are 95% con fi dent that the population mean for the Chevy Impala is somewhere between 26.99 mpg and 28.67 mpg ”
Based on our data, we can confidently state that this car achieves 28 miles per gallon (mpg), as this figure falls within the 95% confidence interval for the population mean.
You are probably asking yourself: “Where did that 1.96 in the formula come from?”
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 population data follows a "normal distribution," resembling a "normal curve" if tested across the entire population This curve, similar in shape to the Liberty Bell outside Independence Hall in Philadelphia, is symmetric; when divided in half and folded, each side aligns perfectly with the other.
Integral calculus is not the focus of this discussion, but we aim to determine the lower and upper limits of population data within a normal curve, encompassing 95% of the area under this curve For research studies involving more than 40 participants, these limits are calculated as plus or minus 1.96 times the standard error of the mean (s.e.) from the sample This calculation provides the boundaries of our confidence interval For further insights on this topic, refer to a reputable statistics book, such as Salkind (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.
You might be wondering if the value in the confidence interval for the mean is always 1.96 The answer is no, and we will clarify the reasons behind this shortly.
Finding the Value for t in the Con fi dence
Objective: Tofind 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) For the upper limit, add the product of the t-value and the standard error (s.e.) to the sample mean Conversely, for the lower limit, subtract the product of the t-value and the s.e from the sample mean The t-value can be found in Appendix E of this book.
Objective: Tofind the value of t in the t-table in Appendix E
Before we get into an explanation of what is meant by“the value of t,”let’s give you practice infinding the value of t by using the t-table in Appendix E.
To effectively utilize Appendix E for your research study, focus on the first column labeled "sample size n" to locate the critical value of t, as this chapter's test is referred to as the "confidence interval about the mean test."
To determine the value of t, locate the sample size in the first column of the table Next, move to the right to find the corresponding t value in the "critical t column," which is used for calculating the 95% confidence interval for the mean.
For example, if you have 14 people in your research study, the value of t 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.
The "critical t column" in Appendix E indicates the t value necessary for achieving 95% confidence in your statistical results This book assumes your goal is to maintain this level of confidence in your tests Consequently, the t value provided in the t-table of Appendix E should be utilized when calculating the 95% confidence interval for the mean.
Now that you know how tofind the value of t in the formula for the confidence interval about the mean, let’s explore how youfind this confidence interval usingExcel.
Using Excel ’ s TINV Function to Find the Con fi dence
Objective: To use the TINV function in Excel to find the confidence interval about the mean
When you use Excel, the formulas forfinding 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 this formula, the “* symbol” indicates that Excel should perform multiplication, representing the term "times" in multiplication language Additionally, as mentioned in Chapter 1, "n" denotes the sample size, while "s" represents the sample size minus one.
You will also recall from Chap.1that the standard error of the mean, s.e., equals the STDEV divided by the square root of the sample size,n(See Sect.1.3).
Let’s try a sample problem using Excel tofind the 95% confidence interval about the mean for a problem.
General Motors claims that the new Chevy Impala achieves a fuel efficiency of 28 miles per gallon (mpg), which they intend to promote on a billboard located at the Vandeventer entrance to Route 44 in St Louis This figure of 28 mpg serves as the "reference value" for the vehicle.
At Ford Motor Co., you aim to verify a specific claim by gathering data and employing a two-sided 95% confidence interval to assess the mean This statistical approach will help determine whether the claim is substantiated by research evidence.
Using Excel to Find the 95% Con fi dence Interval
Objective: To analyze the data using a two-side 95% confidence interval about the mean
In a research study involving new car owners, participants were asked to monitor their mileage over two tanks of gas and record the average miles per gallon achieved The findings are illustrated in Fig 3.1.
To analyze the data effectively, create a spreadsheet in Excel and calculate the sample size (n), mean, standard deviation (STDEV), and standard error of the mean (s.e.) using the specified cell references.
Enter the other mpg data in cells A7: A30
To enhance the appearance of your table, start by selecting cells A6:A30 and formatting them to display numbers with one decimal place, ensuring they are centered in Column A Next, adjust the widths of columns A and B to be twice as wide as the original width of column A Finally, expand column C to three times the original width of column A for a more professional look.
Fig 3.1 Worksheet Data for Chevy Impala (Practical Example)
B26: Draw a picture below this confidence interval
B29: lower (then right-align this word)
B30: limit (then right-align this word)
C28: ‘ -–28 -–28.17 -–(note that you need to begin cell C28 with asingle quotation mark(‘) to tell Excel that this is alabel, and not a number)
D28: ‘ - (notice the single quotation mark at the beginning) E28: ‘29.42 (note the single quotation mark)
Now, align the labels underneath the picture of the confidence interval so that they look like Fig.3.3.
Fig 3.2 Example of Chevy Impala Format for the Con fi dence Interval About the Mean Labels
Next, name the range of data from A6:A30 as: miles
D7: Use Excel tofind the sample size
D10: Use Excel tofind the mean
D13: Use Excel tofind the STDEV
Now, you need tofind 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.
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 mean value is represented by D10, while D16 indicates the standard error of the mean According to the provided formula, the lower limit of the confidence interval is calculated to be 26.92 Figure 3.3 illustrates an example of how to visually represent a confidence interval around the mean result.
F23: ẳD10ỵTINV 1ð :95;24ị*D16 no spaces between symbolsð ị
The calculated upper limit of the confidence interval is 29.42 To ensure clarity in your Excel spreadsheet, format the mean, standard deviation, standard error of the mean, and both the lower (26.92) and upper limits of the confidence interval to two decimal places Be aware that if you print the spreadsheet now, the lower limit and upper limit may extend onto a second page due to the current formatting, which does not accommodate the data within a single page.
To adjust the size of your spreadsheet, utilize Excel's "Scale to Fit" feature as outlined in Chapter 2, Section 2.4 Set the scaling to 95% of the current size through the Page Layout menu Once completed, observe that the dotted line next to the measurements 26.92 and 29.42 indicates that these dimensions will now fit onto a single printed page (refer to Figure 3.4).
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
28 mpg given in the claim that the company wants to make about the car’s miles per gallon performance.
Now, let’s write the conclusion to your research study on your spreadsheet:
C33: Since the reference value of 28 is inside
C34: the confidence interval, we accept that
C35: the Chevy Impala does get 28 mpg.
Fig 3.4 Result of Using the TINV Function to Find the Con fi dence Interval About the Mean
The research study confirmed that the Chevy Impala achieved an average fuel efficiency of 28 miles per gallon, with the study recording an average of 28.17 miles per gallon (refer to Fig 3.5) The findings have been documented in the spreadsheet saved as CHEVY7.
Hypothesis Testing
Hypotheses Always Refer to the Population
or Events That You Are Studying
Thefirst step is to understand that our hypotheses always refer to thepopulationof people under study.
When studying 18–24 year-olds in St Louis, it is essential to select a representative sample from this age group The goal is to ensure that the findings from our study can be generalized to all individuals aged 18–24 in St Louis, rather than being limited to just those in our sample Proper sampling methods play a crucial role in achieving this objective.
We aim to study the population of 18–24 year-olds in St Louis, focusing specifically on a selected group known as the sample.
Our sample sizes usually consist of only a small number of individuals, and we focus on the results primarily to understand how well they can be generalized to the broader population of interest.
That is why our hypotheses always refer to the population, and never to the sample of people 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 thepopulation 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
The two main hypotheses in statistical analysis are the null hypothesis (H0) and the research hypothesis (H1), which is also known as the alternative 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, mirroring the concept of a null hypothesis, which posits that the defendant is innocent Conversely, the research hypothesis asserts the defendant's guilt, highlighting the fundamental principle of justice that safeguards the rights of the accused.
In Missouri, the state slogan "Show me" reflects the residents' skepticism, emphasizing that they require proof over mere words This attitude signifies a belief that actions are a more reliable indicator of truth than spoken claims, highlighting the importance Missourians place on demonstrating integrity through behavior.
In hypothesis testing, the goal is to determine which of the two competing statements—the null hypothesis or the research hypothesis—will be accepted as true while rejecting the other.
In business research, rating scales are commonly employed to assess individuals' attitudes toward a company, its products, or their purchasing intentions These scales typically consist of 5, 7, or 10 points, although various other scale values may also be utilized.
3.2.2.1 Determining the Null Hypothesis and the Research Hypothesis
When Rating Scales Are Used
Here is a typical example of a 7-point scale in attitude research in customer satisfaction studies (see Fig.3.6):
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 above example, since 4 is the number in the middle of the scale (i.e., three numbers are below it, and three numbers are above it), our hypotheses become:
In our statistical analysis of the attitude scale item, a population mean near 4 suggests that we accept the null hypothesis, indicating that our new car purchase experience was neutral, neither positive nor negative.
If our statistical test shows that the population mean significantly differs from 4, we reject the null hypothesis and accept the research hypothesis.
“The new car purchase experience was significantly positive” (this is true whenever our sample mean is significantly greater than our expected population mean of 4). or
“The new car purchase experience was significantly negative”(this is accepted as true whenever our sample mean is significantly less than our expected population mean of 4).
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.
Fig 3.6 Example of a Rating Scale Item for a New Car Purchase (Practical Example)
A business researcher must determine whether to accept the null hypothesis or the research hypothesis as true based on the data collected in their study.
Let’s try some examples of rating scales so that you can practicefiguring 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 not equal to 3 on this 5-point scale (i.e the“middle”of the scale is 3).
2 The null hypothesis is 4, and the research hypothesis is not equal to 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 not equal to 5.5 on this 10-point scale (i.e., the“middle”of the scale is 5.5 since there arefive numbers below 5.5 andfive numbers above 5.5).
Fig 3.7 Examples of Rating Scales for Determining the Null Hypothesis and the ResearchHypothesis
As another example, Holiday Inn Express in its Stay Smart Experience Survey uses 4-point scales where:
On this scale, the null hypothesis is: μ ẳ2.5 and the research hypothesis is: μ6ẳ2.5, because there are two numbers below 2.5, and two numbers above 2.5 on that rating scale.
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 Con fi dence 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 focus on the midpoint of the scale For instance, in a 7-point scale where 1 indicates "poor" and 7 signifies "excellent," the hypotheses should center around the middle values 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 the confidence interval about the mean is:
In this chapter, we outlined the procedure for calculating the confidence interval for the mean using Excel, detailing the essential steps involved in applying the formula effectively.
1 Use Excel’sẳCOUNT function tofind the sample size.
2 Use Excel’sẳAVERAGE function tofind the sample mean,X.
3 Use Excel’sẳSTDEV function tofind 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 tofind the lower limit of the confidence interval.
6 Use Excel’s TINV function tofind 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
3.2.3.5 STEP 5: Decide on a Decision Rule
(a) If the reference value is inside the confidence interval, accept the null hypothesis, H 0
(b) If the reference value is outside the confidence interval, reject the null hypothesis, H 0 , and accept the research hypothesis, H 1
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 considered accurate.
Either: Since the reference value is inside the confidence interval,we accept the null hypothesis, H 0
Or: Since the reference value is outside the confidence interval,we reject the null hypothesis, H 0 , and accept the research hypothesis, H 1
3.2.3.7 STEP 7: State the Conclusion of Your Statistical Test in Plain English!
Summarizing the results of a statistical test into clear and concise language can be challenging, especially when aiming to communicate effectively with individuals who lack a statistics background, such as your boss This article will guide you through the essential steps to craft a coherent conclusion regarding the confidence interval for the mean test, ensuring that the information is both accessible and accurate Throughout this book, you will find ample practice opportunities to master this crucial skill.
Let’s set some basic rules for stating the conclusion of a hypothesis test.
Rule #1:Whenever you reject H 0 and accept H 1 , you must use the word“signifi- cantly” 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.
In this section, we will apply the statistical rules using the Chevy Impala Excel spreadsheet created earlier First, we need to establish the hypotheses related to the vehicle's fuel efficiency, specifically focusing on the claim that the Chevy Impala achieves 28 miles per gallon.
The reference value of 28 mpg falls within the 95% confidence interval for the data collected, leading us to accept the null hypothesis (H0) for the Chevy Impala, confirming that the vehicle achieves an average fuel efficiency of 28 mpg.
Objective: To state the result when you accept H 0
Result: Since the reference value of 28 mpg is inside the confidence interval, we accept the null hypothesis, H 0
Let’s try our three rules now:
Objective: To write the conclusion when you accept H 0
According to Rule #1, when the reference value falls within the confidence interval, the term "significantly" cannot be applied in the conclusion This principle is consistently applied throughout this chapter for all related problems.
Rule #2: The key terms in the conclusion would be:
Rule #3: The Chevy Impala did get 28 mpg.
Writing a conclusion after accepting the null hypothesis (H0) is straightforward, as it reflects the initial statement made in the null hypothesis Conversely, crafting a conclusion upon rejecting H0 and accepting the alternative hypothesis (H1) presents more challenges To enhance understanding, we will practice formulating such conclusions through three illustrative case examples.
Objective: To write the result and conclusion when you reject H 0
CASE #1: Suppose that an ad inBusiness Weekclaimed that the Ford Escape Hybrid got 34 miles per gallon The hypotheses would be:
Suppose that your research yields the following confidence interval:
30 31 32 34 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 34 is outside the confidence interval.
Rule #2: The key terms would be:
–either“more than”or“less than”
Rule #3: The Ford Escape Hybrid got significantly less than 34 mpg, and it was probably closer to 31 mpg.
The conclusion indicates that the miles per gallon (mpg) was below 34, with a sample mean of just 31 mpg Additionally, it's important to recognize that simply stating a result is "significantly less than" the null hypothesis is insufficient; a more comprehensive interpretation is necessary.
34 mpg,”because that does not tell the reader “how much less than 34 mpg”the sample mean was from 34 mpg To make the conclusion clear, you need to add:
“probably closer to 31 mpg”since the sample mean was only 31 mpg.
CASE #2: Suppose that you have been hired as a consultant by the St Louis
The St Louis Symphony Orchestra (SLSO) recently conducted an Internet survey to gather insights from attendees of a concert held at Powell Symphony Hall last month Focused on enhancing data analysis skills, the survey specifically examined responses to Question #7, as illustrated in Figure 3.8.
The hypotheses for this one item would be:
The null hypothesis posits that if the mean score for this survey question is not significantly different from 4 on the rating scale, it indicates that attendees were neither satisfied nor dissatisfied with their SLSO concerts The analysis yielded a confidence interval for this survey item, reflecting the overall sentiment of the attendees.
1.8 _2.8 _3.8 4 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:
–either satisfied or dissatisfied (since the result is significant)
Fig 3.8 Example of a Survey Item Used by the St Louis Symphony Orchestra (SLSO)
Rule #3: Attendees were significantly dissatisfied, overall, on last month’s Internet survey with their experiences at concerts of the SLSO.
Note that you need to use the word“dissatisfied”since the sample mean of 2.8 was on the dissatisfied side of the middle of the rating scale.
The recent Guest Satisfaction Survey results for the Marriott Hotel at the St Louis Airport revealed valuable insights from last week’s customers, highlighting areas for improvement and strengths in service delivery.
This item would have the following hypotheses:
Suppose that your research produced the following confidence interval for this item on the survey:
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:
–either“positive”or“negative”(we will explain this)
Rule #3: Customers at the St Louis Airport Marriott Hotel last week rated their check-in speed in a survey as significantly positive.
Fig 3.9 Example of a Survey Item from Marriott Hotels
In English, it is uncommon to use the phrase "significantly excellent," as something is either excellent or it is not, without the need for modifiers Additionally, since the mean rating for check-in speed was 5.8, which is notably higher than 5.5, it is appropriate to describe this result as "significantly positive."
This chapter concludes with three practice problems designed to enhance your skills in articulating research conclusions Additionally, this book offers numerous examples to assist you in crafting clear and precise conclusions for your findings.