Glucose Data Analysis: Time in Range, GMI & Variability from CGM Data

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Work out how many readings a CGM should deliver per day, compute the capture % for every day and for the whole 14 days, and count the days below the 70 % target.
• Convert a day of readings from mg/dL to mmol/L, compute the mean in both units, find the day's highest value and turn a mmol/L target into mg/dL.
• Compute the share of a day's readings in range (70–180 mg/dL), below 70, below 54 and above 180 with COUNTIF and COUNTIFS.
• Compute the reading-weighted mean glucose of 14 days, turn it into the GMI in % and mmol/mol and compare it with a lab HbA1c.
• Compare two days with a similar mean but very different swings: standard deviation, coefficient of variation (SD ÷ mean), a stable/unstable flag at 36 % and the largest SD that would still count as stable.
• Stack four days on one 24-hour clock: extract the hour, average the readings of each hour with AVERAGEIFS, find the peak hour and chart the daily pattern.
• Flag every reading below 70 mg/dL, build a running counter of consecutive low readings and count only the lows that last at least 15 minutes as events.
• Finish a one-week CGM summary: time above range per day, the days meeting all three consensus targets, a rule that highlights the days with too little time in range, and a stacked chart of the time in ranges.