A medication calculator

[elmobram22]

Hi all,

I am wondering of a way to create a calculator for adding up how much medication someone has at any given time.

For example.

If Bill collects 28 days supply of medication on a monday morning consisting of...
Paracetamol 500mg x 224 (two to be taken at breakfast, dinner, teatime and bedtime)
Ibuprofen 200mg x 56 (one to be taken breakfast and bedtime)
Vitamin D tablet x 28 (one to be taken at bedtime)

I want a calculation so if i go to spot check his medication on the 12th day of the 28 day cycle at 6pm it can calculate exactly how many of each medication he should have left. As for that day, at 6pm he would have already taken 6 paracetamols and 1 ibuprofen although if I had checked at 10am then he would have taken two paracetamol and one ibuprofen. I know this is feesable to do but could do with a starting block as to the best way to go about this.

Cheers,

Paul

[JohnTopley]

See attached:

Potential formula in L to M gives Paracetamol balance at each interval for each day

in L3

=224-(COLUMNS($L:L)*2)-(ROWS($1:1)-1)*8

Copy across to M and down for required days

Similar formula required for other medication

Hope this helps

[thisoldman]

I took a different course for a possible solution. I used yyyy-mm-dd for the date entries to avoid confusion between different formats. I've used a gold fill for cells containing formulas.

I set up a block for the daily medication schedule, with room for up to twelve medications, taken at up to four times per day. I used 24-hour clock times instead of the breakfast to bedtime names: 08:00, 12:00, 16:00, and 20:00. This makes later calculations easier for me.

The medication names are copied from the daily schedule to the inventory block. There is a column for the previous inventory values, then the next column contains the calculated remainder values for the current date and time, if that medication had been taken as scheduled.

=IF(L4, L4 - INT(($C$7 - $C$4 - 1) * SUM(F4:I4)) - SUMIF($F$3:$I$3, "<" & C8, F4:I4), "")

– INT(($C$7 - $C$4 - 1) * SUM(F4:I4) is the number of whole days between now and the previous inventory less 1 times the daily medication total.
– SUMIF($F$3:$I$3, "<" & C8, F4:I4) is the medication that should have been taken today.

[elmobram22]

That is spot on!!! I have added one more thing which will allow me to put in the days supply of medication which gives me the inventory totals based on the medication schedule information. Thank you so much for your help!!

[thisoldman]

You're welcome. I am glad I was able to help.

[elmobram22]

I have a problem with the times on the calculator. I changed the administration times to 9.30, 13.30, 17.30, 21.30 but if I sign the meds out at 9.00 it doesnt take into account the first morning dose. Also if I check at 6pm it sometimes counts the night dose aswell.

[thisoldman]

I did have an error in the Predicted Remaining formula. The refererence to Current Time should have been absolute: $C$8, instead of C8. The revised worksheet corrects that problem.

I made a revision to allow automatic calculation of the original medication counts. See cells C4:C5 in the edited sheets.

I am unsure how to handle your time format, hh.mm. The format would be a custom number format with my U.S. locale. To allow for any possibility, I have included three versions, where the only difference among them should be the tim format.

I do hope we've found all the errors.

[elmobram22]

Hi,

I have taken the -1 out as that was not counting for the current days medication that could be taken and now it is sorted. Would be nice if I could put a sign out time too so if they collect at 2pm they would only have have the teatime and bedtime dose for that day for example.

Cheers,

Paul

[thisoldman]

I knew something was incorrect. As an excuse, I'll say my thinking is a bit cloudy lately—I'm recovering from surgery.

If the Signout Time is in C9, the formula to determine the amount to collect for the rest of the day would be:

=IF(ISBLANK(G4), "", SUMIF($H$3:$K$3, ">" & $C$9, H4:K4))

This would be copied down to fill twelve rows.

If you wish to determine the amount to sign out with a shift start time in cell C9 and a shift end time in cell C10:

=IF(ISBLANK(G4), "", SUMIFS(H4:K4, $H$3:$K$3, ">" & $C$9, $H$3:$K$3, "<" & $C$10))

Again, this formula would be copied down to fill twelve rows.

[elmobram22]

I still want it to show a full 28 days supply whatever time they pick up. It should just affect the amount remaining. So if they collect and start taking at 5.30pm that should show in the calculated column if that makes sense. Hope the surgery was nothing major and I'd just like to say a massive thanks to you cause this has helped me no end!!