We use cookies to provide you with a better experience. If you continue to use this site, we'll assume you're happy with this. Alternatively, click here to find out how to manage these cookies

hide cookie message
Contact Forum Editor

Send an email to our Forum Editor:


PLEASE NOTE: Your name is used only to let the Forum Editor know who sent the message. Both your name and email address will not be used for any other purpose.

Tech Helproom


It's free to register, to post a question or to start / join a discussion


 

Excel - Calculations for medication


Dirty Dick

Likes # 0

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

DD

Like this post
VoG II

Likes # 0

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

Like this post
BT

Likes # 0

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.

Like this post
lotvic

Likes # 0

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

Like this post
Dirty Dick

Likes # 0

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.

Like this post
VoG II

Likes # 0

For C8 try the CF formula

=C8-TODAY()<=7

Like this post

Reply to this topic

This thread has been locked.



IDG UK Sites

Moto G2 (2014) vs Moto E comparison review: New Moto G is worth the extra cash

IDG UK Sites

Is Apple losing confidence in itself?

IDG UK Sites

Oculus Rift 'Crescent Bay' prototype hands-on: it's an amazing experience

IDG UK Sites

How (and where) to buy an iPhone 6 or iPhone 6 Plus in the UK. Plus: What to do if you pre-ordered...