Subscribe

Lesson 73 of 104 4 min Updated Completed

How to Make an HbA1c Calculator in Excel

The HbA1c (or A1C) test reflects your average blood glucose over the past few months. In this lesson, you’ll build a calculator that estimates A1C from an average glucose level, in mg/dL or mmol/L.

  1. Type the labels into your sheet as shown.

    The labels for the A1C calculator

  2. Select cell C6, and on the Data tab, click Data Validation.

    The Data Validation button on the Data tab

  3. Under Allow, choose List. In Source, type mg/dL,mmol/L.

    The source mg/dL,mmol/L for the units list

  4. Click OK. C6 now has a drop-down list of units.

    The units drop-down list in C6

  5. In D8, type =C6 so the unit appears next to the glucose box.

  6. Add formatting (fills, borders, font sizes) to give it a calculator look.

    The calculator after formatting

  7. In C11, type the formula:

    =IF(C6="mg/dL",(46.7+C8)/28.7,(2.59+C8)/1.59)

    The A1C formula in C11

    This is the standard formula linking estimated average glucose (eAG) and A1C, rearranged to solve for A1C. There’s one version for each unit.

  8. Select C11 and click Home > Conditional Formatting > New Rule to color the result by range (Normal, Pre-Diabetes, Diabetes).

    Conditional formatting rules for the A1C result

  9. Click OK.

Result: the finished calculator:

The finished A1C calculator

Note: This is a spreadsheet exercise that gives a rough estimate. It isn’t a medical test or medical advice. Only a lab A1C test gives your real result.

Download the template to compare: excel-hba1c-calculator.xls