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

Alienware 17 R4 2017 review

These brilliant Lego posters show just what children's imaginations are capable of

Mac power user tips and hidden tricks

Comment réinitialiser votre PC, ordinateur portable ou tablette Windows ?