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.