Creating an insulin:carb calculator

By | 15 September, 2009

The thills and spills of formulae

In an idle moment and for a bit of fun – I use the term “fun” advisedly here – I decided to knock up a diabetes spreadsheet.

I thought it would be interesting (again the term “interesting” is relative here) to log my insulin doses, carbohydrate intake and resultant blood glucose. By comparing the carb to insulin ratio – over, say, a 14 day period – to the resulting BG target-range hit-rate, I could objectively calculate my ideal ratio at different times of day. From this data I could then create a personalised insulin calculator. I could type in the time of day, my proposed carb intake and from that calculate the correct dose of insulin to stick in – all based on the data I collected earlier.

I don’t need to tell you, it was a dull, rainy weekend.

I know that the minute I publish this, someone will say in the comments below “Ah yes, the ‘Carbotron-XP’ software can already do this for you”. However, despite that inevitability, it was an interesting exercise to work out the formulas, etc., by myself.

This was mainly because I quickly realised how insanely complex it is to note all the relevant factors and come up with objective calculations. Measuring and logging my BG, carb and insulin intake before and after meals is pretty straightforward (if incredibly, incredibly tedious) but then it gets more complex.

What if my BG is high before my breakfast banquet and I stick in a correcting dose – in addition to an amount to cover the roast pheasant and flagon of port I have each morning? If I simply divide the number of carbs by the amount of insulin I’ve put in then that screws up my carb:insulin ratio calculation, as it doesn’t take account of the correction. So corrections have to be recorded separately.

To keep things simple, I’ve also divided the day into eight separate blocks – that is before and after meals and two night-time blocks. But what if I have a mid-morning snack which I might cover with a unit or two? If that’s not recorded then, it too, will screw up my calculations. I’ve therefore had to cheat and lump my elevenses in with my breakfast. While this is probably near enough, it starts to add a margin of error into the equations.

More errors inevitably creep in as the spreadsheet doesn’t take account glycaemic index, exercise, stress, phase of the moon, tide times and everything else that affects my blood glucose.

And so, in the end, my highly objective exercise results in a rough guide to carb:insulin and requires quite a lot of finger-in-the-air guestimating to get it to work. If carefully recorded data and the massive computing power of Microsoft Excel can’t come up with a useful guide to diabetes, then it’s no wonder I manage to completely cock-up my doses from time to time. I suppose what is more interesting is that – apparently against the odds – I manage to get it right quite so often. Go me!

14 thoughts on “Creating an insulin:carb calculator

  1. Alison

    Wow Tim, my head aches from even thinking about this! The bolus wizard on my pump combined with the download software makes a reasonable job of this – it does what you’re trying to do plus it records whether you’re giving insulin to bring down a high or because you’re eating etc but there’s still a huge amount of guesswork involved. It all seems so simple until you factor in “stuff”, then it turns into an exercise in random number generating!

  2. Tim

    I’m toying with basing the calculator on a random-number generator. It picks out a number between 2 & 10 and that’s the amount of humalog to stick in. It might have a better hit rate than the above!

    My favourite formula from my spreadsheet? I’m glad you asked, it’s:

    =IF(H4<=4, "Bad", IF(H4<10, "Good", IF(H4>=10, “Very Bad”)))

  3. Ckoei

    In yet another moment of shameless exhibitionism, here is a peep at my show (hosted at

    I started off with the given neutral “Settings”. Thanks to the picturesque nature of “Analysis” (especially “Comparison of days” and “Profile per hour”), one can see when there is need to nudge one’s CU factor upwards or downwards: red=up ye go, blue=SIT!, green=stay. . .

  4. Tim

    @Ckoei Blimey – that’s a lot of data.

    On your recommendation I did have a look at Glucosurfer, but – sadly – I didn’t really like the way it displayed the results and entering data via my BlackBerry didn’t really work very well. So I gave up (the simplest solution when anything’s difficult).

  5. Ckoei

    Tim :
    (the simplest solution when anything’s difficult).

    The very reason I make use of this service, me{&phone(according to Moore)} being technologically lobotomized beings. Maybe you should have your BlackBerry’s clever bits cut out.

  6. Tim

    @Ckoei I may just do that. The damn thing’s a pain in the arse most of the time anyway. You can never get fast Internet coverage when you need it – but that’s another rant for another blog…

  7. Tim

    @Heather See! I knew someone would have already done it. 🙂

    Just downloaded t’other sheet and, blimey, it makes your eyes bleed even worse than my one. That’s the next challenge – make a diabetes spreadsheet that doesn’t look intimidatingly scary!

  8. CALpumper aka Crystal

    I hate logging.
    I don’t mind the software that comes with meter and pump but it does not really allow for the other “stuff” as Alison wrote.
    I hate logging.
    Wait, I already wrote that. Crap. Did I say I Hate logging?
    I did it for 20 years, stopped, picked it up again 2 years later. Stopped.
    Sigh. I hate logging.

  9. Scott K. Johnson

    Holy crap Tim!

    (Here’s where you think I’m going to tease you about having nothing better to do with your time)

    You completely forgot Insulin On Board!


  10. Ckoei

    Speaking of meter software (& connection gizmos) needed to download collected data: I refuse to dish out more “pennies” into the bulging pockets of strip manufacturers; so as data-displacing squirrel I’m fine with dropping data drop by drop into Glucosurfer’s sea. Does the NHS provide this software & cables along with meters? And could the NHS not be enticed to provide a service similar to Glucosurfer, SugarStats etc., where you as users can specify how “results should be displayed”? (“a step closer to electronification”?)

  11. Tim

    @Ckoei I’ve never had a cable and software from the NHS – but saying that I’ve never asked, so they might give them away. As I dimly recall, some companies within Big Pharma supply said cables and software for free and some charge. It’s not very consistent.

    Anyway, what annoys me most about meters is that they don’t output their data in a standardised format. An industry standard XML data format would be great as it would allow for data from any meter to be bunged into any software – including open source software. I was planning to write an article about meter database formats but then I decided I didn’t want to lose all our readers through suicidal boredom.

  12. Tim

    CALpumper aka Crystal :
    I hate logging.

    It’s astoundingly tedious isn’t it? I never log – which is probably a Bad Thing – but I’m currently working on the theory that two weeks of solid logging gives enough data to see some trends that need to be corrected (or not) and which I can then base my usual routine on.

    I think this is probably a fair balance between examining the data I produce and the unadulterated boredom of logging.

  13. Teloz

    I use an Accu-Chek Compact Plus (I know you didn’t like it Tim, but I love it) and the Accu-Chek Compass Software. I think the software was free, but I had to pay £15 or so for the infra-red reader and cable to upload my meter onto the PC, but I may be wrong, lots, water, bridge an’ all that.

    I think the software would be great for you T1s, especially pump users, but it’s a bit inflexible for us T2 dinosaurs, it’s created in Microsoft Access, so I’m hoping to come across someone who’s hacked it to allow user input of events. It’s been particularly useful though, since I was forced off the Metformin as it was rotting my gizzard, as it enables you to see trends that aren’t immediately apparent from the readings.


Leave a Reply

Your email address will not be published. Required fields are marked *