Excel - Calculations for medication

  Dirty Dick 10:13 05 May 2011

I have an elderly neighbour who takes several tablets per day, but she tends to forget when to re-order a new prescription.

I have tried using Excel to do a calculation but I am having a problem when I update the spread sheet.

I have started by making cell D3 (Medication Stock) which is the current stock level at the start.

In Column C I have the date running down. In D4 I have the simple formula (=D3-2) 2 being the number of that type of tablet per day.

I have then conditional formatted the cells so that (a) when the stock level goes below 10 it highlight orange. And (b) when at zero highlights red.

However, when I try to edit the sheet by deleting the dates that have past, it comes up with an error, #REF!

Can anyone help with my problem, or suggest a better method.

Many thanks


  VoG II 10:40 05 May 2011

I suspect that you are deleting rows which would cause that error. Why not just hide them?

  BT 16:52 05 May 2011

I have my prescription managed by the Pharmacy.

If your neighbour has a regular prescription the Pharmacy will order the repeat a few days before its required so that its ready to collect. My Pharmacy gives me a little note with the date next due which I stick on the fridge door. They will always order earlier if required and I just give them a phone call. Most Pharmacies operate this service and if your neighbour isn't able to visit many will also deliver.

  lotvic 18:02 05 May 2011

Same as BT and Chemist delivers them. The Chemist keeps the records.

  Dirty Dick 19:16 05 May 2011

Vog, thanks for your reply, but I think I will end up with a sheet that will be very long, 365 rows per year !

Also, the lady is a “silver surfer” and likes to keep her mind going, Excel formulas are a little difficult for her (AND ME !!!) , and getting the chemist to do the work is too easy.

So after a day looking at the exercise, here’s my solution.

I have renamed sheet 3 as “data” and used it just to “dump” facts and figures.

On the data sheet I have a calculation for the number of days between the “stock check” and “today”, and also the “today()” formula

In Cell B5 I put the date whenever the stock number of tablets is known.

In C4 is the stock number of tablets at a set day.

In C7 (Balance cell) I have the formula “=C4-data!D8*2” . The two at the end is how many tablets per day.

In C8 I have the formula “Today()+C4” (C4 being the stock number at a particular day.) this shows the date when the tablets will run out.

I have conditionally formatted the “Balance” cell so that it turns red when the total left is below 10.

Just one final question, Is there a way to Conditionally Format the “Run Out date” cell (C8) to change colour, say I week before that date arrives?

Any comments would be appreciated.

  VoG II 08:12 06 May 2011

For C8 try the CF formula


This thread is now locked and can not be replied to.

Elsewhere on IDG sites

iPhone X review

How to find a font: Discover the name of a typeface with these apps

The best iPhone for 2017

Les meilleurs logiciels de montage vidéo gratuits (en 2017)