Hobbies And Interests
Things You'll Need
Instructions
Confirm your assumptions are correct. The assumptions for an x-bar chart are that your data are continuous and normally distributed. The variance should be constant, meaning there is not wide variation, and the groups should be independent.
Enter your data into Excel. If you are gathering data on the number of calls answered per month over one year by three operators, enter your data into Excel as follows:
The columns will look like this:
Time
January
February
March
April
May
June
July
August
September
October
November
December
Operator 1
48
50
55
47
41
61
64
40
48
44
57
51
Operator 2
51
54
45
47
54
48
49
48
55
53
51
49
Operator 3
52
55
54
54
56
54
56
53
55
54
50
52
All columns will be adjacent to each other.
Read the grid. Along the top of Excel are letters used to name the columns. In this example: A = Time, B = Operator 1; C = Operator 2 and D = Operator 3. Along the side are numbers used to name the rows. In this example: 2 = January, 3 = February, and so forth until 13 = December. Any particular number is located at a cell address, which is the intersection of a row number and a column letter. For example, the value "48" is found at cell addresses B2, B10, C7 and C9.
Calculate the average across each time period. You can make a column at address E1 and label it as Average and then type this formula into cell E2:
=AVERAGE (B2:D2).
All formulas in Excel are denoted by putting an equals sign in front of the formula. The range B2:D2 will give you the average for January; you'll have to adjust the formula for each row.
The result will look like this:
Average
50
53
51
50
50
55
56
47
53
50
53
51
Calculate the average of all the observations over all the time periods. This is known as the Grand Average. You can make another column at address F1 and label it as Grand Average, then in cell F2 you can type the formula =AVERAGE(B2:D13). This formula tells Excel to calculate the average for all the values on your spreadsheet. The result will look like this:
Grand Average
51.54
Calculate the standard deviation of all the observations. This is known as the grand standard deviation. Create a column at cell address G1 and label it Grand Standard Deviation. In cell G2, type the following formula: =STDEV(B2:E13). The result will look like this:
Grand Standard Deviation
4.48
Estimate the standard deviation for observations within each time period. If the number of observations across all time periods is the same, as in this example, this figure will be the same across all months. Create a new column and write the heading Estimated Standard Deviation in address H1. In cell H2 write the following formula:
=G2/SQRT(COUNT(B2:E2))
This formula takes the grand standard deviation and divides it by the square root of the number of observations in each time frame.
Calculate the upper control limit by using the grand average. Create a column called UCL in cell address I1. In cell address I2 type in the following formula:
=$F$2+1.96*H2
The dollar signs permit you to copy this formula for the lower control limits, without Excel auto-adjusting the cell addresses.
The upper control limit is the grand average plus a constant of 1.96 multiplied by the estimated standard deviation. If the number of observations is different over the different time periods, then the upper control limit will change, however, in this example they are all the same.
The result is:
UCL
55.93
Calculate the lower control limit. Create a column called LCL in cell address J1. In cell address J2 type in the following formula:
=$F$2-1.96*H2
The lower control limit is the grand average, which is found in cell F2 in the worksheet subtract a constant of 1.96, multiplied by the standard deviation for each time period.
The result is:
LCL
47.16