This is where my Sudoku game rates me, on how quickly I solve their puzzles:
Actually, I’ve always been very good with numbers, but my eyesight these days is rubbish.
Speed Test
Category: Diabetes
Diabetes Spreadsheet (2)
A couple days ago I posted my diabetes spreadsheet, in case anybody finds it useful. This post is a continuation, so if you are not interested, please save your time and skip this post.
If you are interested, and you missed the other post, it is here, along with a link to the spreadsheet itself.
I said the other day that I would post about the second tab, so here goes (it’s easier than the last post):
If you open the spreadsheet in Excel, and look along the bottom, you see two tabs. The default tab is called Data, which we discussed last time. The second tab is called Calculations. This tab contains data which the macro uses to make its calculation. So straight away, if you’re not interested, you can just leave this tab alone.
If you are interested, though, I will explain:
The spreadsheet supports either of the two units worldwide, mmol/l and mg/dl. In fact, the two are related by a simple formula, just like, say, C and F. The two are related such that:
1 mmol/l = 18 mg/dl
in other words:
1 mg/dl = 1/18 mmol/l
So the first thing you see is a table of possible units, alongside the factor needed to get from one unit to the other. 1/18 = 0.055556. If you change these numbers, you will mess up the calculation. This data is cells B2:C3, on the Calculations tab.
The second item on this tab is just a reminder of what primary units you have selected. We talked about primary units last time, so I won’t drone on again. In practise, this value is just copied from the previous tab. This is cell B6 on the Calculations tab.
The last thing on the Calculations tab is a kind-of key. When the macro fires, it ends up reading data in certain columns, and writing some data into different columns. The macro needs to know, therefore, what column to read/write such-and-such a piece of data from/to. On the Calculations tab, the cells D9:D17 define these columns.
In that way, you can add your own columns to the spreadsheet, if you want, just so long as you update these cells too.
In the same vein, the way the macro works is to look back fifty days, and work out what the first cell is that it needs to include. To do this, it starts off at one row, then looks back at the previous row, then the previous row, and so on. This approach allows you to have as few or as many entries as you like. But by looking back one row at a time, there is a danger that the macro will count beyond the end of the data. So, you need to tell it where the end of the data is.
On the Calculations tab, Cell D18 tells the macro which the first row is that holds data. In the initial case, it is Row 5. But again, you can add your own rows (or remove mine) to the top of the spreadsheet and, so long as you update this cell, the calculation will still work.
Lastly, you know I mentioned that averages were calculated over the last fifty days? Well, the reason I chose fifty was just that it seemed like a decent length of time, but not including dates which are so far back, they might not be relevant. In fact, when you have your HBA1C blood test, this is also an average over the last couple of months.
But, if you have a better idea, you can change this. On the Calculations tab, Cell D20 contains the number of days to go back. If you change this number, the calculation will still work, but you will need to recalculate every row again.
My Diabetes Spreadsheet
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.
