Pro workbook: Schedule, Supply, Log, Setup, Formulas. Sample data, real formulas.
| Medication schedule | ||||||||||||||||||
| One row per medicine, the times of day you take it, and how many doses you actually took each day. | ||||||||||||||||||
| Week starting | 21 Sep | |||||||||||||||||
| Medicines | 10 | |||||||||||||||||
| 21 | 22 | 23 | 24 | 25 | 26 | 27 | ||||||||||||
| Medicine | Strength | Dose | Morning | Noon | Evening | Night | Per day | Mo | Tu | We | Th | Fr | Sa | Su | Taken | Due | Adherence | Missed |
| Metformin | 500 mg | 1 tablet | 08:00 | 20:00 | 2 | 2 | 2 | 2 | 1 | 2 | 2 | 2 | 13 | 14 | 92.9% | 1 | ||
| Lisinopril | 10 mg | 1 tablet | 08:00 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 7 | 7 | 100.0% | ||||
| Atorvastatin | 20 mg | 1 tablet | 22:00 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 6 | 7 | 85.7% | 1 | ||||
| Levothyroxine | 75 mcg | 1 tablet | 06:30 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 7 | 7 | 100.0% | ||||
| Vitamin D | 1000 IU | 1 capsule | 08:00 | 1 | 1 | 1 | 1 | 1 | 1 | 5 | 7 | 71.4% | 2 | |||||
| Sertraline | 50 mg | 1 tablet | 08:00 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 7 | 7 | 100.0% | ||||
| Omeprazole | 20 mg | 1 capsule | 07:30 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 7 | 7 | 100.0% | ||||
| Amlodipine | 5 mg | 1 tablet | 20:00 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 6 | 7 | 85.7% | 1 | ||||
| Salbutamol | 100 mcg | 2 puffs | 08:00 | 20:00 | 2 | 2 | 2 | 2 | 2 | 2 | 1 | 2 | 13 | 14 | 92.9% | 1 | ||
| Ferrous sulfate | 65 mg | 1 tablet | 13:00 | 1 | 1 | 1 | 1 | 1 | 1 | 5 | 7 | 71.4% | 2 | |||||
| Doses that day | 12 | 11 | 11 | 10 | 10 | 11 | 11 | 76 | 84 | 90.5% | 8 | |||||||
| Week adherence | 90.5% | of the doses due | ||||||||||||||||
| Type a time in Morning, Noon, Evening or Night — anything in the cell counts as a dose. 'Per day' is how many of the four are filled, and 'Due' is that times seven. | ||||||||||||||||||
| In the week grid put how many doses you took that day: green when it matches the plan, yellow when it's short, red when the day is blank. | ||||||||||||||||||
| A tracker, not medical advice: doses, times and refills come from your prescription and your pharmacist. |
| Refill planner | |||||||||
| How long what you have lasts, the day it runs out and the day to order so it doesn't. | |||||||||
| Medicine | Per day | Units per dose | On hand | Used per day | Days left | Runs out | Lead days | Order by | Status |
| Metformin | 2 | 1 | 42 | 2 | 21 | 12 Oct | 5 | 7 Oct | OK |
| Lisinopril | 1 | 1 | 18 | 1 | 18 | 9 Oct | 5 | 4 Oct | OK |
| Atorvastatin | 1 | 1 | 27 | 1 | 27 | 18 Oct | 5 | 13 Oct | OK |
| Levothyroxine | 1 | 1 | 60 | 1 | 60 | 20 Nov | 7 | 13 Nov | OK |
| Vitamin D | 1 | 1 | 90 | 1 | 90 | 20 Dec | 3 | 17 Dec | OK |
| Sertraline | 1 | 1 | 4 | 1 | 4 | 25 Sep | 5 | 20 Sep | Order now |
| Omeprazole | 1 | 1 | 14 | 1 | 14 | 5 Oct | 5 | 30 Sep | OK |
| Amlodipine | 1 | 1 | 30 | 1 | 30 | 21 Oct | 5 | 16 Oct | OK |
| Salbutamol | 2 | 2 | 120 | 4 | 30 | 21 Oct | 7 | 14 Oct | OK |
| Ferrous sulfate | 1 | 1 | 21 | 1 | 21 | 12 Oct | 3 | 9 Oct | OK |
| Ordering next | 4 | days is the shortest supply you have | |||||||
| To order now | 1 | medicines are inside their lead time | |||||||
| Used per day = doses a day × units per dose, so a two-puff inhaler twice a day eats four units, not two. | |||||||||
| Days left is whole days only — half a tablet left is not a day. Order by is the run-out date minus the lead days you put on Setup. | |||||||||
| A tracker, not medical advice: doses, times and refills come from your prescription and your pharmacist. |
| Dose log | |||||||||
| One line per dose, for the weeks you want a record rather than a tick: what, when, taken or skipped. | |||||||||
| Doses logged | 12 | ||||||||
| Taken | 11 | ||||||||
| Skipped | 1 | ||||||||
| Taken % | 91.7% | ||||||||
| Date | Medicine | Slot | Taken? | Note | Medicine | Taken | Skipped | Last taken | |
| 2026-09-21 | Metformin | Morning | Taken | Metformin | 5 | 1 | 23 Sep | ||
| 2026-09-21 | Metformin | Evening | Taken | Lisinopril | 3 | 23 Sep | |||
| 2026-09-21 | Lisinopril | Morning | Taken | Atorvastatin | 3 | 23 Sep | |||
| 2026-09-21 | Atorvastatin | Night | Taken | Levothyroxine | |||||
| 2026-09-22 | Metformin | Morning | Taken | Vitamin D | |||||
| 2026-09-22 | Metformin | Evening | Taken | Sertraline | |||||
| 2026-09-22 | Lisinopril | Morning | Taken | Omeprazole | |||||
| 2026-09-22 | Atorvastatin | Night | Taken | Amlodipine | |||||
| 2026-09-23 | Metformin | Morning | Taken | Salbutamol | |||||
| 2026-09-23 | Metformin | Evening | Skipped | Ferrous sulfate | |||||
| 2026-09-23 | Lisinopril | Morning | Taken | ||||||
| 2026-09-23 | Atorvastatin | Night | Taken | ||||||
| Setup | |||||||||
| The week, and every medicine with its times, what's in the box and how long a repeat takes. | |||||||||
| Week starting | 2026-09-21 | Any Monday | |||||||
| Medicine | Strength | Dose | Morning | Noon | Evening | Night | Units per dose | On hand | Lead days |
| Metformin | 500 mg | 1 tablet | 08:00 | 20:00 | 1 | 42 | 5 | ||
| Lisinopril | 10 mg | 1 tablet | 08:00 | 1 | 18 | 5 | |||
| Atorvastatin | 20 mg | 1 tablet | 22:00 | 1 | 27 | 5 | |||
| Levothyroxine | 75 mcg | 1 tablet | 06:30 | 1 | 60 | 7 | |||
| Vitamin D | 1000 IU | 1 capsule | 08:00 | 1 | 90 | 3 | |||
| Sertraline | 50 mg | 1 tablet | 08:00 | 1 | 4 | 5 | |||
| Omeprazole | 20 mg | 1 capsule | 07:30 | 1 | 14 | 5 | |||
| Amlodipine | 5 mg | 1 tablet | 20:00 | 1 | 30 | 5 | |||
| Salbutamol | 100 mcg | 2 puffs | 08:00 | 20:00 | 2 | 120 | 7 | ||
| Ferrous sulfate | 65 mg | 1 tablet | 13:00 | 1 | 21 | 3 | |||
| Sixteen rows. Leave the spares empty — every count on the other sheets ignores blanks. | |||||||||
| Leave a slot empty when there's no dose then: 'Per day' on Schedule counts the filled ones, and everything due is worked out from that. | |||||||||
| Units per dose is what comes out of the box each time — two puffs, half a tablet as 0.5 — so the refill maths matches the packet. | |||||||||
| A tracker, not medical advice: doses, times and refills come from your prescription and your pharmacist. |
| How the formulas work | ||
| Every formula in this workbook, in plain words. No macros, nothing hidden. Formulas are shown without the leading = sign. | ||
| Where | Formula | What it does |
| Schedule · Per day | IF($A8="",0,SUMPRODUCT((D8:G8<>"")*1)) | How many of the four time-of-day cells are filled. Put a time, an x or a dose in each slot you take it — the count is what everything due is built on. COUNTA would be wrong here: a cell holding a formula that returns nothing still counts as filled, and on the Pro sheet every cell holds one. |
| Schedule · Due | IF($A8="",0,H8*7) | Doses a day times seven days. Change the grid to a fortnight and this is the only number to change. |
| Schedule · Adherence | IF(Q8=0,0,P8/Q8) | Doses taken over doses due. Guarded against a zero denominator so an empty row shows 0 %, not an error. |
| Schedule · day colour | Conditional format: =AND($A8<>"",$H8>0,I8>=$H8) | Green when the day's count reaches the plan, yellow when it's short, red when the day is blank. The $ on the columns keeps the rule reading the same two columns as it moves right. |
| Supply · Used per day | B8*C8 | Doses a day × units per dose. A two-puff inhaler twice a day uses four units, not two — this is where most refill maths goes wrong. |
| Supply · Days left | IF($A8="","",IF(E8=0,0,INT(D8/E8))) | Whole days the box lasts. INT rounds down: a part day is not a day you can take. An empty row gives nothing rather than 0, so the shortest-supply MIN below doesn't pick up the spare rows. |
| Supply · Order by | IF($A8="","",G8-H8) | Run-out date minus the lead days for a repeat. Dates are numbers, so subtracting days is plain arithmetic. |
| Supply · Status | IF(F8<=H8,"Order now",IF(F8<=H8*2,"Soon","OK")) | Inside the lead time it's already late, within twice the lead time it's next week's job. Two nested IFs, no macro. |
| Log · Taken | SUMPRODUCT(($B$8:$B$127=$G8)*($D$8:$D$127="Taken")) | Counts rows where both things are true at once: this medicine, and taken. Multiplying two TRUE/FALSE columns is the AND. |
| Log · Last taken | SUMPRODUCT(MAX(($B$8:$B$127=$G8)*($D$8:$D$127="Taken")*$A$8:$A$127)) | Multiplies medicine × taken × date and keeps the largest — the most recent taken dose, with nothing sorted. SUMPRODUCT is what makes MAX read the whole array. |