[Previous Months][Date Index][Thread Index][Join - Register][Login]   Help@Insulin-Pumpers.org
  [Message Prev][Message Next][Thread Prev][Thread Next]   for subscribe/unsubscribe assistance

[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
http://mosquitonet.com/~sbowling (general)
http://bowlingsite.mcf.com/DogPage.html (dogs)
http://climate.gi.alaska.edu/Bowling/Bowling.html (professional--retired)
for HELP or to subscribe/unsubscribe/change list versions,
contact: HELP@insulin-pumpers.org