[IP] Excel log--long, delete if not interested
Here is how I set up a spreadsheet and plots in Excel.
1. In row 1, put date, time, BG, Time and date, BG, carb, insulin,
notes, set, meal, exercise (columns A-K). Highlight cell A2 and
freeze panes (window menu).
2. Put today's date in cell A2, time of first blood glucose test of
the day in B2, and the BG value in C2. Cell D2 gets the formula
=A2+B2 and cell E2 gets the formula =C2. Columns D and E will be
your main plotting columns.
3. Use the fill down function (under the Edit menu) to repeat the
date down the A column for as many cells as you are likely to need in
a day. Use the fill down function on columns D and E to repeat the
formulas as far down as you want. I now "fill as I go" about a
screen length of cells at a time.
4. Put time and blood glucose in a new row in columns B and C each
time you test. If you eat, put the carb amount in column F. If you
take a bolus put it in column G. H can be used for the
food/correction/IOB breakdown of the bolus, exercise, set and/or
reservoir changes, battery changes, etc. Make sure you adjust that
column to be as wide as possible. I, J and K are used if you want
set changes, meals, correction boluses and exercise times to appear
on a plot. I put 350 in the set column when I change sets, 50 in the
meal column when I eat a meal that is bolused for, 10 in the meal
column for a snack (unbolused) to raise my blood sugar or before
exercise, 75 in the exercise column for exercise, and 300 in the
exercise column for a correction bolus. Adjust the widths of all
columns to just fit their contents. C, E, I, J and K should be quite
narrow; D and H will need to be quite wide. I may put in a couple
more columns for protein and fat once I get a monitor that's better
than 640 x 480.
5. Select the last row of the day and go to format--border and
underline. the next row gets the new date put in column A. I use a
new spreadsheet each month. My doctor claimed I was drowning him in
data at first, but I've heard no complaints since the gall bladder
hospitalization. (I e-mail him the Excel file each month.)
6. Plots. I do a week at a time, but you can do anything from a day
to a month. Select as many days as you want to plot from columns D
and E. When they are highlighted, go to insert--chart. Select
"XY-Scatter" I prefer the smoothed plot with connecting lines. Then
select "next" and again "next" At this step put in title, "date" for
x-axis, and "blood glucose" for y-axis. Go to "gridlines" if you
want vertical lines on the graph, as for days. Go to "Legend" and
deselect (click) "show legend. Then click next again. I prefer to
put the chart on a separate page--"as new sheet." Name it if you
want. Then click "finish." You can change the background color from
gray by double-clicking on the background, and then selecting a
different color. You can change the form of the date by double
clicking on the bottom line of the plot and choosing "number". I
like the date format that gives month and day only--1/12. In fact,
you can change just about anything by double-clicking on it.
7. Plots II. To put in the markers for meals, exercise, set changes
and boluses you need to do the selection a little differently. Start
by selecting the time range you want in columns D and E, as before.
I have a Mac, and select discontinuous data by holding down the apple
key after the initial selection, then select the same dates in
columns I, J and K. (If the dates differ you'll get a garbage plot.
Just back up and redo the selection. Can someone familiar with
Windows give instructions for selecting non-adjacent data in
Windows?) Go through the same process as above. The automatic
colors are awful, but easy to change--just double-click on the marker
on the plot, and choose the color, shape and size of marker you want.
I select "none" on the line type for the markers in columns I, J and
K so these are just time markers. Make your chart the way you want
it except leave the x-axis scale (double click on the bottom axis and
select "scale") on automatic.
8. Plots III. Once you have your chart the way you like it, you can
save the custom features to apply to other time periods. Click on
the white space around the chart. Then go to the chart menu and
select chart type. Click "Custom types" then select user-defined.
Click add and name and describe your chart, then click OK. You can
now select data as above, but instead of selecting the x-y scatter,
go to the top of the chart type and select "custom types, click
user-defined and select your own chart. The chart will come up with
your own colors, etc. You will have to put in an up-to-date chart
title. rechoose to have the chart as a separate sheet, etc., but it's
a lot faster than working from scratch. Week charts on automatic
scale will have a blank day before and a blank day after the week of
data. You can use the scale to remove these--just add 1 to the
initial number and subtract 1 from the final number. (Excel gives
you times in scale as numbers--large numbers--but the conversion is 1
= 1 day and whole numbers plot at midnight.)
Sue Ann Bowling, North Pole, Alaska
for HELP or to subscribe/unsubscribe/change list versions,