If you’ve no interest in diabetes, please skip this post. I’m aiming it squarely at anybody who measures their blood sugar.
These last few weeks, I have been working on a Diabetes spreadsheet. It is aimed at storing the values, when you use a glucometer to prick your finger. Well, okay, I have had a Diabetes spreadsheet for years, but just using UK units. After all, that was all I needed. This last few weeks, I made it international. That only took about a day, the rest of the time I have just been testing it.
The resulting spreadsheet, I share with you today. It is a spreadsheet containins macros (tiny snippets of programming) so I had to save it as a .XLSM file (m = macro), instead of the usual .XLSX extension. Furthermore, WordPress will not allow me to upload a .XLSM file directly, so I have had to place it inside an archive .ZIP file. To run the macros in the spreadsheet, you will have to allow Excel itself to run macros – there are instructions for this on the web, or contact me privately, and I will try to help.
What’s in the box?
When you open the spreadsheet, you will see two coloured backgrounds, white and grey. White are the cells in which you are expected to enter data, grey are the cells which are calculated for you. The cells you are expected to enter are just the date, time and the value of your reading. I’ve added ten dummy rows to give you an example to start with.
The very first cell is a dropdown to allow you to specify what units you want to use. There are two units used worldwide (mmol/l and mg/dl), and I support them both, but only one at a time. Whichever unit you choose, this is referred to as your primary unit. The other unit, the spreadsheet refers to as the secondary unit. I realise that the secondary unit might not be of interest to you, but I have written it in so that the spreadsheet can use either unit.
So, the values you enter. Column A is the date, column B is the time. I have formatted these columns to how I like them, but you can change this formatting if you wish.
Column C is used by the spreadsheet to store the datetime, which it will calculate and will use in its own calculations. Again, you can change the format if you wish.
Column D is the value of the measurement, in whatever unit you have chosen. Again, change the format if you wish.
At this point, please note that the spreadsheet expects the values to be added sequentially. In other words, if you took a reading on 1st January, 2021, it goes before the reading you took on 1st February, 2021, which in turn goes before the 1oth February, 2021 reading. And so on. The reason for this will become clear in a moment.
Now, here is why the spreadsheet is useful, beyond just recording the readings. What I find is that the number in Column D can be all over the place. Literally, it can be very low in the morning and very high in the evening, if I’m not careful. Because, they are spot values. So, on their own, the numbers don’t really give a clear idea of how well I’m controlling my sugar. Here’s an example:
See what I mean? It can be difficult to see what is going on. I find that it helps to apply statistics to the numbers, to talk about my average sugar rather than individual values. And the spreadsheet calculates these average values for you, that’s why it has macros in it. So, the chart above becomes:
which I think is clearer. In fact, the spreadsheet calculates your average sugar over the last fifty days. It also calculates a value called the standard deviation, which gives an idea how much your sugar varies each time. Note that the standard deviation calculation needs at least two readings in order to be calculated, as you will see if you look in the very first row of example data.
So when you ask it to calculate what these statistics were on a certain date, the thing the spreadsheet does is to find all the readings which are within 50 days, so as to include them in its calculation. That’s why the data has to be entered sequentially.
Therefore, Column E is the calculation, if I am going back up to 50 days, what is the earliest cell I count? Similarly, Column F tells you the total number of rows to be counted. In fact, as long as they are within 50 days, it doesn’t matter whether there are 1 or 1000 results. Columns E and F will be calculated for you.
Columns F and H contain the two interesting, statistical values, the Average and Standard Deviation, in your chosen unit. Again, I have chosen a format which suits me, you can change it if you wish. I also added a couple of graphs to my spreadsheet (5 minutes) but I shall leave that to you to do, if you wish. The graphs are pretty, but the numbers are more important.
Now, you remember we talked about the primary and secondary units? We have all the values in your primary unit, but Columns I, J and K show them in the secondary unit. Again, these fields are calculated and again, you may alter the format, or even hide, these columns, if you wish.
My last column, Column L, is just a free-text field I use to store the name of my monitor. This is just because, over the years, I have had several. Some will last years, others days. Build quality is usually not very impressive. Storing the monitor against its values might give me a clue if my values were consistently high or low, for example. That has never happened yet, but if it does… This column is not used in any calculations, you can delete it if you wish.
How to make a calculation
When you enter a date and a time, the datetime (Column C) is calculated automatically. Thereafter, though, the spreadsheet only performs a calculation when you hit the Calculate Statistics button at the top of the spreassheet. This is entirely deliberate, to allow you to type in values in peace, without the screen updating all around you.
When you hit Calculate Statistics, the first thing you see is a small dialog box which asks you where you want to start, and where you want to finish. This fits in with how I work. Every week or so, I walk the monitor over to the computer and type in each number. At the end, I only want to calculate the numbers for the last week, not every number. So I only need to calculate a dozen or so rows statistics each time. And, as long as you are just adding numbers, none of the previous numbers will change.
If you’re inserting numbers into the middle of the list, though, I recommend recalculating everything again. But the spreadsheet can do this, just so long as those dates and times are in the correct order.
When the macro has finished, it will pop up a little dialog box just to say as much. I have found on my machine that a calculation for a thousand rows might take 20s or so, but it will tell you when it is finished. Your computer might be faster or slower.
Lastly, the observant people among you will have notices that the spreadsheet has two tabs. Everything I talked about today refers to the Data tab. I’ll briefly talk about the Calculations tab in a day or two [update: this post is now written and may be found here]. Feedback, of course, is welcome.