using excel to plot farrokh alemi, ph.d.. objectives given a set of data plot a control chart using...
TRANSCRIPT
![Page 1: Using Excel to Plot Farrokh Alemi, Ph.D.. Objectives Given a set of data plot a control chart using Excel software](https://reader035.vdocuments.us/reader035/viewer/2022080915/56649d8b5503460f94a72660/html5/thumbnails/1.jpg)
Using Excel to Plot
Farrokh Alemi, Ph.D.
![Page 2: Using Excel to Plot Farrokh Alemi, Ph.D.. Objectives Given a set of data plot a control chart using Excel software](https://reader035.vdocuments.us/reader035/viewer/2022080915/56649d8b5503460f94a72660/html5/thumbnails/2.jpg)
Objectives
Given a set of data plot a control chart using Excel software
![Page 3: Using Excel to Plot Farrokh Alemi, Ph.D.. Objectives Given a set of data plot a control chart using Excel software](https://reader035.vdocuments.us/reader035/viewer/2022080915/56649d8b5503460f94a72660/html5/thumbnails/3.jpg)
Data
Observations over time (see first two columns)
Upper and Lower control limits (see columns 3 and 4)
Pre (yellow) & post (white) Intervention period
Plot a chart with three lines designating the observed, the upper and the lower limits. Distinguish between pre- and post intervention periods
TimeObserved
valueUpper limit
Lower Limit
1 30 52.5 12.5
2 0 52.5 12.5
3 25 52.5 12.5
4 30 52.5 12.5
5 35 52.5 12.5
6 40 52.5 12.5
7 50 52.5 12.5
8 45 52.5 12.5
9 31 52.5 12.5
10 20 52.5 12.5
11 40 52.5 12.5
12 60 52.5 12.5
13 45 52.5 12.5
14 60 52.5 12.5
15 45 52.5 12.5
16 32 52.5 12.5
17 50 52.5 12.5
18 60 52.5 12.5
![Page 4: Using Excel to Plot Farrokh Alemi, Ph.D.. Objectives Given a set of data plot a control chart using Excel software](https://reader035.vdocuments.us/reader035/viewer/2022080915/56649d8b5503460f94a72660/html5/thumbnails/4.jpg)
Distinguish between before and after intervention periods
Create a series for the time period before intervention Select the range from C2 to
D8 and cut and paste it in two new columns E2 to F8.
Label E1 as upper limit before and F1 as lower limit before
Label C1 as upper limit after and D1 as lower limit after
![Page 5: Using Excel to Plot Farrokh Alemi, Ph.D.. Objectives Given a set of data plot a control chart using Excel software](https://reader035.vdocuments.us/reader035/viewer/2022080915/56649d8b5503460f94a72660/html5/thumbnails/5.jpg)
Select chart options1. Click on chart logo
2. Select first option of line charts
3. Enter data range B2:F19 or drag cursor from B2 to F19 to pick up this range
![Page 6: Using Excel to Plot Farrokh Alemi, Ph.D.. Objectives Given a set of data plot a control chart using Excel software](https://reader035.vdocuments.us/reader035/viewer/2022080915/56649d8b5503460f94a72660/html5/thumbnails/6.jpg)
Title, X-axis, Y-axis & legend
Enter plot title, X-axis title and Y-axis titles
Select legend folder and turn off the legend
![Page 7: Using Excel to Plot Farrokh Alemi, Ph.D.. Objectives Given a set of data plot a control chart using Excel software](https://reader035.vdocuments.us/reader035/viewer/2022080915/56649d8b5503460f94a72660/html5/thumbnails/7.jpg)
Select plot area
Select where to make the plot
See plot. The limits are confusing and need to be redone
Example of how to plot
020
4060
80
1 3 5 7 9 11 13 15 17
Time
Ob
se
rve
d v
alu
es
![Page 8: Using Excel to Plot Farrokh Alemi, Ph.D.. Objectives Given a set of data plot a control chart using Excel software](https://reader035.vdocuments.us/reader035/viewer/2022080915/56649d8b5503460f94a72660/html5/thumbnails/8.jpg)
Redo limits
1. Right click on the line until selected
2. Select format data series
3. Set marker to none. Set line color to red
4. Repeat for the after portion but this time set style to dashed
![Page 9: Using Excel to Plot Farrokh Alemi, Ph.D.. Objectives Given a set of data plot a control chart using Excel software](https://reader035.vdocuments.us/reader035/viewer/2022080915/56649d8b5503460f94a72660/html5/thumbnails/9.jpg)
Redo observation line
4. Delete gridline
1. Right click on the line for observed values. Select format data series
2. Set markers to automatic, press ok
3. Click on grid line until selected
![Page 10: Using Excel to Plot Farrokh Alemi, Ph.D.. Objectives Given a set of data plot a control chart using Excel software](https://reader035.vdocuments.us/reader035/viewer/2022080915/56649d8b5503460f94a72660/html5/thumbnails/10.jpg)
Final output
Example of how to plot
020
4060
80
1 3 5 7 9 11 13 15 17
Time
Ob
se
rve
d v
alu
es
![Page 11: Using Excel to Plot Farrokh Alemi, Ph.D.. Objectives Given a set of data plot a control chart using Excel software](https://reader035.vdocuments.us/reader035/viewer/2022080915/56649d8b5503460f94a72660/html5/thumbnails/11.jpg)