Get documents about "Excel Chart Blank Template
W
Description
Excel Chart Blank Template document sample
Document Sample
Standard Celeration Chart MS Excel Template Instructions
ASSESSMENT DATA ENTRY
[1] Click on the "Progress Record" tab at the bottom of the screen.
[2] Enter the name of the behaver and the Target MC at the top of the page in the places noted.
This information will appear on the finished chart.
[3] Enter the date of the first Sunday at the top top of the "date" column.
The name of the day of the week corresponding to that date will appear in the column to the left.
If you don't know or don't care about the dates per se, just enter "9/1" or any other date.
All other dates will be calculated automatically. Don't try and enter dates in any other place.
[4] Enter the assessment time (in minutes), the acceleration count, the deceleration count,
and the total possible count in the spaces provided.
[5] Enter any comments you wish to describe special conditions or events on a day.
These comments will not appear on the chart or effect charting in any way, but can provide a useful
record of what might have influenced your assessments.
Notes: • The record floor, acceleration frequencies, deceleration frequencies, and record ceilings will
be calculated automatically. Don't try to enter those values directly.
• If any of the counts (correct, error, total) are left blank,
• Leave the row blank for any ignore or no chance day. It will be left blank on the chart.
• Any day for which there is no entered assessment time will be left blank on the chart, even if counts are
provided. If you wish to remove a data point from the chart temporarily, simply delete the
assessment time for that day (the counts can be left intact).
PHASE CHANGE INFORMATION and NOTES you want to appear on the chart
Comments recorded directly on the "Progress Record" sheet are not shown on the chart. This section of
the worksheet allows you to enter information that will be shown on the chart, and to position the note
wherever you wish. Space is provided to record up to 50 phase changes or comments.
[1] Click on the "Notes & Phase Changes" tab at the bottom of the screen.
[2] Enter the date on which the phase change occurred (i.e., the first day of the new phase), or the date
that corresponds to the day-line on the chart where you want the note to appear. Check the date on the
Progress Record sheet, if necessary, to make sure this date will correspond to the proper part of the
progress record.
[3]
Enter the numbers that represent the frequency line(s) that correspond to how low and high on the
chart you wish the phase change line to extend. If you want to position a comment on the chart
without a phase change line, enter the same frequency number for both the high and low numbers.
[4] Enter the phase change description or comment you want to appear on the chart.
Notes: • Phase change lines will be drawn as vertical lines from the "low point" to the "high point" you specified
in step #3, above, and a short horizontal line will be drawn from the top of that vertical line to the right to
mark the phase change.
• By convention, phase change lines are drawn 1/2 day before the first day of the next phase (so there will
be no confusion as to which phase any data point falls. Therefore, Excel subtracts 1/2 day from the date
you specified in step #2, above, to fix the position of the vertical line. If you want to have the line fall
precisely on a given day, do the following: (a) select the cell where the date should be entered for the
comment (step #2, above); (b) enter an equal sign ("="); (c) click on the "Progress Record" tab at the
bottom of the worksheet; (d) select the date of the data point where you want the comment to appear; (e)
press the "enter" or "return" key; (f) position the cursor at the end of the "edit box" at the top of the
worksheet; (g) type "+.5"; (h) press enter. This sequence of steps will add back the 1/2 day that Excel
subtracts from the date you entered.
To View/Modify the STANDARD CHART
Whenever data are entered on the progress record sheet, it is charted on the Standard Chart.
To see the chart, click on the "Standard Chart" tab at the bottom of the worksheet.
Instructions Standard Chart Template Instructrions 1
Notes: •
When the chart appears, a dialogue box will appear stating that "negative or zero values" cannot be
plotted. That simply means that there are days in the progress record that have no data (that will nearly
always be the case). Just click on "OK" and the dialogue box will disappear. Occasionally, other
dialogue boxes will also appear. Just click on "OK" until the completed chart is drawn.
• Most of the standard charting conventions are followed by the template: dashed lines represent the
record floor and ceiling; dots represent acceleration targets; x's represent deceleration targets;
successive data points for corrects and errors are connected with lines, but no lines are drawn across no-
chance or ignore days).
• Frequencies based on a count of zero are automatically charted just below the record floor. They are
not charted as question-marks, however. If you wish to use question marks, you will need to make the
changes in a graphics program (just copy the chart and paste it into your graphics program), or use the
Excel procedures for changing individual data point symbols and substituting standard symbols for
special symbols of your own choosing (see the Excel manual).
• Notes and comments, including the description of the behaver and assessment target, can be moved
to any point on the chart. Just click on the note or comment and drag it to the desired postion.
• If the behaver's name and target descriptions do not fully appear on the chart click on their text
boxes and resize them by "pulling" on the handles of the box.
• Any element of the chart can be modified, revised, or reformatted using the standard Excel commands.
In order to preserve the standard nature of the chart, however, do not resize or re-lable the chart
scales.
• Additional notes and comments can be added to the chart by simply typing the comment when no
other text element of the chart is selected. After typing the comment, press "return" or "enter" and the
comment will appear on the chart (usually somewhere close to the center of the chart). Grab the
comment's boarder and drag it to wherever you want it to appear. You can also reformat the comment
(e.g., font size, syle) using the standard Excel commands.
• More extensive reformating of the chart can be accomplished by copying the Excel chart into your
favorite graphics program.
• The chart produced by Excel will be proportionately correct for the Standard Chart, but it will only include
the 6 main "cycles" ranging from frequencies of 0.001 to 1000. It will not include the extra 1/4 cycle
that extends the chart down to a period of 24 hours per day. To produce a final chart that has all the
proper features, copy the data from the Excel chart and paste it onto a prepared standard chart template
in your favorite graphics program.
If you need to change basic features of this template
• The template is "protected" so you can only enter data into selected cells. The underlying formulas
and structures that calculate the finished data and create the chart cannot be inadvertantly changed.
• If you want to change any protected portion of the template, remove the protection by: (a) select
"Tools>Protection>Remove Protection;" (b) when it asks you for the password, type "clear." You can
now change any part of the template you like.
• After making any changes you want in the template, it is suggested that you reinstate the protection.
Select "tools>protection>protect sheet." Enter whatever password you want, but be sure to remember
what it is in case you wish to make other changes in the future.
If you need to add celeration lines to the chart
* Click the Celeration Finder tab and read the instructions in the blue area below the table.
Instructions Standard Chart Template Instructrions 2
Type name of behaver here. This n
Name of Behaver: progress record and on the standar
Type description of target moveme
Target: information appears on the progres
standard chart.
Enter the date of the first Sunday p
Chart 20-Jan-2002 year. This is the chart synchroniza
Synchronization the school year. The last date on th
days from the beginning chart date
Date the start of your second half of the
All the "no count" frequencies on th
Zero-count multiple: 0.8 equal to some arbitrary point below
"0.80" of the record floor because it
appeal, but any other number migh
some prefer "0.50" (half the value o
that places the no-count frequencie
the next non-zero value is above th
"0.9999" (or some other very small
floor) since mathematically, anythin
arbitrarily close to the record floor.
This option changes the font color o
the progress record. If the accelera
acceleration cell is a factor of (X) tim
the previous three points averaged
be green, where (X) is the value in
Green acceleration essentially remove this function.
multiplier 1.1
This option changes the font color o
the progress record. If the accelera
acceleration cell is a factor of (X) tim
the previous three points averaged
be red, where (X) is the value in thi
Red acceleration essentially remove this function.
multiplier 0.8
This option changes the font color o
the progress record. If the decelera
deceleration cell is a factor of (X) tim
the previous three points averaged
be green, where (X) is the value in
Green deceleration essentially remove this function.
multiplier 0.8
This option changes the font color o
the progress record. If the decelera
deceleration cell is a factor of (X) tim
the previous three points averaged
be red, where (X) is the value in thi
Red deceleration essentially remove this function.
multiplier 1.1
Type name of behaver here. This name appears on the
progress record and on the standard chart.
Name of Behaver:
Type description of target movement cycle here. This
information appears on the progress record and on the
standard chart.
Movement Cycle:
Enter the date of the first Sunday prior to the start of the school
year. This is the chart synchronization date for the first half of
the school year. The last date on this chart is a Sunday, 140
days from the beginning chart date. Use this Sunday date as
the start of your second half of the year chart.
All the "no count" frequencies on the standard chart are set
equal to some arbitrary point below the record floor. I choose
"0.80" of the record floor because it had a reasonable aesthetic
appeal, but any other number might be justified. For example,
some prefer "0.50" (half the value of the record floor) because
that places the no-count frequencies as far below the floor as
the next non-zero value is above the floor; others prefer
"0.9999" (or some other very small decrement of the record
floor) since mathematically, anything below the floor is still
arbitrarily close to the record floor.
This option changes the font color of the acceleration count on
the progress record. If the acceleration datum in the
acceleration cell is a factor of (X) times more than the data from
the previous three points averaged, the font color in this cell will
be green, where (X) is the value in this cell. Values > 100
essentially remove this function.
This option changes the font color of the acceleration count on
the progress record. If the acceleration datum in the
acceleration cell is a factor of (X) times less than the data from
the previous three points averaged, the font color in this cell will
be red, where (X) is the value in this cell. A value of -1 will
essentially remove this function.
This option changes the font color of the deceleration count on
the progress record. If the deceleration datum in the
deceleration cell is a factor of (X) times less than the data from
the previous three points averaged, the font color in this cell will
be green, where (X) is the value in this cell. A value of -1 will
essentially remove this function.
This option changes the font color of the deceleration count on
the progress record. If the deceleration datum in the
deceleration cell is a factor of (X) times more than the data from
the previous three points averaged, the font color in this cell will
be red, where (X) is the value in this cell. Values >100
essentially remove this function.
Behaver: 0
Target: 0
Day Day Date Time Accel Decel Total
No. Typ (m/d/y) (min) Count Count Poss# Rec Flr AccelRate DecelRate Rec Clng
0 Sun 01/20/02
1 Mon 01/21/02
2 Tue 01/22/02
3 Wed 01/23/02
4 Thu 01/24/02
5 Fri 01/25/02
6 Sat 01/26/02
7 Sun 01/27/02
8 Mon 01/28/02
9 Tue 01/29/02
10 Wed 01/30/02
11 Thu 01/31/02
12 Fri 02/01/02
13 Sat 02/02/02
14 Sun 02/03/02
15 Mon 02/04/02
16 Tue 02/05/02
17 Wed 02/06/02
18 Thu 02/07/02
19 Fri 02/08/02
20 Sat 02/09/02
21 Sun 02/10/02
22 Mon 02/11/02
23 Tue 02/12/02
24 Wed 02/13/02
25 Thu 02/14/02
26 Fri 02/15/02
27 Sat 02/16/02
28 Sun 02/17/02
29 Mon 02/18/02
30 Tue 02/19/02
31 Wed 02/20/02
32 Thu 02/21/02
33 Fri 02/22/02
34 Sat 02/23/02
35 Sun 02/24/02
36 Mon 02/25/02
37 Tue 02/26/02
38 Wed 02/27/02
39 Thu 02/28/02
40 Fri 03/01/02
41 Sat 03/02/02
42 Sun 03/03/02
43 Mon 03/04/02
44 Tue 03/05/02
45 Wed 03/06/02
46 Thu 03/07/02
47 Fri 03/08/02
48 Sat 03/09/02
49 Sun 03/10/02
50 Mon 03/11/02
51 Tue 03/12/02
52 Wed 03/13/02
2/2/2011 b9933e92-2b5a-4b96-b358-23c797641082.xls — Progress Record 7 of 42
53 Thu 03/14/02
54 Fri 03/15/02
55 Sat 03/16/02
56 Sun 03/17/02
57 Mon 03/18/02
58 Tue 03/19/02
59 Wed 03/20/02
60 Thu 03/21/02
61 Fri 03/22/02
62 Sat 03/23/02
63 Sun 03/24/02
64 Mon 03/25/02
65 Tue 03/26/02
66 Wed 03/27/02
67 Thu 03/28/02
68 Fri 03/29/02
69 Sat 03/30/02
70 Sun 03/31/02
71 Mon 04/01/02
72 Tue 04/02/02
73 Wed 04/03/02
74 Thu 04/04/02
75 Fri 04/05/02
76 Sat 04/06/02
77 Sun 04/07/02
78 Mon 04/08/02
79 Tue 04/09/02
80 Wed 04/10/02
81 Thu 04/11/02
82 Fri 04/12/02
83 Sat 04/13/02
84 Sun 04/14/02
85 Mon 04/15/02
86 Tue 04/16/02
87 Wed 04/17/02
88 Thu 04/18/02
89 Fri 04/19/02
90 Sat 04/20/02
91 Sun 04/21/02
92 Mon 04/22/02
93 Tue 04/23/02
94 Wed 04/24/02
95 Thu 04/25/02
96 Fri 04/26/02
97 Sat 04/27/02
98 Sun 04/28/02
99 Mon 04/29/02
100 Tue 04/30/02
101 Wed 05/01/02
102 Thu 05/02/02
103 Fri 05/03/02
104 Sat 05/04/02
105 Sun 05/05/02
106 Mon 05/06/02
107 Tue 05/07/02
108 Wed 05/08/02
109 Thu 05/09/02
2/2/2011 b9933e92-2b5a-4b96-b358-23c797641082.xls — Progress Record 8 of 42
110 Fri 05/10/02
111 Sat 05/11/02
112 Sun 05/12/02
113 Mon 05/13/02
114 Tue 05/14/02
115 Wed 05/15/02
116 Thu 05/16/02
117 Fri 05/17/02
118 Sat 05/18/02
119 Sun 05/19/02
120 Mon 05/20/02
121 Tue 05/21/02
122 Wed 05/22/02
123 Thu 05/23/02
124 Fri 05/24/02
125 Sat 05/25/02
126 Sun 05/26/02
127 Mon 05/27/02
128 Tue 05/28/02
129 Wed 05/29/02
130 Thu 05/30/02
131 Fri 05/31/02
132 Sat 06/01/02
133 Sun 06/02/02
134 Mon 06/03/02
135 Tue 06/04/02
136 Wed 06/05/02
137 Thu 06/06/02
138 Fri 06/07/02
139 Sat 06/08/02
140 Sun 06/09/02
2/2/2011 b9933e92-2b5a-4b96-b358-23c797641082.xls — Progress Record 9 of 42
#NAME?
#NAME?
Comments
2/2/2011 b9933e92-2b5a-4b96-b358-23c797641082.xls — Progress Record 10 of 42
Phase Changes
Phase or
Comment Line Line
No. Change Date From (Low) To (High) Description
1
2
3
4
5
6
7
8
9
2/2/2011 b9933e92-2b5a-4b96-b358-23c797641082.xls — Phase Descriptions Comments 11 of 42
Aim Keep in mind that only a "Zero-Count" Decel Aimstar requires time data. This is so that the Decel Aimstar will be plotted below the
Stars record floor. The time data has no effect on Aimstars other than those of a count of zero.
Time for
Aim Star Aim Star Aim Star Aim Star "Zero
Number Date Freq Accel Freq Decel Count" Notes
1
2
3
4
5
2/2/2011 b9933e92-2b5a-4b96-b358-23c797641082.xls — Phase Descriptions Comments 12 of 42
1000
100
10
Count Per Minute
1
0.1
0.01
0.001
0 7 14 21 28 35 42 49 56 63 70 77 84 91 98 105 112 119 126 133 140
Successive Calendar Days (by weeks)
Movement Cycle:
Name of Behaver:
2/2/2011 b9933e92-2b5a-4b96-b358-23c797641082.xls — Standard Celeration Chart
b9933e92-2b5a-4b96-b358-23c797641082.xls
1000
100
10
Count Per Minute
1
0.1
0.01
0.001
0 7 14 21 28 35 42 49 56 63 70 77 84 91 98 105 112 119 126 133 140
Successive Calendar Days (by weeks)
Movement Cycle:
Name of Behaver:
2/2/2011 b9933e92-2b5a-4b96-b358-23c797641082.xls — Standard Celeration Chart
b9933e92-2b5a-4b96-b358-23c797641082.xls
Start to Stop Date Celeration Lines Project Backward to Project Forward Date Celerations
Date Acel Day Acel Count Dcel Day Dcel Count Proj AcelDays Pacel Proj DeclDays Pdcel Celeration Value
Cel.1 StartDay 1/20/2002
StopDay 1/20/2002
Cel.2 StartDay 1/20/2002
StopDay 1/20/2002
Cel.3 StartDay 1/20/2002
StopDay 1/20/2002
Cel.4 StartDay 1/20/2002
StopDay 1/20/2002
Cel.5 StartDay 1/20/2002
StopDay 1/20/2002
Cel.6 StartDay 1/20/2002
StopDay 1/20/2002
Cel.7 StartDay 1/20/2002
StopDay 1/20/2002
Cel.8 StartDay 1/20/2002
StopDay 1/20/2002
Cel.9 StartDay 1/20/2002
StopDay 1/20/2002
Cel.10 StartDay 1/20/2002
StopDay 1/20/2002
Version: Description
3.0
3.1 Fixed bug in the formula for plotting below the floor.
3.2 Added Set-up tab, with sub-record-floor multiplier
option
3.3 Added scrolling highlight for today's date.
3.4 Added cell coloration
3.5 Removed superfluous cell information from earlier
version
3.6 Added conditional font formatting, and added
options for formatting to the Set-up tab.
3.7 Added lined chart tab & changed formula for
conditional formatting of red decel
3.8 Added conditional format for stalled trends; fixed
formula in cells K4-K8
3.9 Changed "last three datum" formula in rows O and P
3.99 Hid cells in Phase Change list.
3.999 Added dates on charts
4.0b Added up to 25 phase change lines; corrected
position of phase change lines.
4.0c Changed "blueline" for easier reading by adding
traditional gridlines
4.0d Added up to 50 phase change lines; corrected
labeling of new phase change lines
4.5b Added accel aim stars
4.5c Linked Stuart's dates to the axis so that chart could
be resized. Cleaned up some other little things.
Changed scalabitlity of fonts
4.5d Added decel aim stars
4.5e Fixed decel aim star crossbar
Add CELERATION FINDER and celeration lines to chart.
5.0 thru 5.3
Version History Standard Chart Template Instructrions 16
Macro sheet
c-o-d
dl.Npoints
Page 17
Macro sheet
gqi.DataRangeMiddle
Page 18
Macro sheet
gqi.DataRangeMiddleE
gs.LowerOneFlag
gs.UpperOneFlag
gs.Singles
gs.StartDayA
gs.StopDayA
gs.Mid1DayA
gs.Mid2DayA
gs.Mid1FreqA
gs.Mid2FreqA
gs.LogSlopeA
Page 19
Macro sheet
gs.StartFreqA
gs.StopFreqA
gs.PBdayA
gs.PFdayA
gs.PBFreqA
gs.PFFreqA
gs.TextSlopeA
gs.SlopeSignA
gs.StringSlopeA
gs.LineDescA
gs.LowerTwoFlag
gs.UpperTwoFlag
gs.StartDayE
gs.StopDayE
gs.Mid1DayE
gs.Mid2DayE
gs.Mid1FreqE
gs.Mid2FreqE
gs.LogSlopeE
gs.StartFreqE
gs.StopFreqE
gs.PBdayE
gs.PFdayE
gs.PBFreqE
Page 20
Macro sheet
gs.PFFreqE
gs.TextSlopeE
gs.SlopeSignE
gs.StringSlopeE
gs.LineDescE
c-o-C
Page 21
Macro sheet
DefineLimits
=ACTIVATE("Progress Record")
=SELECT(OFFSET(StartCell,0,0,141,1))
=COUNT(SELECTION())
=PROTECT.DOCUMENT(FALSE,TRUE,"clear")
=IF(dl.Npoints=0)
=ALERT("There are no data on which celeration lines can be drawn.",1)
=HALT()
=ELSE()
=TestBox()
=SelectRanges()
=GetQuarterIntersects()
=GetStats()
=END.IF()
=ACTIVATE("Progress Record")
=PROTECT.DOCUMENT(TRUE,TRUE,"clear")
=RETURN()
SelectRanges
=SELECT(OFFSET(DSeries,tb.D2row-1,0,(tb.D3row-tb.D2row+1),1))
=DEFINE.NAME("DWorkingSeries",SELECTION())
=IF(dAcel dInitial)
=SELECT(OFFSET(ASeries,tb.D2row-1,0,(tb.D3row-tb.D2row+1),1))
=DEFINE.NAME("AWorkingSeries",SELECTION())
=END.IF()
=IF(dDcel dInitial)
=SELECT(OFFSET(ESeries,tb.D2row-1,0,(tb.D3row-tb.D2row+1),1))
=DEFINE.NAME("EWorkingSeries",SELECTION())
=END.IF()
sr.Range=tb.D3row-tb.D2row+1
=TestDataRange()
=RETURN()
TestDataRange
=IF(dAcel dInitial)
I.2=0
=SELECT(OFFSET(!aworkingseries,sr.Range-1-I.2,-4,1,1))
=WHILE(ISBLANK(ACTIVE.CELL()))
I.2=I.2+1
=SELECT(OFFSET(!aworkingseries,sr.Range-1-I.2,-4,1,1))
=NEXT()
I.1=0
=SELECT(OFFSET(!aworkingseries,I.1,-4,1,1))
=WHILE(ISBLANK(ACTIVE.CELL()))
I.1=I.1+1
=SELECT(OFFSET(!aworkingseries,I.1,-4,1,1))
=NEXT()
=SELECT(OFFSET(Aworkingseries,I.1,0,sr.Range-I.1-I.2,1))
=DEFINE.NAME("Aworkingseries",SELECTION())
Page 22
Macro sheet
sr.Arange=ROWS(SELECTION())
=SELECT(OFFSET(ACTIVE.CELL(),0,-9,sr.Arange,1))
=DEFINE.NAME("DAworkingseries",SELECTION())
=END.IF()
=IF(dDcel dInitial)
j.2=0
=SELECT(OFFSET(!Eworkingseries,sr.Range-1-j.2,-4,1,1))
=WHILE(ISBLANK(ACTIVE.CELL()))
j.2=j.2+1
=SELECT(OFFSET(!Eworkingseries,sr.Range-1-j.2,-4,1,1))
=NEXT()
j.1=0
=SELECT(OFFSET(!Eworkingseries,j.1,-4,1,1))
=WHILE(ISBLANK(ACTIVE.CELL()))
j.1=j.1+1
=SELECT(OFFSET(!Eworkingseries,j.1,-4,1,1))
=NEXT()
=SELECT(OFFSET(!Eworkingseries,j.1,0,sr.Range-j.1-j.2,1))
=DEFINE.NAME("Eworkingseries",SELECTION())
sr.Erange=ROWS(SELECTION())
=SELECT(OFFSET(ACTIVE.CELL(),0,-10,sr.Erange,1))
=DEFINE.NAME("DEworkingseries",SELECTION())
=END.IF()
=RETURN()
GetQuarterIntersects
=IF(COUNT(DAworkingseries)<=1)
=ALERT("There are insufficient data points in the date range to proceed.",1)
=HALT()
=END.IF()
=IF(dAcel dInitial)
=SELECT(DAworkingseries)
=QUARTILE(DAworkingseries,2)
=IF((gqi.DataRangeMiddle)-INT(gqi.DataRangeMiddle)=0)
=SELECT(OFFSET(DAworkingseries,0,0,INT(sr.Arange/2),1))
=DEFINE.NAME("DAlowerhalf",SELECTION())
=SELECT(OFFSET(DAlowerhalf,0,9))
=DEFINE.NAME("AcelLowerHalf",SELECTION())
=SELECT(OFFSET(DAworkingseries,INT(sr.Arange/2)+1,0,INT(sr.Arange/2),1))
=DEFINE.NAME("DAupperhalf",SELECTION())
=SELECT(OFFSET(DaUpperhalf,0,9))
=DEFINE.NAME("AcelUpperHalf",SELECTION())
=ELSE()
=SELECT(OFFSET(DAworkingseries,0,0,INT(sr.Arange/2),1))
=DEFINE.NAME("DAlowerhalf",SELECTION())
=SELECT(OFFSET(DAlowerhalf,0,9))
=DEFINE.NAME("AcelLowerHalf",SELECTION())
=SELECT(OFFSET(DAworkingseries,INT(sr.Arange/2),0,INT(sr.Arange/2),1))
=DEFINE.NAME("DAupperhalf",SELECTION())
Page 23
Macro sheet
=SELECT(OFFSET(DaUpperhalf,0,9))
=DEFINE.NAME("AcelUpperHalf",SELECTION())
=END.IF()
=END.IF()
=IF(COUNT(DEworkingseries)<=1)
=ALERT("There are insufficient data points in the date range to proceed.",1)
=HALT()
=END.IF()
=IF(dDcel dInitial)
=SELECT(DEworkingseries)
=QUARTILE(DEworkingseries,2)
=IF((gqi.DataRangeMiddleE)-INT(gqi.DataRangeMiddleE)=0)
=SELECT(OFFSET(DEworkingseries,0,0,INT(sr.Erange/2),1))
=DEFINE.NAME("DELowerhalf",SELECTION())
=SELECT(OFFSET(DELowerhalf,0,10))
=DEFINE.NAME("DcelLowerHalf",SELECTION())
=SELECT(OFFSET(DEworkingseries,INT(sr.Erange/2)+1,0,INT(sr.Erange/2),1))
=DEFINE.NAME("DeUpperhalf",SELECTION())
=SELECT(OFFSET(DeUpperhalf,0,10))
=DEFINE.NAME("DcelUpperHalf",SELECTION())
=ELSE()
=SELECT(OFFSET(DEworkingseries,0,0,INT(sr.Erange/2),1))
=DEFINE.NAME("Delowerhalf",SELECTION())
=SELECT(OFFSET(DELowerhalf,0,10))
=DEFINE.NAME("dcelLowerHalf",SELECTION())
=SELECT(OFFSET(DEworkingseries,INT(sr.Erange/2),0,INT(sr.Erange/2),1))
=DEFINE.NAME("Deupperhalf",SELECTION())
=SELECT(OFFSET(DeUpperhalf,0,10))
=DEFINE.NAME("dcelUpperHalf",SELECTION())
=END.IF()
=END.IF()
=RETURN()
GetStats
=IF(dAcel dInitial)
=IF(COUNT(AcelLowerHalf)=1,TRUE,FALSE)
=IF(COUNT(AcelUpperHalf)=1,TRUE,FALSE)
=IF(AND(gs.LowerOneFlag,gs.UpperOneFlag),TRUE,FALSE)
=DEREF(OFFSET(DAworkingseries,0,0,1,1))
=DEREF(OFFSET(DAworkingseries,ROWS(DAworkingseries)-1,0,1,1))
=IF(gs.Singles,gs.StartDayA,QUARTILE(DAlowerhalf,2))
=IF(gs.Singles,gs.StopDayA,QUARTILE(DaUpperhalf,2))
=QUARTILE(AcelLowerHalf,2)
=QUARTILE(AcelUpperHalf,2)
=(LOG(gs.Mid2FreqA)-LOG(gs.Mid1FreqA))/(gs.Mid2DayA-gs.Mid1DayA)
Page 24
Macro sheet
=10^((gs.LogSlopeA*(gs.StartDayA-gs.Mid1DayA))+LOG(gs.Mid1FreqA))
=10^((gs.LogSlopeA*(gs.StopDayA-gs.Mid1DayA))+LOG(gs.Mid1FreqA))
=MATCH(date1,DateSeries,0)-1
=MATCH(date4,DateSeries,0)-1
=IF(NOT(ISBLANK(date1)))
=10^((gs.LogSlopeA*(gs.PBdayA-gs.Mid1DayA))+LOG(gs.Mid1FreqA))
=END.IF()
=IF(NOT(ISBLANK(date4)))
=10^((gs.LogSlopeA*(gs.PFdayA-gs.Mid1DayA))+LOG(gs.Mid1FreqA))
=END.IF()
=TEXT(10^(ABS(gs.LogSlopeA)*7),"0.0")
=IF(gs.LogSlopeA<0,CHAR(214),"x")
=gs.SlopeSignA&gs.TextSlopeA
="("&gs.SlopeSignA&gs.TextSlopeA&")"
=ACTIVATE("Celeration Lines")
=FORMULA(gs.StartDayA,ACTIVE.CELL())
=FORMULA(gs.StopDayA,OFFSET(ACTIVE.CELL(),1,0,1,1))
=FORMULA(gs.StartFreqA,OFFSET(ACTIVE.CELL(),0,1,1,1))
=FORMULA(gs.StopFreqA,OFFSET(ACTIVE.CELL(),1,1,1,1))
=IF(NOT(ISBLANK(date1)),FORMULA(gs.PBdayA,OFFSET(ACTIVE.CELL(),0,4,1,1)),FORMULA(gs.StartDayA,OFFSET(ACTIVE.CELL(),0,4,1,1)))
=IF(NOT(ISBLANK(date4)),FORMULA(gs.PFdayA,OFFSET(ACTIVE.CELL(),1,4,1,1)),FORMULA(gs.StopDayA,OFFSET(ACTIVE.CELL(),1,4,1,1)))
=IF(NOT(ISBLANK(date1)),FORMULA(gs.PBFreqA,OFFSET(ACTIVE.CELL(),0,5,1,1)),FORMULA(gs.StartFreqA,OFFSET(ACTIVE.CELL(),0,5,1,1)))
=IF(NOT(ISBLANK(date4)),FORMULA(gs.PFFreqA,OFFSET(ACTIVE.CELL(),1,5,1,1)),FORMULA(gs.StopFreqA,OFFSET(ACTIVE.CELL(),1,5,1,1)))
=FORMULA(gs.LineDescA,OFFSET(ACTIVE.CELL(),0,8,1,1))
=END.IF()
=IF(dDcel dInitial)
=IF(COUNT(DcelLowerHalf)=1,TRUE,FALSE)
=IF(COUNT(decelupperhalf)=1,TRUE,FALSE)
=DEREF(OFFSET(DEworkingseries,0,0,1,1))
=DEREF(OFFSET(DEworkingseries,ROWS(DEworkingseries)-1,0,1,1))
=IF(gs.LowerTwoFlag,gs.StartDayE,QUARTILE(DELowerhalf,2))
=IF(gs.UpperTwoFlag,gs.StopDayE,QUARTILE(DeUpperhalf,2))
=QUARTILE(DcelLowerHalf,2)
=QUARTILE(DcelUpperHalf,2)
=(LOG(gs.Mid2FreqE)-LOG(gs.Mid1FreqE))/(gs.Mid2DayE-gs.Mid1DayE)
=10^((gs.LogSlopeE*(gs.StartDayE-gs.Mid1DayE))+LOG(gs.Mid1FreqE))
=10^((gs.LogSlopeE*(gs.StopDayE-gs.Mid1DayE))+LOG(gs.Mid1FreqE))
=MATCH(date1,DateSeries,0)-1
=MATCH(date4,DateSeries,0)-1
=IF(NOT(ISBLANK(date1)))
=10^((gs.LogSlopeE*(gs.PBdayE-gs.Mid1DayE))+LOG(gs.Mid1FreqE))
=END.IF()
Page 25
Macro sheet
=IF(NOT(ISBLANK(date4)))
=10^((gs.LogSlopeE*(gs.PFdayE-gs.Mid1DayE))+LOG(gs.Mid1FreqE))
=END.IF()
=TEXT(10^(ABS(gs.LogSlopeE)*7),"0.0")
=IF(gs.LogSlopeE<0,CHAR(214),"x")
=gs.SlopeSignE&gs.TextSlopeE
="("&gs.SlopeSignE&gs.TextSlopeE&")"
=ACTIVATE("Celeration Lines")
=FORMULA(gs.StartDayE,OFFSET(ACTIVE.CELL(),0,2,1,1))
=FORMULA(gs.StopDayE,OFFSET(ACTIVE.CELL(),1,2,1,1))
=FORMULA(gs.StartFreqE,OFFSET(ACTIVE.CELL(),0,3,1,1))
=FORMULA(gs.StopFreqE,OFFSET(ACTIVE.CELL(),1,3,1,1))
=IF(NOT(ISBLANK(date1)),FORMULA(gs.PBdayE,OFFSET(ACTIVE.CELL(),0,6,1,1)),FORMULA(gs.StartDayE,OFFSET(ACTIVE.CELL(),0,6,1,1)))
=IF(NOT(ISBLANK(date4)),FORMULA(gs.PFdayE,OFFSET(ACTIVE.CELL(),1,6,1,1)),FORMULA(gs.StopDayE,OFFSET(ACTIVE.CELL(),1,6,1,1)))
=IF(NOT(ISBLANK(date1)),FORMULA(gs.PBFreqE,OFFSET(ACTIVE.CELL(),0,7,1,1)),FORMULA(gs.StartFreqE,OFFSET(ACTIVE.CELL(),0,7,1,1)))
=IF(NOT(ISBLANK(date4)),FORMULA(gs.PFFreqE,OFFSET(ACTIVE.CELL(),1,7,1,1)),FORMULA(gs.StopFreqE,OFFSET(ACTIVE.CELL(),1,7,1,1)))
=FORMULA(gs.LineDescE,OFFSET(ACTIVE.CELL(),1,8,1,1))
=END.IF()
=RETURN()
ClearAllCelerations
=SELECT(All.Celerations)
=CLEAR(3)
=RETURN()
Page 26
Macro sheet
dType dX dY dW dH
dGetDates 371 269 308 340
1 5 66 5 171 25
2 dPBDate 8 66 20 171 25
3 5 30 50 250
get data point count from Time(min) column as data are 'real' only when the record floor has been defined 25
Check for zero count 4 dStartDate 8 66 65 171 25
Alert the user 5 5 40 95 250 25
stop the program 6 dStopDate 8 66 110 171 25
7 5 66 140 171 25
get critical dates from user 8 dPFDate 8 66 155 171 25
9 5 20 185 275 50
begin marking off ranges 11 dAcel 13 66 235 200 25
12 dDcel 13 66 255 200 25
13 d.Cancel 2 170 300 64 23
14 d.Okay 1 80 300 64 23
GetCelBlock 371 269 275 300
1 5 50 25 175 50
2 gcb.ListVal 15 50 75 150 100
3 gcb.ClearBlock103 10 200 250 25
4 gcb.Cancel 2 150 250 64 23
5 gcb.Ok 1 50 250 64 23
name range in DaySeries
name range in Acel Series
name range in Dcel Series
set # days in range to a constant
Collapse specified range from top and bottom to data end points.
begins at bottom of Aseries
works backwards to first
data point
begins at top of Aseries
works downwards to first
data point
c-o-t
Select Aseries
Rename tb.AvailBlock
Page 27
Macro sheet
calculate new Aseries data range
tb.GetBlock
begins at bottom of Eseries
works backwards to first
data point
begins at top of Eseries
works downwards to first
data point
tb.DialogBox
Select new Aseries
Rename
calculate new Aseries data range
Select Eseries tb.D2row
Rename
calculate new Eseries data range
tb.D3row
Check acel data range for number of data points
if ≤1 points, error msg
halt program
if Acel series was chosen
select the day series
find the median
if even number of points
select the lower half
name it
select the lower half acel series
name it
select the upper half day series
name it
select the upper half acel series
name it
else odd number of points
select the lower half day series
name it
select the lower half acel series
name it
select upper day series
name it
Page 28
Macro sheet
select upper half acel series
name it
end of if condition
check dec el series for number of data points
if ≤1 points
halt
if decel series selected
select error day series
find the median
if even # of points
As above
else odd count
As above
Page 29
Macro sheet
Page 30
Macro sheet
Page 31
Macro sheet
Updates
dText dInitial
Celeration Line Start/Stop Dates
Enter 'Project Backward Date'
Beginning date of data used for celeration line
35850
End date of data used for celeration line
35860
Enter 'Project Forward' Date
35867
="Enter dates earliest to latest. No date may preceed "&TEXT(SynchDate,"mm/dd/yyyy")&" or be later than "&TEXT(SynchDate+140,"mm/dd/yyyy")&". Project F
Plot line for acceleration series TRUE
Plot line for deceleration series TRUE
Cancel
OK
=RETURN()
Celeration Finder
Select from the list the Block where the celeration data get placed.
gcb.cellist 5
Clear Existing Celeration Lines
Cancel
OK
ClearRange gcb.CelList
Celeration Line 1
=IF(gcb.ListVal dInitial=1) Celeration Line 2
=SELECT(Cel.1) Celeration Line 3
=ELSE.IF(gcb.ListVal dInitial=2) Celeration Line 4
=SELECT(Cel.2) Celeration Line 5
=ELSE.IF(gcb.ListVal dInitial=3) Celeration Line 6
=SELECT(Cel.3) Celeration Line 7
=ELSE.IF(gcb.ListVal dInitial=4) Celeration Line 8
=SELECT(Cel.4) Celeration Line 9
=ELSE.IF(gcb.ListVal dInitial=5) Celeration Line 10
=SELECT(Cel.5)
=ELSE.IF(gcb.ListVal dInitial=6)
=SELECT(Cel.6)
=ELSE.IF(gcb.ListVal dInitial=7)
=SELECT(Cel.7)
=ELSE.IF(gcb.ListVal dInitial=8)
=SELECT(Cel.8)
=ELSE.IF(gcb.ListVal dInitial=9)
=SELECT(Cel.9)
=ELSE.IF(gcb.ListVal dInitial=10)
=SELECT(Cel.10)
=END.IF()
=RETURN()
TestBox
=Updates()
=ACTIVATE("Celeration Lines")
=MATCH(TRUE,AvialList,0)
Page 32
Macro sheet
=IF(NOT(ISNA(tb.AvailBlock)))
=SET.VALUE((gcb.ListVal dInitial), tb.AvailBlock)
=ELSE()
=ALERT("All Celeration Lines Are In Use. To Continue, Select A Line To Eliminate, Clear, Then OK.",1)
=END.IF()
=DIALOG.BOX(GetCelBlock)
=IF(NOT(tb.GetBlock),HALT()) 5
=ClearRange()
=IF(tb.GetBlock=3)
=CLEAR(3)
=GOTO(tb.GetBlock)
=END.IF()
=ACTIVATE("Progress Record")
=DIALOG.BOX(DateBox)
=IF(NOT(tb.DialogBox),HALT())
=IF(OR(date2<SynchDate,date2>date3,date2>(SynchDate+140)))
=ALERT("Your start date is either before "&TEXT(SynchDate,"mm/dd/yyyy")&"or after "&TEXT(SynchDate+140,"mm/dd/yyyy")&". ",1)
=HALT()
=ELSE()
=MATCH(date2,DateSeries,0)
=END.IF()
=IF(OR(date3<SynchDate,date3<date2,date3>SynchDate+140))
=ALERT("Your end date is either before "&TEXT(SynchDate,"mm/dd/yyyy")&"or after "&TEXT(SynchDate+140,"mm/dd/yyyy")&". ",1)
=HALT()
=ELSE()
=MATCH(date3,DateSeries,0)
=END.IF()
=IF(NOT(ISBLANK(date1)))
=IF(AND(date1>=SynchDate,date1<date2))
tb.Pbrow=MATCH(date1,DateSeries,0)
=ELSE()
tb.Pbrow=MATCH(SynchDate,DateSeries,0)
=SET.VALUE(dPBDate dInitial,SynchDate)
=ALERT("The Project Backward date is before "&TEXT(SynchDate,"mm/dd/yyyy")&" and has been changed to the chart synchronization date. To adjust, enter C
=END.IF()
=END.IF()
=IF(NOT(ISBLANK(date4)))
=IF(date4<=SynchDate+140)
tb.Pfrow=MATCH(date4,DateSeries,0)
=ELSE()
tb.Pfrow=MATCH(SynchDate+140,DateSeries,0)
=SET.VALUE(dPFDate dInitial,SynchDate+140)
=ALERT("The Project Forward date is after "&TEXT(SynchDate+140,"mm/dd/yyyy")&" and has been changed to the chart synchronization date. To adjust, enter
=END.IF()
=END.IF()
=RETURN()
Page 33
Macro sheet
Page 34
Macro sheet
Page 35
Macro sheet
Page 36
Macro sheet
Page 37
Macro sheet
Page 38
Macro sheet
Page 39
Macro sheet
Page 40
Macro sheet
Page 41
Macro sheet
0 0
Page 42
Related docs
